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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Appending date from one table to multiple tables
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? |
#2
|
|||
|
|||
Appending date from one table to multiple tables
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... -- More info, please ... Jeff Boyce Access MVP |
#3
|
|||
|
|||
Appending date from one table to multiple tables
On 27 Jul 2004 23:06:11 -0500, Stranger wrote:
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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#4
|
|||
|
|||
Appending date from one table to multiple tables
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. John Vinson wrote in : On 27 Jul 2004 23:06:11 -0500, Stranger wrote: 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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#5
|
|||
|
|||
Appending date from one table to multiple tables
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; Stranger wrote in : 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? |
#6
|
|||
|
|||
Appending date from one table to multiple tables
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 Stranger wrote: 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. John Vinson wrote in : On 27 Jul 2004 23:06:11 -0500, Stranger wrote: 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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#7
|
|||
|
|||
Appending date from one table to multiple tables
That looks like it makes more since. If I do that, will I keep the
employees associated with their respective computer, etc? "John Spencer (MVP)" wrote in : 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 Stranger wrote: 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. John Vinson wrote in : On 27 Jul 2004 23:06:11 -0500, Stranger wrote: 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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#8
|
|||
|
|||
Appending date from one table to multiple tables
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. Stranger wrote: That looks like it makes more since. If I do that, will I keep the employees associated with their respective computer, etc? "John Spencer (MVP)" wrote in : 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 Stranger wrote: 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. John Vinson wrote in : On 27 Jul 2004 23:06:11 -0500, Stranger wrote: 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. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |
Northwind | Mike | Database Design | 3 | May 21st, 2004 03:03 PM |
Cannot join 1:M table into M:M tables | Tom | Database Design | 4 | May 19th, 2004 10:16 PM |
Does date fall between two ranges? | MR | Worksheet Functions | 4 | January 14th, 2004 04:08 PM |