Help Totalling By Specific Values

[vicsalt]

Simple Terms
Table with three fields: "MC" "Date" "Parts Made"

My query selects a date range

I want the query to give the "total parts made" on each machine between the two dates specified.

I don't want it to show every record for each machine, just the total parts made for each machine.

Thankyou for your help

 

[pbaldy]

Create a query with the three fields. In design view, click on the Totals icon. Change Group By to Sum and Where on the appropriate fields, add your criteria and test.

 

[vicsalt]

Nope - Not that straight forward lol , I need totals across a date range your solution simply totals the parts made each day. My query could span days, weeks, months ect. I simply want the total across each machine, thus if I have 3 machine numbered 1,2,3 and we mad 100 parts per day and my query spanned 3 days my results need to look like: MC 1: 300, MC 2: 300, MC 3: 300.
hope this makes sence

 

[Tieval]

In the query delete the "group by" from dates and just set criteria for your date range.

 

[kloklo]

When you are in the design view, gaze to the right side of the Access ribbon. You will see a big sigma symbol (∑).
Click on it and you will see a new "Total" row in the query design grid.

Choose "group by" for MC and "Sum" for the Parts made.
Hit "Run" and check out if that is the result you wanted to see

ps. I hope you don't actually have a field named "Date" in your table. It is a reserved word in Access. Would be really good to change it into ProductionDate or Production_date or whatever naming convention you prefer

 

[kloklo]

LOL...I have to type faster...when I started there were no replies yet

 

[vicsalt]

lol - Ive done this lot no joy The date criteria is Between XXXXX And XXXX it stll shows the total parts for each machine every day. However I have a solution...

Create query with Date field which collects all the records between the dates required.
Create a second query using the 1st query but only select the MC field "Grouped By" and Parts Made as "Sum" Bingo

 

[pbaldy]

Like I said, change Group By to Where on the date field.