Finding Customer Thats Spent The Most (1 Viewer)

[danielemyr]

Hello,

I am working on a database at the moment to try and find the customer that has spent the most money. At the moment i can only get the customers that have placed the most orders. I have a dispatch table that consists of all the orders and a customer table that are linked using a Customer Number. Each order has a dispatch number because one customer can buy more than one product per order. Like i said at the start I'm trying to find the customer that has spent the most money but the top 10 would be better. I havent used Access in a long time and I've just been bluffing my way through up til now. I have never used any code in Access before so the simplest way would be the best for me. Any advice would be greatly appreciated.

Thanks

 

[WhiteRex]

Do you have separate columns for each separate order that has been placed?

 

[danielemyr]

I have created a append query to find out how many individual orders there are and because of the way the relationship has been set up i can see how many product's that are attached to that order. So i have a table that just has a list of order numbers in if that helps.

 

[jdraw]

Please get a screen capture of your tables and relationships; save it as a jpg; then create a zip and attach it to a post. You must use zip because of your post count.

 

[danielemyr]

here is the relationship. I know its probably overly complicated and messy to what it needs to be. A lot of it isn't related to my question but here it is.

 

[jdraw]

What is SQF? SQFProduct vs Product.?? I don't understand your diagram.
See the free videos here for Customer--Order--OrderDetail--Product/Item set up(typical).

Can you give us an overview of your business in plain English so that we might follow the diagram?

 

[danielemyr]

Ye sorry. Basically SQF is a replacement so a product that needs to be sent out so a customer says that there missing a item or its broken etc then that information gets stored in that table. The related tables like SQF product is a way of keeping track of showing how many times a particular product needed to be replaced to see if there are any common denominators with the product eg broken in some way. I wanted to keep the SQF (Replacements) side away from the orders side of things. I hope this helps.

 

[jdraw]

I suggest you watch the videos. They will give you an overview of table design and the concepts underlying the design.