Appending data from one table to multiple relational tables

[Stranger]

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.
Everything is in one row.I exported the table structure from the original table. Hopefully it will look correct in here.

ID Site Location UserName ComputerMakeModel SerialNoServiceTag Type SPEED RAM HD WindowsVersion OfficeVersion CD JackNumber PRINTER PCName IPAddress LastUpdate UPSInservice Date Registered HandheldModel HandheldPhoneNumber HandheldSerial Spybot

Well, you kind of see the current setup. It was a terrible design.

What I did was take that table, added some fields and made this:

ID PrinterID EmployeeID ComputerID HandHeldID UPSID SiteID Site Location UserName ComputerMakeModel SerialNoServiceTag Type SPEED RAM HD WindowsVersion OfficeVersion CD JackNumber PRINTER PCName IPAddress LastUpdate UPSInservice Date Registered HandheldModel HandheldPhoneNumber HandheldSerial Spybot



the next part I may not have needed.

This is where I took the current ID and copied it into the corresponding ID field that I made. Lets say that ID 4 had an employee, printer, and computer in that row. I took the ID 4 and put it into the fldempolyeeid, fldprinterid, and fldcomputerid. I hope that makes since. So, If I can run an append query that will take the data from the above ID fields and put them into the tblemphrd in the new table, then everything should be related back together.

Does this help?

 

[Stranger]

Here is a the append query that I am trying to get to work but it is coming
up with over 30,000 records. Even with the select distinct.

INSERT INTO tblemployeehardware ( EmployeeID, HardwareID, SiteID )
SELECT DISTINCT tblemployees.EmployeeID, tblhardware.ID, tblsites.SiteID
FROM tblemployees, tblhardware, tblsites
WHERE (((tblemployees.EmployeeID)=[tblhardware].[id]) AND ((tblhardware.ID)
Is Not Null))
ORDER BY tblhardware.ID;

 

[Stranger]

Ok, I have the append query showing the correct data, however, I can't run
it do to key violations.

INSERT INTO tblemployeehardware ( EmployeeID, HardwareID, SiteID )
SELECT tblemployees.EmployeeID, tblhardware.ID, tblsites.SiteID
FROM tblemployees, tblhardware, tblsites
WHERE (((tblemployees.EmployeeID)=[tblhardware].[id]) AND
([tblhardware].[id]=[tblsites].[siteid]))
ORDER BY tblhardware.ID;

I know I am really close.

 

[Stranger]

Hi!!

I believe I got it!!!

I created a select query to include the unique ID in the hardware table.
Then created the append query from that. Everything looks great!!!

Thank you so much for you help!!!

Jack

 

[Ken Snell]

Wow. Great news! I was going to reply to your posts this afternoon after
church, but I see it's unnecessary now. Good luck!

 

[Stranger]

Hi!!

I have another question for you.

I have several forms and that completed. Now, If I want to change the pc
assigned to an employee or site, do I need to first create an select query
with the unique ID's and then create an append query with just those ID's to
append the changes or new assignment to the tblemphrd? Form would be based
off of the append query?

 

[Ken Snell]

Your form should be based on a select query that returns the tblEmplHard
records. You can filter on a specific hardware ID and then you can change
the employee to which that piece of hardware is assigned.

Append queries cannot be used as the basis for a form or report.

What you probably would want is a form that has a combo box in the form's
header and use that combo box to select the piece of hardware. Then have the
form be filtered (using the query noted above and by filtering as a WHERE
expression on the HardID field) to show the desired record. You then can
change the employee using a combo box that shows all employees.

The combo box wizard can get you started on this form.

 

[Stranger]

Hi,

I'm a little confused again.

I should create a select query using the tblemplhrd and employee name from
tblemployees and say serial number from tblhard?

 

[Stranger]

I'm using office 2003 if that helps.

