I'm trying to create a table for every record I have in a field. I have two tables, one titled "Experiments" and another titled "Students". I want the new tables generated from the field EXP_NAME from Experiments. I need it to have seven fields, one copied from Students, five labeled Trial1 thru Trial5, and a final for a grade.
View RepliesI have a table with an empty column called ActionID. I need to generate a numerical number that begins with 5000 and goes up by 1 number on each saved record. I have a basic form that links all of my other field to the table except the one I need to generate (ActionID) but will need that number saved to the table once its created.
View 11 Replies View RelatedI think what I want is:
1 table(1): record of people & contact details
1 table(2): list of events with check box's with the names of people from the other table
1 report: listing how many events people have attended.
When I add a new person to table 1 I want a field to be added to table 2 in the form of a checkbox, also when I delete this person I want this field to be deleted in table 2.how to make this an automated process.
I have a table as followed;
EmployeeID: PK
FirstName
MInitial
LastName
Company:FK
Department:FK
What I want to do is create a new field that automatically populates based on what is entered into the above fields.
Essentially creating a single field with unique data generated by multiple fields.
For Example if I entered the following informaiton:
EmployeeID: 1 (AutoNumber)
FirstName: John
MInitial: P
LastName: Doe
Company: FederalGov
Department:Test
The new field can be generated as followed: JohnPDoeFederalgovTest
What i plan on doing with this is making this "generated" field an index so no new duplicated records can be added.
I'm trying to use DDL to add a DATE field as above with NOW() as the default value..
I am using the same syntax pretty much (Altering instead of Creating a table), but its not working..
The error I get is:
err.number = 3293
err.description = Syntax error in ALTER TABLE statement.
Code:
CurrentDb.Execute "ALTER TABLE MyTable ADD COLUMN RecordCreatedDate DATE DEFAULT NOW()"
I want to create a field validation rule that requires the user to input a policy number in a standard format. The format has a fixed length of 13 with three underlying components. The first 3 digits identify coverage, the next 7 are the policy number and the final three must always be "-00".
Example: GLO1234567-00
Is it possible to create a validation rule for this ? If so, can you provide the code based on the simple example above ?
I am trying to create a database by compiling records from multiple excel sheets. however, since most of the clients still uses the excel sheets to enter records, I am thinking if it's possible to create a linked table in access from these excel sheets that are hosted on an online server.
also is it possible to merge multiple linked tables together? if they have the same field range, so they will append when the linked tables gets updated.
I am wondering if it is possible to automatically create a new field in one table whenever a new record is entered in another table. The name of the field would be the primary key entry of that new record.
I have one table (table 1) in which each record corresponds to a particular mouse with a unique ID number, and each field is the ID number of a particular genetic marker. The table overall shows what genotype (+ or -) a mouse has at each marker.
In the other table (table 2) the primary key of each record is the ID number of a genetic marker, and the fields are several different bits of information about the marker (e.g. what chromosome it's on, its location on the chomosome, etc.)
I would like to have it set up so that if I enter a new genetic marker in table 2 a field named after its marker ID will automatically appear in table 1. Is there a way to do this?
In Access 2010, I'm creating a table with a rich text memo field.
I name the field (BugDescription), select "Memo" as the Data Type, and from the Text Format dropdown I select "Rich Text."
However, as soon as I do that, it switches back to plain text.
I can't find any documentation anywhere that would indicate why I can't set this property...
I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2".I would like this done automatically.
Table A
Field X Field Y
1 a
1 b
1 c
1 d
2 a
2 b
2 c
When I tried paste some data using front end to my database, Access showed error (can't create record because data would be duplicated). I thought it's impossible because it is autonumber field. So I checked it (manually). I did copy of my database and then for testing, I created record. I was shocked. Next record should has a value of "160" but Access gave "130" then showed an error "Can't create record because data will be duplicated". Of course after compact and repair everything is fine.
View 5 Replies View RelatedThis is a errand database where each client can have several errands and some errands are related to each one and other.
Since each client can have several errands and and each errand is related to each one and other, then the client. I thought i need key which can work as a ClientErrandID, so i can tie the errands together. My idee is to use the ClientErrandID as an text auto incrementing field with a string like: (Errand-A-xxxx).
Lest say the range would be from (Errand-A-0000 to Errand-A-9999) And when the string hits "9999" at the end the "A" will switch to a "B" and the number counter would start over from "0000"
How should the table relationship look like?When a user is adding a new errand to a client, how should the function work?
Should all the associated errands be created in the background and granted the same Errand_ID when the main errand is created?Can i create a incrementing text field with vba & queries?
How would the code structure look like?Is there any examples on a incrementing text field with code available?
The interface being used is a main form with various tabs and a subform on each of these tabs.
There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?
Is there a way of merging 2 fields together to create an additional field
my database consists of 4 main tables (in order of relationships)
*HeadOfficeDetails
*SiteDetails
*ContainersOnSite *Contracts2015-2016
For example;
Account Reference: TEST
Site Number: 001
and the field i would like to have;
Site Reference: TEST/001
I would also like that when i add a new site to that account i will have TEST/002....
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
I have just been given a txt file with 85 tables and not sure how many fields per table, all different Is there a script I can put this data in to automatically create all these tables and fields?
There must be an easier way to do this than manually create all these tables and fields? I am recieving the data at a late date.
An example of the data I have recieved is:-
ADD TABLE "accounts"
DESCRIPTION "Ratepayer Account Summary"
DUMP-NAME "accounts"
ADD FIELD "subregion" OF "accounts" AS integer
FORMAT "9"
INITIAL "0"
LABEL "SUB-REGION"
ORDER 20
ADD FIELD "district" OF "accounts" AS integer
FORMAT "99"
INITIAL "0"
LABEL "DISTRICT"
ORDER 30
ADD FIELD "raterefno" OF "accounts" AS character
FORMAT "9999999999"
INITIAL ""
LABEL "RATE REFNO"
COLUMN-LABEL "RATE REFNO"
ORDER 10
ADD FIELD "type" OF "accounts" AS character
FORMAT "x(2)"
INITIAL ""
LABEL "TYPE"
COLUMN-LABEL "TYPE"
ORDER 60
ADD FIELD "ulacode" OF "accounts" AS integer
FORMAT "99"
INITIAL "0"
LABEL "UNITARY CODE"
ORDER 5
ADD FIELD "exempt" OF "accounts" AS logical
FORMAT "Yes/No"
INITIAL "No"
LABEL "TAX EXEMPT"
ORDER 70
ADD INDEX "accounts" ON "accounts"
UNIQUE
PRIMARY
INDEX-FIELD "raterefno" ASCENDING ABBREVIATED
I've only just started using Access 2007 at my new job. I've been asked to create a database that will show appointments for all 10 of the employees. I have created a table for the main schedule (where ill put all the data) then one for each of the employees. I've managed to link the tables no problem but it wont let me create and updating relationship. It keeps saying "no unique index found for the referenced field of the primary table". How do I fix this?
I want it to automatically update the date, time, location, customer name and description, if its changed on the main schedule for a certain appointment on the corresponding employees schedule.
Hi,
I want to list all the records of a particular field from all tables in my database. The field has the same name in all the tables. Ideally I would like one long list of the records. How can I do this?
Thanks,
Richard
I need to change my all field information at the same time. For example :
FIELD
a
a
a
a
a
a
This is my field and i want to change there b instead of a (of course there is 5000 records)
Is that possible ? how can i do that ? or can i change at the same time 1by1 its not an option.
building a validation expression that concerns four of my fields:
Quote:
Country | Text
Centr | Boolean
Centr limit | Integer
Apply | Boolean
The number of records that have the same country, AND Centr=TRUE, AND Apply=TRUE, should not exceed the Centr limit value.
So I currently have a table that tracks medication activity for patients called tblMedRanges. The Fields are the ID, StartMedication, EndMedication, Med ID/Medication Name.
In the End of Medication, some patients are not currently active so they have a set date/time field property. What I have for right now is the default value is set to
Code:
=date()
So that when staff adds a new medication it is set to the current date. These patients are supposed to be active, but it doesn't update the default value. I want it to continue to update to the current date every time the database is open.
I have three options that I know of(or think I know of) to update but can't commit to one in particular :
1)I want these individual records in the field to update the current time for today and was considering using some kind of data macro code using before update.
2)The other idea I had was splitting up the field into an EndOfMedication field that would display a value only if their inactive, and an Active field (text, value list active/inactive choices) that would be used in a query to generate the current date as med field. This would take some crafty query work with comparisons later on to generate list of drugs that they were on for each visit.
3) set to text field and convert the field to date field for comparison in queries.
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
I have an existing table that has calculations. When I added a new field with an default value of zero, it did not populate the existing records. Now my tables are not calculating. How can I update the existing records with the new default of zero.
View 2 Replies View RelatedI'm able to import new data from excel just fine, but I can't import updated data from excel due to duplicates not being allowed for a particular field. Is there a way to keep from importing duplicate records based on one field, but still import data from other fields where the information is different from the excel file?
View 5 Replies View RelatedI have been unable to find a simple way to copy a text entry from a Table Field and paste it to multiple records in Datasheet view.The Toolbar 'Paste' procedure copies to the selected record only and I am unaware of how to select multiple records, as per a spreadsheet procedure.
View 2 Replies View RelatedI'm trying to run an if statment, on the records in a field (called "Current_Month"), in an existing table called ""Current_Months_Lag1_Data".
The IF statements work fine - and simply perform a different action for different data in "Current_Month".
I am having problems referring to "Current_Month", where I keep getting 424 (Object required) and 3420 (Object invalid) error messages, on the last line of code.
I have defined & identified the table in which the field is located, yet somehow cannot identify the field within that table. (I've already tried searching the web for similar problems under error messages 424 & 3420).
Function LAG_Forecast_03()
Dim dbs As Database
Set dbs = CurrentDb
Dim Tbl As TableDef
Set Tbl = CurrentDb.TableDefs("Current_Months_Lag1_Data")
Dim FLD As Field
Set FLD = Tbl![Current_Month]