A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Starting over with appending data from one flat table to multiple relational tables.



 
 
Thread Tools Display Modes
  #1  
Old August 3rd, 2004, 06:43 PM
Stranger
external usenet poster
 
Posts: n/a
Default 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  
Old August 5th, 2004, 04:32 AM
Stranger
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 07:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.