Question about appending new data to an existing table

[Bob Waggoner]

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.

 

[Jeff Boyce]

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]

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

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

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.

 

[Jeff Boyce]

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

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

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

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.

 

[Michael Gramelspacher]

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);