If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Starting over with appending data from one flat table to multiple relational tables.
Here I go again. I changed some things around.
Current flat table (COMPUINV) contains all fields about the computer inventory including employees, handhelds and printers. the only unique key in the flat table is the auto number field. this is the primary key. I have created new tables to separate this data. Computer related info will now be in the computers table (tblcomputers), employee names in the employees table (tblemployees), printers in the printers table (tblprinters) and handhelds in the handhelds table (tblhandhelds). In each of these new tables I am using an auto number field for the primary key. Relationships: tblemployees contains the primary key auto number field of employeeid. tblcomputers contains the primary key auto number field of computerid. tblups contains the primary key auto number field of upsid. tblsites contains the primary key auto number field of siteid. tblprinters contains the primary key auto number of printerid tblhandhelds contains the primary key auto number of handheldid. Now, from tblemployees I am using a 1 to 1 relationship by linking tblemployees.handheldid to tblhandhelds.handheldid From tblemployees there is also a many to many relationship through the intermediary table. 1 to many relationship is tblemployees.employeeid to tblintermediary.employeeid then a 1 to many from tblcomputers.computerid to tblintermediary.computerid. This is because an employee can be assigned to more than one computer. Remaining relationships a 1 to many from tblsites.siteid to tblcomputers.siteid 1 to many from tblups.upsid to tblcomputers.upsid 1 to many from tblprinters.printerid to tblcomputers.printerid these are because the computer would be assigned to a site, ups or printer. My thinking in another way is that these would be physically attached to the computer and not the employee. Make since? Now, how do I take the current flat table (COMPUINV) and append all of the data into the new tables bur keeping the employee assigned to the correct PC and keeping all printers, sites and ups's correctly assigned to each other? I hope this is clearer than my previous post. thanks for all the help. |
#2
|
|||
|
|||
Starting over with appending data from one flat table to multiple relational tables.
I was looking over post from previous suggestions to what I had posted
relating to this.and made the following changes. Suggestion was to create the following: tblEmployees fldEmplID (primary key) fldEmplFName fldEmplLName (etc.) tblHardware fldHardID (primary key) fldHardDesc fldHardSerialNum (etc.) tblEmplHard fldEmplID fldHardID (primary key) I do have the hardware table suggested above split into one for computers, printers, and ups as listed below. Maybe I don't need to do this??? I created the additional table (tblEmplHard) Which containd the primary keys from all other tables. I have the primary key tblemplhardID which is an autonumber field. I made quesries and appended the data to each table. I used the primary auto number field from the flat table and appended that to the auto number field wich is the primary in all other tables. this way I have the same number in each table. I just need to somehow get those numbers into the additional table (tblEmplHard). this should then relate all information back to the employees. I think. However, it won't let me append to this table. I think I am close. Any other ideas? Thanks. -- "Stranger" wrote in message . .. Here I go again. I changed some things around. Current flat table (COMPUINV) contains all fields about the computer inventory including employees, handhelds and printers. the only unique key in the flat table is the auto number field. this is the primary key. I have created new tables to separate this data. Computer related info will now be in the computers table (tblcomputers), employee names in the employees table (tblemployees), printers in the printers table (tblprinters) and handhelds in the handhelds table (tblhandhelds). In each of these new tables I am using an auto number field for the primary key. Relationships: tblemployees contains the primary key auto number field of employeeid. tblcomputers contains the primary key auto number field of computerid. tblups contains the primary key auto number field of upsid. tblsites contains the primary key auto number field of siteid. tblprinters contains the primary key auto number of printerid tblhandhelds contains the primary key auto number of handheldid. Now, from tblemployees I am using a 1 to 1 relationship by linking tblemployees.handheldid to tblhandhelds.handheldid From tblemployees there is also a many to many relationship through the intermediary table. 1 to many relationship is tblemployees.employeeid to tblintermediary.employeeid then a 1 to many from tblcomputers.computerid to tblintermediary.computerid. This is because an employee can be assigned to more than one computer. Remaining relationships a 1 to many from tblsites.siteid to tblcomputers.siteid 1 to many from tblups.upsid to tblcomputers.upsid 1 to many from tblprinters.printerid to tblcomputers.printerid these are because the computer would be assigned to a site, ups or printer. My thinking in another way is that these would be physically attached to the computer and not the employee. Make since? Now, how do I take the current flat table (COMPUINV) and append all of the data into the new tables bur keeping the employee assigned to the correct PC and keeping all printers, sites and ups's correctly assigned to each other? I hope this is clearer than my previous post. thanks for all the help. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Appending data from one table to multiple relational tables | Stranger | Running & Setting Up Queries | 42 | August 18th, 2004 02:55 AM |
Appending date from one table to multiple tables | Stranger | Running & Setting Up Queries | 7 | July 30th, 2004 12:30 AM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |
How to extract data from multiple Access databases to create a Pivot table | Android | Worksheet Functions | 4 | February 6th, 2004 04:22 PM |