Access Query Type?

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
I have two tables. Both have a field called "Quote Number". One table is called "Quotes Master Table" and the other tblProposalStatus

I need a list of all Quote Numbers that are in the Quotes Master Table and not in the tblProposalStatus.

I will then need to append those Quote Numbers into tblProposalStatus

I'm looking for some recommendation as to how to achieve this.

Note the Quote Master Table is Linked and I do not have the ability to make changes to it. The tbleProposal is located in my database.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
One way is to use a series of three queries run each one in turn:

Code:
/* PSEUDOCODE create table XTMP00100 (QuoteNum Long Integer) */

delete XTMP00100.* from XTMP00100

insert into XTMP00100 (QuoteNum)
	select QuoteNum from [Quotes Master Table] A
	where not exists (select * from tblProposalStatus B where B.QuoteNum = A.QuoteNum)

insert into tblProposalStatus (QuoteNum) select QuoteNum from XTMP00100

This assumes your staging table exists (here called XTMP00100) - I wouldn't actually do that in SQL, just create the table in Access the usual way.



Another option may be possible. If QuoteNum is unique in tblProposal then you could create a unique index on QuoteNum in the table, and the just insert directly from the master table (Access will then discard duplicates and insert only the records that aren't already there).
 
Upvote 0
Thanks Xenou; Not sure I know enough to do what you suggest. Your Pseudo code is throwing me. I guess its code to make a new table - which I dont know how to create a table using VBA. But I will give it a shot.

I tried an Unmatched Query then an Append Query. With my tblProposalStatus table being empty (No Records) I run my Unmatched query and it gives me 132 records (Which is correct). I then run my Append Query which appends 132 Quote numbers. All work well up to this point.

But when I run this again - in theory I should get zero records from my unmatched. But I dont. I get 24 records. These 24 Quote numbers are alreadt in my tblProposalStatus from the first time I ran the Unmatched and then Append query. for some reason it thinks these 24 are uniques - but they are not. So now I am ending up with 24 duplicates in my tblProposalStatus.
 
Upvote 0
I would need to see your actual unmatched query. Keep in mind that "matched" all depends on what you are matching - not necessarily just one field (quotenum) but possibly more than one. At any rate, I don't use the wizard I just write my own queries, so I'd need to see the SQL the wizard generates. Edit (or you can use my SQL above :) )

As far as pseudocode goes as I stated above I wouldn't actually use SQL for that (hence why it is pseudocode and why it is commented out). It is only there to make clear that the table must exist and what it's structure is expected to be). As far as creating it, I'd just do it the normal way (hit the create table button and take it from there).
 
Last edited:
Upvote 0
Thanks. Any idea why my code brings up some of the same quote numbers as it previously did (Creating duplicates in the second tbl)? Is it because I am only Matching one field in the two tables?
 
Upvote 0
Not sure. I would need to inspect your generated SQL query (and possibly get a list of fields from both tables).
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top