ummmm... finding duplicate rows

[mcchung52]

I have 4 columns in a table:
id (primary)
vendor (which is a foreign key for vendor table)
vendorno (which is actually a product number from that vendor)
product (which is a foreing key for product table)*



*I'm separating the vendorno from the product table
so product table is general in definition so two different vendor
carrying the same product would point to one product id.
I don't know if this was a smart move but oh well...

I want to pull, for a given vendor, all duplicate vendornos
which cannot happen either with same or different product id
because vendorno should be a unique number from that vendor.

I'm currently struggling with this:
SELECT DISTINCT vendor, vendorno, COUNT(vendorno) AS NumOcc
FROM vendor_map
GROUP BY vendor, vendorno
HAVING (count(vendorno)>1);

any insight??

BONUS TIP: if you also know how to call from the vendor table to
substitute in the final query result the actual name of the vendor
instead of the id that'll be super!!

[June7]

Query looks good. What is problem? Error message, wrong results, no results?

Include the vendor table in the query by join and include the name in the GROUP BY.

[mcchung52]

nothing shows up on query. no result.
I did some manual check and saw that for
vendor and vendorno distinct combo, there were
no duplicates so nothing should be returned.

Then I figured I need to look for a different thing. (sorry...)
How do I write a query where
(all vendor, vendorno combo are distinct but) the fourth
column, product, cannot have a duplicate product id
within the same vendor?
ex. for a similar looking vendorno but different, there were
duplicate product id, which is most likely an erroraneous
user input.

this is what I have so far:
SELECT vendor, vendorno, drug
FROM vendor_map
GROUP BY vendor, drug
HAVING (count(drug)>1);


June7, can you show me how I do
that join and group by query?

[June7]

Building queries with table joins is basic Access functionality. Access Help has guidelines on using the query designer. Here is a tutorial http://office.microsoft.com/en-us/ac...010096320.aspx