SQL Query in Access

[Greg]

Guys,

Searched high and low and I can't find anything at all for this.

I'm trying to create a query in Access that'll allow me to search a table, call it tbl_products and pick out from this data set all the products that are sold in different locations.

e.g.

LocationID,ProductID
1,1
1,2
1,3
2,1
2,4
2,5

So the query would pull back the result that ProductID 1 is found at both LocationID 1 & 2

I'm not sure what SQL statements to use for this at all, all the data is in one table tbl_products

Any help is greatly appreciated!

Regards

 

[AshenShugar1873]

This will return the LocationId and ProductId of all ProductIds that occur more than once in the table.

SELECT tbl_products.LocationId, tbl_products.ProductId
FROM tbl_products INNER JOIN
(SELECT tbl_products.ProductId, Count(*) AS Total
FROM tbl_products
GROUP BY tbl_products.ProductId
HAVING (((Count(*))>1))) Products
ON tbl_products.ProductId = Products.ProductId;

This uses subqueries which aren't to everyones taste but should do what you need. The subquery starts on line 3 and gets all productIDs with more than one occurance using a count and names the subquery Products which is then used in the join clause.

 

[Greg]

Fantastic! Thank you so much. this worked perfectly!