[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

query to group by month

Posted on 2008-11-07
4
Medium Priority
?
838 Views
Last Modified: 2012-05-05
Experts,
Here's my objective. To create a query where I sum quantity scrapped for each month by part number and line code. I got this far

SELECT tblScrapTicket.ScrapTicketDate, tblLineCode.LineCodeNumber, tblLineCode.LineCodeDescription, tblPartNumber.PartNumber, tblPartNumber.PartNumberDescription, tblScrapTicket.QuantityScrapped
FROM tblPartNumber INNER JOIN (tblScrapTicket INNER JOIN tblLineCode ON tblScrapTicket.LineCodeID = tblLineCode.LineCodeID) ON tblPartNumber.PartNumberID = tblScrapTicket.PartNumberID
WHERE (((tblScrapTicket.ScrapTicketDate) Between [Beginning Date] And [Ending Date]))
ORDER BY tblPartNumber.PartNumber;

0
Comment
Question by:Frank Freese
  • 2
4 Comments
 
LVL 2

Expert Comment

by:tomasgruener
ID: 22905114
I would add a group by clause, which groups for the year and the month.
I'm not very used to access sql syntax, but I think you have to add following line befor the ORDER BY Clause

GROUP Format(tblScrapTicket.ScrapTicketDate, "YY.MM")
(include the year in the group clause, so you can group correctly, even if you group over several years)
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 22906212
check whether this helps

SELECT Format(tblScrapTicket.ScrapTicketDate, "YY.MM")tblLineCode.LineCodeNumber, tblLineCode.LineCodeDescription, tblPartNumber.PartNumber, tblPartNumber.PartNumberDescription, sum(tblScrapTicket.QuantityScrapped)
FROM tblPartNumber INNER JOIN (tblScrapTicket INNER JOIN tblLineCode ON tblScrapTicket.LineCodeID = tblLineCode.LineCodeID) ON tblPartNumber.PartNumberID = tblScrapTicket.PartNumberID
WHERE (((tblScrapTicket.ScrapTicketDate) Between [Beginning Date] And [Ending Date]))
groub by Format(tblScrapTicket.ScrapTicketDate, "YY.MM")tblLineCode.LineCodeNumber, tblLineCode.LineCodeDescription, tblPartNumber.PartNumber, tblPartNumber.PartNumberDescription
ORDER BY tblPartNumber.PartNumber;
0
 
LVL 17

Accepted Solution

by:
Shanmuga Sundaram earned 2000 total points
ID: 22906220
sorry it should be as given below.

SELECT Format(tblScrapTicket.ScrapTicketDate, "YY.MM"),tblLineCode.LineCodeNumber, tblLineCode.LineCodeDescription, tblPartNumber.PartNumber, tblPartNumber.PartNumberDescription, sum(tblScrapTicket.QuantityScrapped)
FROM tblPartNumber INNER JOIN (tblScrapTicket INNER JOIN tblLineCode ON tblScrapTicket.LineCodeID = tblLineCode.LineCodeID) ON tblPartNumber.PartNumberID = tblScrapTicket.PartNumberID
WHERE (((tblScrapTicket.ScrapTicketDate) Between [Beginning Date] And [Ending Date]))
groub by Format(tblScrapTicket.ScrapTicketDate, "YY.MM"),tblLineCode.LineCodeNumber, tblLineCode.LineCodeDescription, tblPartNumber.PartNumber, tblPartNumber.PartNumberDescription
ORDER BY tblPartNumber.PartNumber;
0
 

Author Closing Comment

by:Frank Freese
ID: 31514380
thanks...it looks like it'll get me where i need. just had to change groub to group
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question