Your form should be based on a select query that returns the tblEmplHard
records. You can filter on a specific hardware ID and then you can change
the employee to which that piece of hardware is assigned.

Append queries cannot be used as the basis for a form or report.

What you probably would want is a form that has a combo box in the form's
header and use that combo box to select the piece of hardware. Then have the
form be filtered (using the query noted above and by filtering as a WHERE
expression on the HardID field) to show the desired record. You then can
change the employee using a combo box that shows all employees.

The combo box wizard can get you started on this form.

--

Ken Snell
<MS ACCESS MVP>

ID's piece

 

[Ken Snell]

Assuming just one hardware item at a time will be displayed on the form, I'd
put two combo boxes on the form.

NOTE: Your actual table names and field names may be different from what I'm
showing here.

I would make the form's RecordSource be this query:

SELECT tblEmplHard.HardID, tblEmplHard.EmplID
FROM tblEmplHard;


I would bind one combo box to the HardID field, and the other to the EmplID
field.

I would make the RowSource for the hardware combo box be this:
SELECT tblHard.HardID, tblHard.HardName
FROM tblHard
ORDER BY tblHard.HardName;

I would make the RowSource for the employee combo box be this:
SELECT tblEmpl.EmplID, tblEmpl.EmplName
FROM tblEmpl
ORDER BY tblEmpl.EmplName;


Then, as you select a hardware item from the one combo box, the assigned
employee name will show up in the other combo box.

This may not be the most optimum result for you, but it should get you
started.

 

[Stranger]

That make since. I am also using the location table. Should I include that
or only if the location would change? Or do you think I should just add the
location to the hardware table?

 

[Stranger]

I did what you stated below but all I see are the ID's and not the names.
Any ideas? Instead of having the additional sites table, what if I added
that to the employee table?

 

[Ken Snell]

You're right... I didn't finish my "setup" for the combo boxes.

The bound column for each combo box should be set to 1. Set the column count
to 2 for each. Set the column widths to 0";2" for each.

That should do it.

 

[Stranger]

Hello,

I have this part done and it is showing the assignments initially, however,
this part is not working: Then, as you select a hardware item from the one
combo box, the assigned employee name will show up in the other combo box.
If I pick a different hardware name, it changes it for that employee. It is
not showing the current employee assigned. Any idea?

 

[Stranger]

I found one other item. If I do change the hardware name, and try to exit
it says that changes are not successful because it would create a duplicate
in the index or primary key. Why would that be? I would think it would be
acting like changing the employees assigned PC.

-

 

[Ken Snell]

Change the form to a continuous forms view. That will allow you to see the
separate records in the tblEmplHard table and you can change them
independently.

What is happening on your current form (because I didn't provide enough
details, sorry) is that only one record is seen and any changes are made to
that record alone. When you change the hardware item for an employee, and if
that hardware item already exists on another record, you're getting a
duplicate use of the same hardware, and the table says "NO WAY" because of
how we set up the table.

 

[Stranger]

I changed it to continuous. It has every record going down the page.

With the form I am working on now, I would use that to assign an employee to
new hardware? Correct?

I tried picking a different name but it still is not changing the employee.

 

[Stranger]

Do you think I should add the site, location to the employee field and
eliminate the site table? Or would that be denormalizing the table?

I really appreciate all your help,

 

[Stranger]

Hi, are you still here?

Change the form to a continuous forms view. That will allow you to see the
separate records in the tblEmplHard table and you can change them
independently.

What is happening on your current form (because I didn't provide enough
details, sorry) is that only one record is seen and any changes are made to
that record alone. When you change the hardware item for an employee, and if
that hardware item already exists on another record, you're getting a
duplicate use of the same hardware, and the table says "NO WAY" because of
how we set up the table.

--

Ken Snell
<MS ACCESS MVP>

is fields

 

[Ken Snell]

Yes. I was out of town for just under a week. Haven't had chance to look at
newsgroups since coming back, but I will.