• Status: Solved
• Priority: Medium
• Security: Public
• Views: 382
• Last Modified:

# parameter query to sum daily quantity for each month

Experts,
I have two tables - tblPartNumber, tblQuanityScrapped
tblQuantityScrapped fields used:
ScrapDate
PartNumberID
QuanityScrapped

tblPartNumber
PartNumberID
PartNumber
PartNumberDescription

My query is a date parameter query on ScrapDate. What has been requested is a query that totals the quantity of each part for the month. For example Part 123 could have 25 for Month 1, Part 124 could have 35 for Month 1 with the process being repeated for each month in the query.
0
Frank Freese
Asked:
• 3
4 Solutions

Commented:
This do it?

SELECT a.PartNumber, a.Description, Month(b.ScrapDate) as MonthNum Sum(b.QuantityScrapped) as MonthlyTotal FROM tblPartNumber a INNER JOIN tblQuantityScrapped b ON a.PartNumberID = b.PartNumberID GROUP BY a.PartNumber, a.Description, Month(b.ScrapDate);
0

Commented:
Try this

SELECT PartNumberID, Sum(QuanityScrapped) AS SumScraped
FROM tblPartNumber INNER JOIN  tblQuanityScrapped
ON tblPartNumber.PartNumberID= tblQuanityScrapped.PartNumberID
HAVING ScrapDate between [Start Date] AND [End Date]
0

Commented:
Come to think of it, if the table has multi-year values consider this:

SELECT a.PartNumber, a.Description, Format(b.ScrapDate,"yymm") as YrMonNum Sum(b.QuantityScrapped) as MonthlyTotal FROM tblPartNumber a INNER JOIN tblQuantityScrapped b ON a.PartNumberID = b.PartNumberID GROUP BY a.PartNumber, a.Description, Format(b.ScrapDate,"yymm");
0

Commented:
If you wanted to limit it to a given year:

SELECT a.PartNumber, a.Description, Format(b.ScrapDate,"yymm") as YrMonNum Sum(b.QuantityScrapped) as MonthlyTotal FROM tblPartNumber a INNER JOIN tblQuantityScrapped b ON a.PartNumberID = b.PartNumberID
WHERE Year(b.ScrapDate) = [Enter Year nnnn]
GROUP BY a.PartNumber, a.Description, Format(b.ScrapDate,"yymm");

When prompted you would enter a 4 digit year.
0

## Featured Post

• 3
Tackle projects and never again get stuck behind a technical roadblock.