S
Stranger
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 are:
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.
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 are:
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.