Create Totals (1 Viewer)

Number11

Member
Local time
Today, 10:24
Joined
Jan 29, 2020
Messages
62
How do you create a query that totals up products sold for a day. week and say Month
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 04:24
Joined
Feb 28, 2001
Messages
16,253
Generally, you have to have a table of transactions (sales, if you prefer) that include a date and a quantity of items sold and WHAT was sold. This might in fact be a parent-child table involving a sales invoice that probably includes the date of sale and a line-item table that lists the quantity sold of each item individually.

Once you have that, you can create a JOIN query of Invoices to LineItems showing all line items grouped by invoice number or by date or by item code. Once you have a grouping (look up GROUP BY for details), you can do what is called an SQL Aggregate such as SUM() or COUNT(). To get total sales you would probably use the SUM() aggregate. To limit the date range, you put your date restrictions in a WHERE clause. There are only about a gazillion ways to do that so you would need to tell us more about your setup.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:24
Joined
Feb 19, 2013
Messages
11,828
you also need to be clear what you mean by 'day, week, month' i.e. what you want it to look like - e.g. yesterday, last seven days, last 30 days? something else. Recommend you also provide some example data and what you want from that example data
 

theDBguy

I’m here to help
Local time
Today, 02:24
Joined
Oct 29, 2018
Messages
8,713
Hi. A Totals query can be easy to use, but it can get complicated if you want to show several categories at the same time. Just a thought...
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 04:24
Joined
Feb 28, 2001
Messages
16,253
One last bit of general advice. Before you start thinking about how you want to implement anything, you MUST (absolutely and unhesitatingly MUST) decide WHAT you want to implement. I.e. don't set out on a trip if you don't have a destination in mind.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom