Comparing some entries of the table to another table

[mach27]

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!

 

[traingamer]

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

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

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

 

[mach27]

Hi Greg

I'm having a problem with this part:

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"


Mach

 

[traingamer]

Did you specify the table alias?

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