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
|
|||
|
|||
Appending data from one table to multiple relational tables
Situation:
Computer inventory is currently in one table. I have created a new database with several tables related together for the new inventory database. Now, I have created several append queries that will append all the computers into the computer table, the employee info into the employee table, printer info into the printer table, etc. If I run all of theses append queries seperately, the employees are not associated with anything. So, How can I get these to run but keep the employee associated with the proper hardware? thanks. |
#2
|
|||
|
|||
Appending data from one table to multiple relational tables
How is an employee related to a hardware now? What you need to do is to load
the data into the parent tables (the "one" tables), and then use those tables along with the original table (joining from the original table to the new tables on a field that uniquely identifies the data in the parent tables) in order to append data into the child tables (the "many" tables). You'll need to give us more information about your exact setup and data for someone to give you more specific info. -- Ken Snell MS ACCESS MVP "Stranger" wrote in message . .. Situation: Computer inventory is currently in one table. I have created a new database with several tables related together for the new inventory database. Now, I have created several append queries that will append all the computers into the computer table, the employee info into the employee table, printer info into the printer table, etc. If I run all of theses append queries seperately, the employees are not associated with anything. So, How can I get these to run but keep the employee associated with the proper hardware? thanks. |
#3
|
|||
|
|||
Appending data from one table to multiple relational tables
The current inventory table contains all of the fields along with an
employee field. The autonumber field is the primary key. Nothing else unique with the current table. I did run the seperate append queries and loaded the computer info into the computer table, printers into the printer table and employee names into the employee table. The employee table would be the one to many table. So, I would now need to reassociate the employees with the new tables. I tried to use an append query in access to append the data into all tables at one time but it won't let me do that. I've attached wordpad document with two print screens. Hopefully this will help explain better. "Ken Snell" wrote in : How is an employee related to a hardware now? What you need to do is to load the data into the parent tables (the "one" tables), and then use those tables along with the original table (joining from the original table to the new tables on a field that uniquely identifies the data in the parent tables) in order to append data into the child tables (the "many" tables). You'll need to give us more information about your exact setup and data for someone to give you more specific info. |
#4
|
|||
|
|||
Appending data from one table to multiple relational tables
Looking at my relationships again, I have the Employee table setup as the
parent table. All of the others are child tables. I think that is what I have done. I'm still learning this database stuff. I have one to many relationships from the other tables back to the employee table. Since an employee can be assigned more than one computer. Stranger wrote in : The current inventory table contains all of the fields along with an employee field. The autonumber field is the primary key. Nothing else unique with the current table. I did run the seperate append queries and loaded the computer info into the computer table, printers into the printer table and employee names into the employee table. The employee table would be the one to many table. So, I would now need to reassociate the employees with the new tables. I tried to use an append query in access to append the data into all tables at one time but it won't let me do that. I've attached wordpad document with two print screens. Hopefully this will help explain better. "Ken Snell" wrote in : How is an employee related to a hardware now? What you need to do is to load the data into the parent tables (the "one" tables), and then use those tables along with the original table (joining from the original table to the new tables on a field that uniquely identifies the data in the parent tables) in order to append data into the child tables (the "many" tables). You'll need to give us more information about your exact setup and data for someone to give you more specific info. |
#5
|
|||
|
|||
Appending data from one table to multiple relational tables
Stranger wrote in
: Looking at my relationships again, I have the Employee table setup as the parent table. All of the others are child tables. I think that is what I have done. I'm still learning this database stuff. I have one to many relationships from the other tables back to the employee table. Since an employee can be assigned more than one computer. Stranger wrote in : The current inventory table contains all of the fields along with an employee field. The autonumber field is the primary key. Nothing else unique with the current table. I did run the seperate append queries and loaded the computer info into the computer table, printers into the printer table and employee names into the employee table. The employee table would be the one to many table. So, I would now need to reassociate the employees with the new tables. I tried to use an append query in access to append the data into all tables at one time but it won't let me do that. I know I am rambling now. Should I have the other tables related to the computer table and not to the employee table? It would be something like: Employee Computer printer handheld Instead of what I have now which is: employee Computer Printer Handheld Unless it doesn't really matter. I've attached wordpad document with two print screens. Hopefully this will help explain better. "Ken Snell" wrote in : How is an employee related to a hardware now? What you need to do is to load the data into the parent tables (the "one" tables), and then use those tables along with the original table (joining from the original table to the new tables on a field that uniquely identifies the data in the parent tables) in order to append data into the child tables (the "many" tables). You'll need to give us more information about your exact setup and data for someone to give you more specific info. |
#6
|
|||
|
|||
Appending data from one table to multiple relational tables
I still don't have a better understanding of your setup, so let me suggest a
starting point for your tables. I envision three tables so far: tblEmployees fldEmplID (primary key) fldEmplFName fldEmplLName (etc.) tblHardware fldHardID (primary key) fldHardDesc fldHardSerialNum (etc.) tblEmplHard fldEmplID fldHardID (primary key) The third table allows you to "match" an employee to a piece of hardware. -- Ken Snell MS ACCESS MVP "Stranger" wrote in message . .. Stranger wrote in : Looking at my relationships again, I have the Employee table setup as the parent table. All of the others are child tables. I think that is what I have done. I'm still learning this database stuff. I have one to many relationships from the other tables back to the employee table. Since an employee can be assigned more than one computer. Stranger wrote in : The current inventory table contains all of the fields along with an employee field. The autonumber field is the primary key. Nothing else unique with the current table. I did run the seperate append queries and loaded the computer info into the computer table, printers into the printer table and employee names into the employee table. The employee table would be the one to many table. So, I would now need to reassociate the employees with the new tables. I tried to use an append query in access to append the data into all tables at one time but it won't let me do that. I know I am rambling now. Should I have the other tables related to the computer table and not to the employee table? It would be something like: Employee Computer printer handheld Instead of what I have now which is: employee Computer Printer Handheld Unless it doesn't really matter. I've attached wordpad document with two print screens. Hopefully this will help explain better. "Ken Snell" wrote in : How is an employee related to a hardware now? What you need to do is to load the data into the parent tables (the "one" tables), and then use those tables along with the original table (joining from the original table to the new tables on a field that uniquely identifies the data in the parent tables) in order to append data into the child tables (the "many" tables). You'll need to give us more information about your exact setup and data for someone to give you more specific info. |
#7
|
|||
|
|||
Appending data from one table to multiple relational tables
"Ken Snell" wrote in
: Hi, i posted a more detailed description of what I currently have. It's under the subject starting over. I understand what you are saying below. However, would this allow me to assign multiple Pc's to one employee? It looks like I would be able to enter hardware without having to assign it immediately to an employee. I get confused with the relationships layouts. Once it sinks in I'm sure I will get this. I still don't have a better understanding of your setup, so let me suggest a starting point for your tables. I envision three tables so far: tblEmployees fldEmplID (primary key) fldEmplFName fldEmplLName (etc.) tblHardware fldHardID (primary key) fldHardDesc fldHardSerialNum (etc.) tblEmplHard fldEmplID fldHardID (primary key) The third table allows you to "match" an employee to a piece of hardware. |
#8
|
|||
|
|||
Appending data from one table to multiple relational tables
I didn't find the "new post"?
Anyway, yes, with the table setup I've shown, you can assign multiple hardware items to one employee, but only one employee to each hardware. -- Ken Snell MS ACCESS MVP "Stranger" wrote in message . .. "Ken Snell" wrote in : Hi, i posted a more detailed description of what I currently have. It's under the subject starting over. I understand what you are saying below. However, would this allow me to assign multiple Pc's to one employee? It looks like I would be able to enter hardware without having to assign it immediately to an employee. I get confused with the relationships layouts. Once it sinks in I'm sure I will get this. I still don't have a better understanding of your setup, so let me suggest a starting point for your tables. I envision three tables so far: tblEmployees fldEmplID (primary key) fldEmplFName fldEmplLName (etc.) tblHardware fldHardID (primary key) fldHardDesc fldHardSerialNum (etc.) tblEmplHard fldEmplID fldHardID (primary key) The third table allows you to "match" an employee to a piece of hardware. |
#9
|
|||
|
|||
Appending data from one table to multiple relational tables
Hello!!
It is under the subject of: Starting over with appending data from one flat table to multiple relational tables. I have all of the tables populated, however the only thing I need to do now is to populate the tblemphard so that the same ID number is in the appropriate field. that should relate everything back together. However, It won't let me do so. I think I am getting close. "Ken Snell" wrote in : I didn't find the "new post"? Anyway, yes, with the table setup I've shown, you can assign multiple hardware items to one employee, but only one employee to each hardware. |
#10
|
|||
|
|||
Appending data from one table to multiple relational tables
If you made the "fldHardID" field in tblEmpHard an autonumber field, then
change it to Long Integer. Same for the "fldEmplID" field -- it should be Long Integer. -- Ken Snell MS ACCESS MVP "Stranger" wrote in message .. . Hello!! It is under the subject of: Starting over with appending data from one flat table to multiple relational tables. I have all of the tables populated, however the only thing I need to do now is to populate the tblemphard so that the same ID number is in the appropriate field. that should relate everything back together. However, It won't let me do so. I think I am getting close. "Ken Snell" wrote in : I didn't find the "new post"? Anyway, yes, with the table setup I've shown, you can assign multiple hardware items to one employee, but only one employee to each hardware. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Appending date from one table to multiple tables | Stranger | Running & Setting Up Queries | 7 | July 30th, 2004 12:30 AM |
Append one table to multiple tables? | Stranger | Running & Setting Up Queries | 2 | July 28th, 2004 01:00 PM |
Combining data from multiple fields into Pivot table | Os | Worksheet Functions | 5 | June 1st, 2004 05:04 PM |
Need help with Tables Design and Relationships | Tom | Database Design | 24 | May 19th, 2004 06:51 PM |
Data Table | Max | Worksheet Functions | 1 | April 12th, 2004 04:12 AM |