Appending date from one table to multiple tables

  • Thread starter Thread starter Stranger
  • Start date Start date
S

Stranger

I currently have the computer inventory at work in one table. I have
created new tables with relationships and I want to append the data in the
existing table so that it populates all of the new tables. Thus keeping
employees associated with their PC, printers, etc. I have tried creating
an append query but when ran it says it will append 0 rows. Any idea?
 
Without some idea of the structure, or of the SQL statement of your append
query, it will be difficult to guess what might be happening...
 
I currently have the computer inventory at work in one table. I have
created new tables with relationships and I want to append the data in the
existing table so that it populates all of the new tables. Thus keeping
employees associated with their PC, printers, etc. I have tried creating
an append query but when ran it says it will append 0 rows. Any idea?
Click to expand...

It's almost NEVER either necessary or a good idea to populate new
tables with empty "placeholder" records, if that's what you mean. Are
you trying to extract data from a single wide-flat table into multiple
normalized tables? If so, note that the Append query should be based
JUST on the wide-flat table; if you join it to the target table, you
will select only those records which already exist in the target
(none, that is). Perhaps you could post the SQL view of the append
query.
 
Here is the SQL code:

UPDATE COMPUINV, tblups INNER JOIN (tblsites INNER JOIN (tblprinters
INNER JOIN (tblhandhelds INNER JOIN (tblcomputers INNER JOIN tblemployees
ON tblcomputers.NetworkID = tblemployees.NetworkID) ON
tblhandhelds.HandheldID = tblemployees.HandheldID) ON
tblprinters.PrinterID = tblemployees.PrinterID) ON tblsites.SiteID =
tblemployees.SiteID) ON tblups.UPSID = tblemployees.UPSID SET
COMPUINV.Site = tblsites!Site, COMPUINV.Location = tblsites!Location,
COMPUINV.[User Name] = tblemployees!EmployeeName, COMPUINV.[Computer
Make/Model] = tblcomputers!Make, COMPUINV.[Serial No/Service Tag] =
tblcomputers!SerialNumber, COMPUINV.Type = tblcomputers!Type,
COMPUINV.SPEED = tblcomputers!CPUSPEED, COMPUINV.RAM = tblcomputers!RAM,
COMPUINV.HD = tblcomputers!RAM, COMPUINV.WindowsVersion = tblcomputers!
WindowsVersion, COMPUINV.OfficeVersion = tblcomputers!OfficeVersion,
COMPUINV.CD = tblcomputers!CDROMSpeed, COMPUINV.[Jack #] = tblcomputers!
JackNumber, COMPUINV.PRINTER = tblprinters!Make, COMPUINV.[IP Address] =
tblcomputers!IPAddress, COMPUINV.LastUpdate = tblcomputers!
LastWindowsUpdate, COMPUINV.[UPS Inservice Date] = tblups!InserviceDate,
COMPUINV.Registered = tblups!Registered, COMPUINV.HandheldModel =
tblhandhelds!Model, COMPUINV.HandheldPhoneNumber = tblhandhelds!
PhoneNumber, COMPUINV.HandheldSerial = tblhandhelds!SerialNumber,
COMPUINV.Spybot = tblcomputers!Spybot, tblcomputers.NetworkID =
tblemployees!NetworkID;

The table COMPUINV is the single table that holds all of the information.
All other tables are new and related together(Normalized) I want to
populate those table from the existing single table. I hope this makes
things clearer.
 
I did change the ! to . I don't know why it put ! in there. I don't
beleive that was correct. Still doesn't work though.

UPDATE COMPUINV, tblups INNER JOIN (tblsites INNER JOIN (tblprinters
INNER JOIN (tblhandhelds INNER JOIN (tblcomputers INNER JOIN tblemployees
ON tblcomputers.NetworkID = tblemployees.NetworkID) ON
tblhandhelds.HandheldID = tblemployees.HandheldID) ON
tblprinters.PrinterID = tblemployees.PrinterID) ON tblsites.SiteID =
tblemployees.SiteID) ON tblups.UPSID = tblemployees.UPSID SET
COMPUINV.Site = tblsites.Site, COMPUINV.Location = tblsites.Location,
COMPUINV.[User Name] = tblemployees.EmployeeName, COMPUINV.[Computer
Make/Model] = tblcomputers.Make, COMPUINV.[Serial No/Service Tag] =
tblcomputers.SerialNumber, COMPUINV.Type = tblcomputers.Type,
COMPUINV.SPEED = tblcomputers.CPUSPEED, COMPUINV.RAM = tblcomputers.RAM,
COMPUINV.HD = tblcomputers.RAM, COMPUINV.WindowsVersion =
tblcomputers.WindowsVersion, COMPUINV.OfficeVersion =
tblcomputers.OfficeVersion, COMPUINV.CD = tblcomputers.CDROMSpeed,
COMPUINV.[Jack #] = tblcomputers.JackNumber, COMPUINV.PRINTER =
tblprinters.Make, COMPUINV.[IP Address] = tblcomputers.IPAddress,
COMPUINV.LastUpdate = tblcomputers.LastWindowsUpdate, COMPUINV.[UPS
Inservice Date] = tblups.InserviceDate, COMPUINV.Registered =
tblups.Registered, COMPUINV.HandheldModel = tblhandhelds.Model,
COMPUINV.HandheldPhoneNumber = tblhandhelds.PhoneNumber,
COMPUINV.HandheldSerial = tblhandhelds.SerialNumber, COMPUINV.Spybot =
tblcomputers.Spybot, tblcomputers.NetworkID = tblemployees.NetworkID;
 
Pardon me for jumping in.

What you need is a series of APPEND queries that will extract the relevant
fields from COMPUINV and insert the data into the relevant tables.

For instance to populate tblsites you might need something like:

INSERT Into tblSites (Site, Location)
SELECT Distinct Site, Location
FROM COMPUINV

That is based on breaking down you original query as much as I could. I am sure
I am probably missing some of the fields you want to populate

For tblComputers:

Insert INTO tblComputers
(Make, SerialNumber,Type,CPUSPEED, RAM, WindowsVersion,OfficeVersion,...)
SELECT DISTINCT [Computer Make/Model], [Serial No/Service Tag], ...
FROM COMPUINV

Here is the SQL code:

UPDATE COMPUINV, tblups INNER JOIN (tblsites INNER JOIN (tblprinters
INNER JOIN (tblhandhelds INNER JOIN (tblcomputers INNER JOIN tblemployees
ON tblcomputers.NetworkID = tblemployees.NetworkID) ON
tblhandhelds.HandheldID = tblemployees.HandheldID) ON
tblprinters.PrinterID = tblemployees.PrinterID) ON tblsites.SiteID =
tblemployees.SiteID) ON tblups.UPSID = tblemployees.UPSID SET
COMPUINV.Site = tblsites!Site, COMPUINV.Location = tblsites!Location,
COMPUINV.[User Name] = tblemployees!EmployeeName, COMPUINV.[Computer
Make/Model] = tblcomputers!Make, COMPUINV.[Serial No/Service Tag] =
tblcomputers!SerialNumber, COMPUINV.Type = tblcomputers!Type,
COMPUINV.SPEED = tblcomputers!CPUSPEED, COMPUINV.RAM = tblcomputers!RAM,
COMPUINV.HD = tblcomputers!RAM, COMPUINV.WindowsVersion = tblcomputers!
WindowsVersion, COMPUINV.OfficeVersion = tblcomputers!OfficeVersion,
COMPUINV.CD = tblcomputers!CDROMSpeed, COMPUINV.[Jack #] = tblcomputers!
JackNumber, COMPUINV.PRINTER = tblprinters!Make, COMPUINV.[IP Address] =
tblcomputers!IPAddress, COMPUINV.LastUpdate = tblcomputers!
LastWindowsUpdate, COMPUINV.[UPS Inservice Date] = tblups!InserviceDate,
COMPUINV.Registered = tblups!Registered, COMPUINV.HandheldModel =
tblhandhelds!Model, COMPUINV.HandheldPhoneNumber = tblhandhelds!
PhoneNumber, COMPUINV.HandheldSerial = tblhandhelds!SerialNumber,
COMPUINV.Spybot = tblcomputers!Spybot, tblcomputers.NetworkID =
tblemployees!NetworkID;

The table COMPUINV is the single table that holds all of the information.
All other tables are new and related together(Normalized) I want to
populate those table from the existing single table. I hope this makes
things clearer.

It's almost NEVER either necessary or a good idea to populate new
tables with empty "placeholder" records, if that's what you mean. Are
you trying to extract data from a single wide-flat table into multiple
normalized tables? If so, note that the Append query should be based
JUST on the wide-flat table; if you join it to the target table, you
will select only those records which already exist in the target
(none, that is). Perhaps you could post the SQL view of the append
query.
Click to expand...
Click to expand...
 
That looks like it makes more since. If I do that, will I keep the
employees associated with their respective computer, etc?



Pardon me for jumping in.

What you need is a series of APPEND queries that will extract the
relevant fields from COMPUINV and insert the data into the relevant
tables.

For instance to populate tblsites you might need something like:

INSERT Into tblSites (Site, Location)
SELECT Distinct Site, Location
FROM COMPUINV

That is based on breaking down you original query as much as I could.
I am sure I am probably missing some of the fields you want to
populate

For tblComputers:

Insert INTO tblComputers
(Make, SerialNumber,Type,CPUSPEED, RAM,
WindowsVersion,OfficeVersion,...)
SELECT DISTINCT [Computer Make/Model], [Serial No/Service Tag], ...
FROM COMPUINV

Here is the SQL code:

UPDATE COMPUINV, tblups INNER JOIN (tblsites INNER JOIN (tblprinters
INNER JOIN (tblhandhelds INNER JOIN (tblcomputers INNER JOIN
tblemployees ON tblcomputers.NetworkID = tblemployees.NetworkID) ON
tblhandhelds.HandheldID = tblemployees.HandheldID) ON
tblprinters.PrinterID = tblemployees.PrinterID) ON tblsites.SiteID =
tblemployees.SiteID) ON tblups.UPSID = tblemployees.UPSID SET
COMPUINV.Site = tblsites!Site, COMPUINV.Location = tblsites!Location,
COMPUINV.[User Name] = tblemployees!EmployeeName, COMPUINV.[Computer
Make/Model] = tblcomputers!Make, COMPUINV.[Serial No/Service Tag] =
tblcomputers!SerialNumber, COMPUINV.Type = tblcomputers!Type,
COMPUINV.SPEED = tblcomputers!CPUSPEED, COMPUINV.RAM =
tblcomputers!RAM, COMPUINV.HD = tblcomputers!RAM,
COMPUINV.WindowsVersion = tblcomputers! WindowsVersion,
COMPUINV.OfficeVersion = tblcomputers!OfficeVersion, COMPUINV.CD =
tblcomputers!CDROMSpeed, COMPUINV.[Jack #] = tblcomputers!
JackNumber, COMPUINV.PRINTER = tblprinters!Make, COMPUINV.[IP
Address] = tblcomputers!IPAddress, COMPUINV.LastUpdate =
tblcomputers! LastWindowsUpdate, COMPUINV.[UPS Inservice Date] =
tblups!InserviceDate, COMPUINV.Registered = tblups!Registered,
COMPUINV.HandheldModel = tblhandhelds!Model,
COMPUINV.HandheldPhoneNumber = tblhandhelds! PhoneNumber,
COMPUINV.HandheldSerial = tblhandhelds!SerialNumber, COMPUINV.Spybot
= tblcomputers!Spybot, tblcomputers.NetworkID =
tblemployees!NetworkID;

The table COMPUINV is the single table that holds all of the
information. All other tables are new and related
together(Normalized) I want to populate those table from the
existing single table. I hope this makes things clearer.

I currently have the computer inventory at work in one table. I
have created new tables with relationships and I want to append the
data in the existing table so that it populates all of the new
tables. Thus keeping employees associated with their PC, printers,
etc. I have tried creating an append query but when ran it says it
will append 0 rows. Any idea?

It's almost NEVER either necessary or a good idea to populate new
tables with empty "placeholder" records, if that's what you mean.
Are you trying to extract data from a single wide-flat table into
multiple normalized tables? If so, note that the Append query
should be based JUST on the wide-flat table; if you join it to the
target table, you will select only those records which already
exist in the target (none, that is). Perhaps you could post the SQL
view of the append query.
Click to expand...
Click to expand...
Click to expand...
 
It depends on your table structure and how you build the append queries. It
should be possible, since all the data is in one table to put the proper linking
data into each of your tables.

For instance, if each computer has an "Employee" that "owns" the computer, you
should be able to include the identifying number of the employee as a field in
the computers table.

Hopefully this makes sense to you. Transforming data from a flat table
structure to a relational structure can be tedious, but is usually very
beneficial once it is completed.
That looks like it makes more since. If I do that, will I keep the
employees associated with their respective computer, etc?

Pardon me for jumping in.

What you need is a series of APPEND queries that will extract the
relevant fields from COMPUINV and insert the data into the relevant
tables.

For instance to populate tblsites you might need something like:

INSERT Into tblSites (Site, Location)
SELECT Distinct Site, Location
FROM COMPUINV

That is based on breaking down you original query as much as I could.
I am sure I am probably missing some of the fields you want to
populate

For tblComputers:

Insert INTO tblComputers
(Make, SerialNumber,Type,CPUSPEED, RAM,
WindowsVersion,OfficeVersion,...)
SELECT DISTINCT [Computer Make/Model], [Serial No/Service Tag], ...
FROM COMPUINV

Here is the SQL code:

UPDATE COMPUINV, tblups INNER JOIN (tblsites INNER JOIN (tblprinters
INNER JOIN (tblhandhelds INNER JOIN (tblcomputers INNER JOIN
tblemployees ON tblcomputers.NetworkID = tblemployees.NetworkID) ON
tblhandhelds.HandheldID = tblemployees.HandheldID) ON
tblprinters.PrinterID = tblemployees.PrinterID) ON tblsites.SiteID =
tblemployees.SiteID) ON tblups.UPSID = tblemployees.UPSID SET
COMPUINV.Site = tblsites!Site, COMPUINV.Location = tblsites!Location,
COMPUINV.[User Name] = tblemployees!EmployeeName, COMPUINV.[Computer
Make/Model] = tblcomputers!Make, COMPUINV.[Serial No/Service Tag] =
tblcomputers!SerialNumber, COMPUINV.Type = tblcomputers!Type,
COMPUINV.SPEED = tblcomputers!CPUSPEED, COMPUINV.RAM =
tblcomputers!RAM, COMPUINV.HD = tblcomputers!RAM,
COMPUINV.WindowsVersion = tblcomputers! WindowsVersion,
COMPUINV.OfficeVersion = tblcomputers!OfficeVersion, COMPUINV.CD =
tblcomputers!CDROMSpeed, COMPUINV.[Jack #] = tblcomputers!
JackNumber, COMPUINV.PRINTER = tblprinters!Make, COMPUINV.[IP
Address] = tblcomputers!IPAddress, COMPUINV.LastUpdate =
tblcomputers! LastWindowsUpdate, COMPUINV.[UPS Inservice Date] =
tblups!InserviceDate, COMPUINV.Registered = tblups!Registered,
COMPUINV.HandheldModel = tblhandhelds!Model,
COMPUINV.HandheldPhoneNumber = tblhandhelds! PhoneNumber,
COMPUINV.HandheldSerial = tblhandhelds!SerialNumber, COMPUINV.Spybot
= tblcomputers!Spybot, tblcomputers.NetworkID =
tblemployees!NetworkID;

The table COMPUINV is the single table that holds all of the
information. All other tables are new and related
together(Normalized) I want to populate those table from the
existing single table. I hope this makes things clearer.



I currently have the computer inventory at work in one table. I
have created new tables with relationships and I want to append the
data in the existing table so that it populates all of the new
tables. Thus keeping employees associated with their PC, printers,
etc. I have tried creating an append query but when ran it says it
will append 0 rows. Any idea?

It's almost NEVER either necessary or a good idea to populate new
tables with empty "placeholder" records, if that's what you mean.
Are you trying to extract data from a single wide-flat table into
multiple normalized tables? If so, note that the Append query
should be based JUST on the wide-flat table; if you join it to the
target table, you will select only those records which already
exist in the target (none, that is). Perhaps you could post the SQL
view of the append query.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top