Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Comparing some entries of the table to another table

Status
Not open for further replies.

mach27

Technical User
Oct 25, 2005
18
Hello Techies

What I would like to do is to compare some entries of the table to another table to check if they have the same value, here's my condition:
If the 2 entries being compared will not match, the result will be posted to the 3rd table using the entry of the 2nd table.
If the information in table 1 is not in table 2, the result will be posted to the 3rd table using the entry of the 1st table.
If the information in table 2 is not in table 1, the result will be posted to the 3rd table using the entry of the 2nd table.

Submission_ID and Request_Date are the common fields and will not change.


Table 1: (MPO_Dashboard)
Submission_ID
Request_Date
Recipient
OpsModel
Jan
Feb

Table 2: (MMR_Dashboard)
Submission_ID
Request_Date
Recipient
OpsModel
Jan
Feb

Table 3: (Dashboard_result)
Submission_ID
Request_Date
Recipient
OpsModel
Jan
Feb


I'm thinking of using the recordset but the loop part makes the program slow and most of the time Access is not responding.

Thanking you all in advance! Have a nice day!

;)
 
You can do this with two append queries. (You can combine your first two conditions - if it's not in table 1 OR it's different then use table 2 data).
Code:
Insert into Dashboard_result ( Submission_ID,  Request_Date, etc. )
Select mmr.Submission_ID,  mmr.Request_Date, etc.  
from MMR_Dashboard mmr

Then use an unmatched query to append the rest of the records from table 1
Code:
Insert into Dashboard_result ( Submission_ID,  Request_Date, etc. )
Select mpo.Submission_ID,  mpo.Request_Date, etc.  
from MPO_Dashboard mpo
    inner join MMR_Dashboard mmr on 
    mpo.Submission_ID = mmr.Submission_ID and
    mpo.Request_Date = mmr.Request_Date

where mmr.Submission_ID is null


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard
 
Hi Greg

I'm having a problem with this part:

Code:
mpo.Submission_ID = mmr.Submission_ID and
mpo.Request_Date = mmr.Request_Date

It gives me an error message of "Syntax error (missing operator)in query expression"

[peace]
Mach
 
Did you specify the table alias?

Code:
...
from MPO_Dashboard [b][blue]mpo[/blue][/b]
    inner join MMR_Dashboard [b][blue]mmr[/blue][/b] on 
...

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top