Access Query Type?

[gheyman]

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.

 

[xenou]

One way is to use a series of three queries run each one in turn:

/* 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).

 

[gheyman]

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.

 

[xenou]

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

 

[gheyman]

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?

 

[xenou]

Not sure. I would need to inspect your generated SQL query (and possibly get a list of fields from both tables).