Better Way To Create Tables?
Aug 8, 2005
I am developing a database which will track some of my companies clients bonus trips. Basically everyone is going to the same place but folks are of different levels. Level A is a 14 day trip with X number of activities, Level B is a 10 day trip with Y number of activities and so on. The major things I'll have to track are all of their personal information as well as 'Air Travel', 'Hotel Stay', and 'Recreational Activities' information.
I can put all of this information in to one table (tblTraveler), or am I better off with creating tblTraveler holding only personal information and then linking to other tables such as tblAir, tblHotel, and so on? I'm not entirely new to Access, but no pro yet for sure. Only problem with creating one table is that it would have one whole lotta fields. Not sure if it's better developing technique to split everything up.
Also, the only thing I can come up with as a primary key is the people's last names. The client numbers are the same on many of them, so that's the only thing I can come up with. The PK really is only a factor if it's best to create multiple tables for the traveler. At first I thought it was best to split everything, but now I'm seeing less reason to take all of the travelers information and dump in seperate tables since most of the time on the master reports the trip planners want everything lumped together. Perhaps I'm better off just keeping the forms clean and seperate and just one table? The reports will do the rest for me. Any input greatly appreciated.
View Replies
ADVERTISEMENT
Sep 5, 2013
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 10 Replies
View Related
Jul 18, 2007
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
View 3 Replies
View Related
Sep 9, 2013
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.
View 1 Replies
View Related
Oct 17, 2005
Hi All
I am trying to create a database for estimating manhours on projects. There is a setup table with two columns: "Project" and "Time Period". The database is supposed to allocate manhours to tasks on projects, which is used in estimating man hour costs and also forecasting labour resource requirements.
For example, say the database is used for "Project X" and "Project Y" projects, with Project X running from Jan to March and Project Y running from Aug to Dec. The setup table would look like this:
PROJECT TIME PERIOD
Project X Jan
Project X Feb
Project X March
Project Y Aug
Project Y Sept
Project Y Oct
Project Y Nov
Project Y Dec
From this table, I need to automatically create a new table for each unique project that allows man power to be allocated to each time period. Following on with the example, there are two unique projects, so two tables
need to be automatically created with column headings as per below:
Project X table:
FUNCTION PERSON JAN FEB MARCH
Project Y table:
FUNCTION PERSON AUG SEPT OCT NOV DEC
"Function" is a description of a role, for example engineer. "Person" is the individual undertaking that role, for example Bob Jane. Then in each time period a number between 0 and 1 would be entered corresponding to how much time (0 is no time, 1 is full time) that person would be spending on that function for that time period. Then the table would be populated with many functions and people in this manner.
So the number of tables automatically created depends on how many unique projects there are in the "Project" field of the setup table. The number of columns in these tables will be atleast two: "Function" and "Person",
plus another column for each time period defined for that project.
Perhaps something can be done with a make-table query, but all I can get that to do is copy data from one table straight into another table.
Any help would be greatly appreciated.
Thankyou in advance.
Joey
View 14 Replies
View Related
Jan 9, 2007
hi everyone,
i think this must be easy but im new and despite reading lots just cant figure it out.
i have 2 tables
job details:
autonumber
customer
location
time
picking list:
autonumber,
equipment1
equipment2
equipment3
both the autonumber are primary and linked in a relationship.
when i create a new record in the job details table i need it to automatically create the coresponding blank line in the picking list table with the same number. If I enter something in the picking list table then it creates the matching number and everything is ok but if i dont then it throws my numbers out.
any idea how to get it to do what i need,
thanks
View 1 Replies
View Related
Jun 4, 2007
I am a newbie to access and i am working on a project that has two tables
the primary table has for example the following fields:
firstnameID(primary key) data type auto number
Firstname data type text
the second table has for example :
LastnameID(primary key) data type auto number
Lastname data type text
FirstnameID (foreign key)data type number
I created a relationship between the FirstnameID in the primary table and the FirstnameID in the second table ..
My problem is this whenever i try to view the relationship when i open the primary table it don't show the relationship ,
The Field FirstnameID in the secondary field is always Empty the only time the relationship is created is when i manually insert the autonumber that is generated in the FirstnameID field , but i thought that since a link was created then that field would have automatically be inseted with data , am i assuming wrong or am i doing somethingn wrong? how do i get the autonumber to be inserted in the field automatically to create the relationship?
Please Help
View 3 Replies
View Related
Jul 17, 2007
Hi, i currently have three tables.
Policy table: Policy number(key),date and fund(4 possible strings of four letters)
Last Price: date, fund, last price
Next price: date, fund, next price
What would i have to do to make a table that has a policy number, date, fund, last price and next price?
Thanks
View 1 Replies
View Related
Nov 3, 2012
I am unable to create a new record due the the following error "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. " I have looked everything over and can't find how the duplication is occurring. I am pretty sure it is the primary key of a table called Squad, but it's set to autonumber. I have uploaded the database hoping an experienced or fresh set of eyes could spot the problem. The goal of the database is to track squad inventory along with officers assigned to the squads. The main form is generically called "unit_Numbers" which uses "Squad Subform". Everything else seems to work correctly except for creating another squad.
View 14 Replies
View Related
Aug 14, 2011
Not sure where this question must be posted! I would like to create code in VBA to backup tables in access. I am not sure where to begin, but what I would like to do is the following:
When the user signs in I want to backup the database/tables
When the user sign off I want to back up the database/tables.
View 2 Replies
View Related
Jan 20, 2005
Hi Pals,
Is it possible to create VIEWS for access Tables....
If its possible... How its pls ? :confused:
View 3 Replies
View Related
Dec 2, 2005
I have a db which enables users to enter expenses.
tblExpense
tblExpenseDet
tbleExpenseMileage
tblExpenseDet & tblExpenseMileage are joined to tblExpense
As the expenses are entered at User Level not Client Level, I need to extract the Client ID from the 2 sub tables to create a list grouped by client so we can report the billable expenses.
I'm thinking union query but wouldnt know where to start.
Any help would be appreciated
Ta
View 1 Replies
View Related
Sep 26, 2006
I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. I would really like to have one massive table that contains all the information from both lists. I have made a relationship between the two tables based on the person’s name. Any ideas? Thanks.
View 4 Replies
View Related
Nov 12, 2012
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.
View 5 Replies
View Related
Apr 26, 2015
I am very new with access database. Want to create table with Dates & DayName. For example, tblYear2015 with all dates from 1-Jan-2015 to 31-Dec-2015 in FirstColumn and DayName in second column as per date of First Column. DayName Should be entered automatically as per date entered in first column.
View 6 Replies
View Related
Sep 24, 2012
I have two tables tblPatients (pkUMRN) and tblAdmissions (pkAdmissionID, fkUMRNAdmission). pkUMRN is not auto-number, but is an 8-digit alphanumeric code unique to each tblPatients record. I have set up a one-to-many relationship between tblPatients_pkUMRN and tblAdmissions_fkUMRNAdmissions (each patient can have multiple admissions).
Is it possible to allow the entry of a new tblAdmissions_fkUMRNAdmissions to create a new tblPatients_pkUMRN? IE can you create a new primary key from a new foreign key?
Along a similar line, if the primary key already exists, how would you autofill a form with data from tblPatients when the tblAdmissions_fkUMRNAdmissions is entered in to the table?
View 6 Replies
View Related
Jan 23, 2014
I need to get the MAX id from two tables for use in vb.net. For example:
Code:
Table1 Table2
id, customer id, customer
How do I write a query that will return the last or MAX id from both tables based on a customer criteria.
View 5 Replies
View Related
May 8, 2013
I'm new to MS Access, indeed database design as a whole.
I have been tasked with creating an issues logging database and am having some issues...
I have a table/form that has all the fields I want, including a unique ID, called "Issues". I want to be able to log multiple entries in another table called "emails". The trouble is I am not having much luck. I can create an entry in the issues Form which then shows in the emails but I can't then add another email under the same ID in the "Issues" table.
View 2 Replies
View Related
Jun 18, 2014
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()"
View 11 Replies
View Related
Apr 17, 2007
Hello,
I have attached a zipped excel workbook to best describe what I'm trying to do. I have table1 and table2 and I'm trying to write a query in access to get the output as shown in the workbook. I'm having hard time getting this right. I would appreciate your help if possible. Tks
Richard
View 4 Replies
View Related
Feb 18, 2008
Hi,
I have two tables:
Table A provides total sales volume of the UK shoe market from 2000-2007.
Table B provides the sales volume of different shoe manufacturers from 2000-07.
I want a third table created, called Table C. This should look exactly like Table B but instead of sales volume it shows percentage sales that are calculated by using the figures in Table A and B (i.e. [sales volume from Table B/total sales volume from Table A] * 100).
Could someone point me in the right direction please (assuming that such a table can be created, based on a calculation of figures in other existing tables).
Thank you.
View 3 Replies
View Related
Mar 2, 2015
Is there a way of creating a log to capture changes in a table by users?
In a current database that I'm taking over, there was a lot of records deleted from the main table and now I need to figure out what happened.
So, if I would be able to have a log that records all the actions taken by users, I could at least see who did what.
View 13 Replies
View Related
Mar 25, 2014
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 ?
View 4 Replies
View Related
Nov 26, 2014
I 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 Related
Nov 14, 2013
How i would best combine values in a table to produce a 'primary key id number.'
For example: the first letter of a city in the ID and the next number available/auto number - Portsmouth -> P233
I know i can create this in a query however i want it as the unique ID for that record entry in a table. If that doesn't make sense i can try to elaborate some more.
View 14 Replies
View Related
Aug 3, 2012
I currently have 3 spreadsheets with simliar information. How can I take a database and export only select fields to make either a report or table? Here is the example with types of info i will be using:
100 communities and all their roads. Each of these communities are in a borough, which is then in a region.
Then we have a mileage for each road. Where I have difficulty is that we have another set of data that would be fine if we could just include into this which is all of those roads but additionally they have:
Classification
Surface type/condition
Traffic count
Some roads have two classifications.. each of those having a mileage. I would really like to see all of this data in a database that spits out the needed tables and totals.
View 1 Replies
View Related
