How to do this in Access

[accessmatt]

Hello June7, one other question that occurred to me on this: does the query use the ID of the primary table as the matcher. i.e. how does it know which two rows to compare?
Thanks!

[June7]

I have done that with the wizard. No idea why you can't.

Once you have the query built and saved with one match, use the query designer to add other fields to the grid and apply Is Null parameter.

[accessmatt]

Ok well I was able to manually add them through the query designer however I'm not seeing the expected results. I'd like to compare Clicks, Impressions and Cost in the two tables, they don't match, but I'm not seeing them show up in the output. Also, could you comment about whether the query is using ID to link the two tables for compare?

Thanks!

[June7]

Linking on the ID fields is meaningless because they are both autonumber.

I used the wizard and this is the SQL:

SELECT [Paid Media Output].Website
FROM [Paid Media Output] LEFT JOIN [SEM Portfolio Report] ON [Paid Media Output].[Cost] = [SEM Portfolio Report].[Pub Cost]
WHERE ((([SEM Portfolio Report].[Pub Cost]) Is Null));

The query shows:

Website
Website1.com
Website2.com

[accessmatt]

June7, 2 questions:

From this query, it doesn't look like you're comparing on 3 but rather only 1 field (Cost).

Linking on the ID fields is meaningless because they are both autonumber.

I used the wizard and this is the SQL:

SELECT [Paid Media Output].Website
FROM [Paid Media Output] LEFT JOIN [SEM Portfolio Report] ON [Paid Media Output].[Cost] = [SEM Portfolio Report].[Pub Cost]
WHERE ((([SEM Portfolio Report].[Pub Cost]) Is Null));

The query shows:

Website
Website1.com
Website2.com

[June7]

Sorry, that makes me recall another reason why I avoid the wizards. You were right, the wizard only matches on the last pair that is selected. So I built the query manually. See if this gets what you want.


SELECT [Paid Media Output].Website
FROM [Paid Media Output] LEFT JOIN [SEM Portfolio Report] ON ([Paid Media Output].Clicks = [SEM Portfolio Report].Clicks) AND ([Paid Media Output].Impressions = [SEM Portfolio Report].Impr) AND ([Paid Media Output].[Cost] = [SEM Portfolio Report].[Pub Cost])
WHERE ((([SEM Portfolio Report].[Pub Cost]) Is Null)) OR ((([SEM Portfolio Report].Impr) Is Null)) OR ((([SEM Portfolio Report].Clicks) Is Null));

or

SELECT [Paid Media Output].Website
FROM [Paid Media Output] LEFT JOIN [SEM Portfolio Report] ON [Paid Media Output].Website = [SEM Portfolio Report].Website
WHERE ((([Paid Media Output].Cost)<>[SEM Portfolio Report].[Pub Cost])) OR ((([Paid Media Output].Impressions)<>[SEM Portfolio Report].[Impr])) OR ((([Paid Media Output].Clicks)<>[SEM Portfolio Report].[Clicks]));