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
|
|||
|
|||
Question about appending new data to an existing table
I have a table that holds specific vendor IDs - for critical vendors. I get
this information from a table that holds all Vendor IDs. I need to have a query that finds new vendor IDs and ignores the existing ones between the two tables. Original table with all Vendor IDs = Vendors [LIVendorID], [binCritical]. The table to be updated = CriticalVendors [LIVendorID]. The reason I'm appending the data to the new table is because I can't modify the original table (its in another database) and I only need Critical Vendors in my database. I have added a frequency field to my table CriticalVendors that tells me how often I need to evaluate my vendors. I don't want to update existing vendors - just add new critical vendors to the table. Sorry for the long-winded explanation but hope it is helpful. |
#2
|
|||
|
|||
Question about appending new data to an existing table
Bob
Let's see if I can paraphrase... You have a list of vendors, each with a unique ID. Some of them you want to be able to identify as "critical". If this is accurate, why would you want to have the same vendor show up in two different tables? That's a formula for bad data, just waiting to happen. Instead, what about the possibility of adding a single field to the original list, to indicate "criticality"? Regards Jeff Boyce Microsoft Office/Access MVP "Bob Waggoner" wrote in message ... I have a table that holds specific vendor IDs - for critical vendors. I get this information from a table that holds all Vendor IDs. I need to have a query that finds new vendor IDs and ignores the existing ones between the two tables. Original table with all Vendor IDs = Vendors [LIVendorID], [binCritical]. The table to be updated = CriticalVendors [LIVendorID]. The reason I'm appending the data to the new table is because I can't modify the original table (its in another database) and I only need Critical Vendors in my database. I have added a frequency field to my table CriticalVendors that tells me how often I need to evaluate my vendors. I don't want to update existing vendors - just add new critical vendors to the table. Sorry for the long-winded explanation but hope it is helpful. |
#3
|
|||
|
|||
Question about appending new data to an existing table
I have a list of vendors in a table I don't control but need to use.
I need to add frequency of evaluation to the vendor. So I created a new table in my database and appended the critical vendors (a very limited number) to the new table. Then I select the frequency in that new table. I just want to add new vendors and have a query that ignores vendors already in my table. In other words, can I write a query that checks to see if there are any vendors in the other database table that have a designation as critical - but aren't in my table - and have it append the new records to my table? Bob "Jeff Boyce" wrote: Bob Let's see if I can paraphrase... You have a list of vendors, each with a unique ID. Some of them you want to be able to identify as "critical". If this is accurate, why would you want to have the same vendor show up in two different tables? That's a formula for bad data, just waiting to happen. Instead, what about the possibility of adding a single field to the original list, to indicate "criticality"? Regards Jeff Boyce Microsoft Office/Access MVP "Bob Waggoner" wrote in message ... I have a table that holds specific vendor IDs - for critical vendors. I get this information from a table that holds all Vendor IDs. I need to have a query that finds new vendor IDs and ignores the existing ones between the two tables. Original table with all Vendor IDs = Vendors [LIVendorID], [binCritical]. The table to be updated = CriticalVendors [LIVendorID]. The reason I'm appending the data to the new table is because I can't modify the original table (its in another database) and I only need Critical Vendors in my database. I have added a frequency field to my table CriticalVendors that tells me how often I need to evaluate my vendors. I don't want to update existing vendors - just add new critical vendors to the table. Sorry for the long-winded explanation but hope it is helpful. |
#4
|
|||
|
|||
Question about appending new data to an existing table
Bob
I guess I'm not yet clear on that original table. Are you saying that there's something recorded in that original table that let's you know that the vendor is "critical"? If so, again, why bother copying the vendor over if you already have a way to tell who they are? I don't understand "then I select the frequency in that new table". What frequency? If you use the vendor_ID as your primary key, primary keys don't allow duplicates. You could, if you must create a table that duplicates some of the vendors, use the primary key to prevent adding the same vendor more than once. Regards Jeff Boyce Microsoft Office/Access MVP "Bob Waggoner" wrote in message ... I have a list of vendors in a table I don't control but need to use. I need to add frequency of evaluation to the vendor. So I created a new table in my database and appended the critical vendors (a very limited number) to the new table. Then I select the frequency in that new table. I just want to add new vendors and have a query that ignores vendors already in my table. In other words, can I write a query that checks to see if there are any vendors in the other database table that have a designation as critical - but aren't in my table - and have it append the new records to my table? Bob "Jeff Boyce" wrote: Bob Let's see if I can paraphrase... You have a list of vendors, each with a unique ID. Some of them you want to be able to identify as "critical". If this is accurate, why would you want to have the same vendor show up in two different tables? That's a formula for bad data, just waiting to happen. Instead, what about the possibility of adding a single field to the original list, to indicate "criticality"? Regards Jeff Boyce Microsoft Office/Access MVP "Bob Waggoner" wrote in message ... I have a table that holds specific vendor IDs - for critical vendors. I get this information from a table that holds all Vendor IDs. I need to have a query that finds new vendor IDs and ignores the existing ones between the two tables. Original table with all Vendor IDs = Vendors [LIVendorID], [binCritical]. The table to be updated = CriticalVendors [LIVendorID]. The reason I'm appending the data to the new table is because I can't modify the original table (its in another database) and I only need Critical Vendors in my database. I have added a frequency field to my table CriticalVendors that tells me how often I need to evaluate my vendors. I don't want to update existing vendors - just add new critical vendors to the table. Sorry for the long-winded explanation but hope it is helpful. |
#5
|
|||
|
|||
Question about appending new data to an existing table
On Tue, 7 Apr 2009 09:35:03 -0700, Bob Waggoner wrote:
I have a table that holds specific vendor IDs - for critical vendors. I get this information from a table that holds all Vendor IDs. I need to have a query that finds new vendor IDs and ignores the existing ones between the two tables. Original table with all Vendor IDs = Vendors [LIVendorID], [binCritical]. The table to be updated = CriticalVendors [LIVendorID]. The reason I'm appending the data to the new table is because I can't modify the original table (its in another database) and I only need Critical Vendors in my database. I have added a frequency field to my table CriticalVendors that tells me how often I need to evaluate my vendors. I don't want to update existing vendors - just add new critical vendors to the table. Sorry for the long-winded explanation but hope it is helpful. Just of the top of my head I might try: INSERT INTO CriticalVendors (VendorID, binCritical) SELECT a.VendorID, a.binCritical FROM Vendors AS a WHERE a.binCritical = 1 AND NOT EXISTS (SELECT b.VendorID FROM CriticalVendors AS b WHERE b.vendorID = a.vendorID); |
Thread Tools | |
Display Modes | |
|
|