Query design help

[Carol Frey]

I want to query where one field is the same and another field is different. For example, where invoice number field is the same and the vendor number field is different.

I got the first part as: In (SELECT [Invoice Number] FROM [combo] As Tmp GROUP BY [Invoice Number] HAVING Count(*)>1 ) but I can't figure out how to at the same time get where the vendor number field is different.



Any help is appreciated.

Thanks,

[ranman256]

SELECT * from tData , tData as tData1 where
tData.[invoice number]= tData1.[invoice number]
and
tData.[vendor number]<> tData1.[vendor number]

[Carol Frey]

Thank you, but I am trying to find the same and different data in the same file, not from two different files.

[June7]

You want to list invoice numbers with multiple vendors and you want to show the vendors as well?

Ranman's SQL is using only 1 table. He is doing a Cartesian relationship self-join by using alias name - tData AS tData1

Try another approach:

SELECT * FROM table WHERE [Invoice Number] IN (SELECT [Invoice Number] FROM table WHERE Count([Vendor])>1 GROUP BY [Invoice Number]);

However, this won't work if there can be duplicate invoice/vendor pairs in the table. Will that be possible? Might provide sample data.