Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL query help 1

Status
Not open for further replies.

pwinstanley

IS-IT--Management
Nov 23, 2000
22
GB
I'm trying to create a best sellers chart for my eCommerce site. I have put in place the following query

SELECT dbo_OrderDetails.productID, sum(Quantity) as Quantity
FROM dbo_OrderDetails GROUP BY dbo_OrderDetails.productID
ORDER BY 2 DESC

which gives me the productID with most sales at the top and works fine.

My problem is that I need to get the productname from another table, the Products table. The orderDetails and Product table are related through the productID field.

Thing is as soon as I introduce another element to the group by section it all goes wrong. Do I need to use a join as well rather than stating from dbo_OrderDetails, dbo.Product?

Argggghhh!! Please help!

Cheers
Paul
 
Hi Paul,

The easiest option would be to create an access query and unite the tables through the common relationship. If you then use this as the source in your rs setup dialog (ie create a query called qryProduct, the inner join between the tables would be created by Access and maintained when using the qry as the source for filtering by ID or whatever.

Hope that helps

M "There are 3 kinds of people; those that can count and those that can't"
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top