Unmatched query help please 1

[Zonie32]

Using Access 2003, Win XP.

I have 2 tables. tblMGA (3856 records) and tblPPO (4595 records). Each have the exact same fields.

PPOID = text
State = text
Zip = text
Network = text
Factor = number

I want to show any state and zip for each of the PPOID's where the Factors are different between the 2 tables. I've tried the Unmatched query wizard with no luck. It looks like it duplicates the PPOID's, States and Zips. I end up with 1,366,432 records!

 

[lespaul]

something like this maybe?

SELECT STATE, ZIP FROM tblPPO P M LEFT JOIN tblMGA M on P.PPOID = M.PPOID WHERE P.FACTOR <> M.FACTOR

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Zonie32]

thanks for the quick response. I pasted the code and it says "Syntax error in From Clause" and it highlights the M after tblPPO P "M

 

[lespaul]

my bad! try this instead:

SELECT STATE, ZIP FROM tblPPO As P LEFT JOIN tblMGA As M on P.PPOID = M.PPOID WHERE P.FACTOR <> M.FACTOR

 

[Zonie32]

well, now it says the field "State" could refer to more than one table listed in the From clause.

Sorry, I'm new to SQL and Access so I don't understand too much code.

 

[lespaul]

well at least we're making progress, again, me just not thinking clearly enough!

SELECT P.STATE, P.ZIP FROM tblPPO As P LEFT JOIN tblMGA As M on P.PPOID = M.PPOID WHERE P.FACTOR <> M.FACTOR

hopefully that will do it!

les

 

[Zonie32]

well, here is the latest error...

syntax error(missing operator) in query expression P.PPOID=M.PPOID

maybe one more try?? thanks.

 

[lespaul]

Are you using this exact statement? could you paste the SQL of the query you tried?

thanks,
leslie

 

[Zonie32]

Here is my query.

SELECT P.STATE, P.ZIP FROM tblPPO As P LEFT JOIN tblMGA As M on P.PPOID = M.PPOID WHERE P.FACTOR <> M.FACTOR

It actually opens up with no errors however it lists each State and Zip 535 times. So I end up with over 1,000,000 records!

 

[lespaul]

ok then add DISTINCT:

SELECT DISTINCT P.STATE, P.ZIP FROM tblPPO As P LEFT JOIN tblMGA As M on P.PPOID = M.PPOID WHERE P.FACTOR <> M.FACTOR