Appending data from one table to multiple relational tables

[Stranger]

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.

 

[Ken Snell]

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.

 

[Stranger]

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.

 

[Stranger]

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]

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.

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.

 

[Ken Snell]

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.

 

[Stranger]

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.

 

[Ken Snell]

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.

 

[Stranger]

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]

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.

 

[Stranger]

I tried to do that but access will not let me have more than one auto
number field in the table.

 

[Ken Snell]

You should not have *any* autonumber fields in tblEmplHard table. They both
should be Long Integer.

 

[Stranger]

I have made that change. However, since I have the hardware in separate
tables, computer table, printer table, etc. It is requiring in the
tblemphard to have a value in every field.

1. Should I not separate the hardware into separate tables?
2. If it is ok, then why can I not assign a computer without assigning a
printer, etc?

Thanks.

 

[Ken Snell]

The structure that I gave you assumed that all hardware data are in one
table. Splitting them into printers, computers, etc. tables shouldn't be
necessary. You can use a field in tblHard to identify the type of hardware.
For the table structure I gave you, each piece of hardware, *regardless of
what it is*, must have a unique ID that is not shared with any other
hardware piece.

 

[Stranger]

Hi,

I really appreciate all of your help.

Let me make sure I understand what you are saying.
I can put all hardware into one table. Have an additional field specifying
whether it is a printer, desktop, server, handheld, etc.

For the statement, "each piece of hardware, *regardless of what it is*, must
have a unique ID that is not shared with any other hardware piece."

Does this mean that the autonumber field which is the primary key in that
table would be the unique ID??

Or

Do you mean that I need to somehow assign a unique ID something like
desktops number 1, servers number 2m etc?

 

[Stranger]

I think I have answered the question I just sent. Each piece of hardware
would have it's own unique ID because each one is a separate record in the
table therefore it would have it's own unique auto number? Right?

--

 

[Ken Snell]

Right!

--

Ken Snell
<MS ACCESS MVP>

I think I have answered the question I just sent. Each piece of hardware
would have it's own unique ID because each one is a separate record in the
table therefore it would have it's own unique auto number? Right?

 

[Stranger]

Thanks for you help with that one. Now, I am back to the original issue of
getting all of the data from the current flat table into the new tables.
Can I somehow create one append query that will update the data into all the
tables?

 

[Ken Snell]

I'm not sure that just one append query will do the job.. you may need to
run sequential ones. However, I cannot see your data in the flat file, and I
don't believe you've described it during this thread. So I'll need more info
from you first regarding the structure of the current data and in what
format it is (ACCESS table, EXCEL spreadsheet, text file, etc.).

 

[Stranger]

I don't know if this is going to help but in the original flat table I added
additional ID columns. Like employeeid, printerid, upsid, etc. I copied
the unique ID from the computer ID (primary key) into those additional
fields so that the unique id will be in each of those fields if the employee
has additional hardware. In other words, if an employee had a computer and
printer, I copied the primary key ID (lets say 4) into the employeeid and
printerid fields that I created in the original table. I'm hoping this will
help in bringing the data into the new tables.

I kept the tblsites. Maybe this isn't needed but since it is not hardware,
I figure it should be separate. ??

I can append the data into the parent tables with no problem but I just
can't get the tblemlhrd to tie everything together. I can manually go in
and put each number in but that would take a while.