A query to find which customer ordered the most products?

[roadrage]

I have three tables (Customer, Order, CD's) I would like to know if it is possible to run a query that finds which customer has ordered the most Cd's?

I have tried the following in an access query:

FirstName
TBL_Customer
Group by

OrderNumber(text)
TBL_Order
Max

And i have tried

FirstName
TBL_Customer
Group by

OrderNumber(text)
TBL_Order
Max

And i have tried

FirstName
TBL_Customer
Group by

OrderDate
TBL_Order
Max

and

FirstName
TBL_Customer
Group by

OrderNumber
TBL_Order
Count

All of those give me nothing like what i would like to see. Would anybody here have any ideas?

 

[jzwp22]

Can you provide more detailed info about your tables?

What table holds the order details assuming that a customer can order many different items and multiples thereof?

 

[roadrage]

Can you provide more detailed info about your tables?

What table holds the order details assuming that a customer can order many different items and multiples thereof?

Customer table:
Id
title
Name
Address
Credit card details

Cd table:
Id
Cd number
Cd title
Artist

Order table:
Order number
Order date
Cust id
Cd id

Hope that's enough info.

 

[DCrake]

What seems to be missing is an order quantity. You should be grouping by customer and summing the order quantity sorting this field descending (highest first)

 

[jzwp22]

With the structure you indicated, the customer can only order 1 CD per order. What happens if they want to order more than 1. What about a quantity field for those people who want to order more than 1 copy of a CD?

With your current structure, to get a count by customer, you would use a query like this

query name: qryCountByCustomer
SELECT [Cust id], Count([Cd id]) as CountByCustomer
FROM [order table]
GROUP BY [Cust id]

You would then need to create another query that uses the above query to get the customer with the maximum count

 

[roadrage]

With the structure you indicated, the customer can only order 1 CD per order. What happens if they want to order more than 1. What about a quantity field for those people who want to order more than 1 copy of a CD?

With your current structure, to get a count by customer, you would use a query like this

query name: qryCountByCustomer
SELECT [Cust id], Count([Cd id]) as CountByCustomer
FROM [order table]
GROUP BY [Cust id]

You would then need to create another query that uses the above query to get the customer with the maximum count

Thanks I'll try that

 

[jzwp22]

Getting the max can be a little tricky since you will need a subquery. It will look something like this (using the query I provided earlier)

SELECT qryCountByCustomer.[Cust id], qryCountByCustomer.CountByCustomer
FROM qryCountByCustomer
WHERE qryCountByCustomer.CountByCustomer= (SELECT Max(Q1.CountByCustomer) AS MaxOfCountByCustomer
FROM qryCountByCustomer as Q1)



I'm still not sure if you table structure is right for what you are trying to do. Could you provide some more details on what your application is designed to do?

 

[roadrage]

Getting the max can be a little tricky since you will need a subquery. It will look something like this (using the query I provided earlier)

SELECT qryCountByCustomer.[Cust id], qryCountByCustomer.CountByCustomer
FROM qryCountByCustomer
WHERE qryCountByCustomer.CountByCustomer= (SELECT Max(Q1.CountByCustomer) AS MaxOfCountByCustomer
FROM qryCountByCustomer as Q1)



I'm still not sure if you table structure is right for what you are trying to do. Could you provide some more details on what your application is designed to do?

It's a database ive put together as part of a course I'm delivering so the actual functionality of it needs to be limited, I'm just trying to identify some of the trends in data. Thanks so much for your help!

 

[jzwp22]

You're welcome. Please post back if you need additional help.