Query to calculate repeated names

[AccessDon99]

I have a simple table that is a dump of individual sales events from a small ecommerce site over a one year period. The relevant fields included in the table are an Account Name (text) and a trasaction amount, I am interested in determining the number of repeat customers in that period and how many times each repeat customer shopped. Ideally, I would also like to calculate the average transaction amount for each repeat customer. I am probably as new to Access as one can be. Thanks to anyone who can help.

 

[monkeytunes]

There are a number of ways you could set this up - for ease of viewing, I usually use "Count" fields in the query, and "Sum" fields in text boxes on forms/subforms.

If you just need to look at this quickly, you might have some luck using "Find Duplicates" queries, which Access can set-up for you using wizards. Just click "New" on the Queries screen, and one of the option is "Find Duplicates". You can modify/set parameters/etc. as you need from there.

Depending on how permanent you need this customer count to be, that will determine what sort of structure you should use.

 

[DLB]

You can build a Totals query.


In query design, add the table, then drag the Account Name field to the first and the second columns, and drag the Amount field to the third column.

Click on the Totals button on the toolbar so that a Total: row appears in the grid.

In the second column, change Group By to Count and put >1 in the Criteria.

In the third column, change Group By to Avg


as in the image attached.

 

[AccessDon99]

Thanks so much

Great info, solved the problem, and gave me a bit more understanding of the program. Thanks for your generous help!