I'm using a SQL Statement (query builder) in a report. Now I'm trying to get the query right before I can really use the report to print what I need. I divided up a shipping date into two columns, M: Month([Date]), and Y: Year([Date]). And I use a Criteria in both columns to ask for a month number and year. I'm getting part of the results I want. However, I can't seem to figure out how to combine all items shipped into one Summary. It will display the results for an item per each month going back to the first month items were shipped to a customer. I do have a Group By "Sum" on the Quantities. I really need to just show the total for all items at one time, not by each month. Can anyone help me? If I can figure this out, I will be set!

Date is a reserved word in Access. Continued use will cause problems in the future.

Select Format(YourDate,"yyyymm") as ReportMonth, Customer, Sum(Quantities) as Quantities
From YourTable
Group By Format(YourDate,"yyyymm") , Customer
Order By Format(YourDate,"yyyymm") , Customer

Here's my written query SQL Statement:

SELECT Jobsites.JobsiteID, Jobsites.JobsiteName, tblOrderDetails.Category, tblOrderDetails.Description, Sum(tblOrderDetails.Quantity) AS SumOfQuantity, tblItems.ItemType, Month([Date]) AS Expr1
FROM (Jobsites INNER JOIN tblOrders ON Jobsites.JobsiteID = tblOrders.JobsiteID) INNER JOIN (tblOrderDetails INNER JOIN tblItems ON (tblOrderDetails.Category = tblItems.Category) AND (tblOrderDetails.Description = tblItems.Description)) ON tblOrders.TicketID = tblOrderDetails.TicketID
GROUP BY Jobsites.JobsiteID, Jobsites.JobsiteName, tblOrderDetails.Category, tblOrderDetails.Description, tblItems.ItemType, Month([Date])
HAVING (((Jobsites.JobsiteID)=[Enter JobsiteID]) AND ((tblItems.ItemType)="R") AND ((Month([Date]))<[Enter Month Number]));


This gives a better idea as to what I have going on.

I appreciate that help, however, I'm still back to square one. It's returning results that divides up the sum of the quantities by month. I want the total over all the months up to the month and year I enter into the parameter. Any suggestions?

Hey, that's working great! Now, I need to multiply the Quantity times a RentalRate that I added to the SQL Statement. It's not working right when I just enter ItemTotals: [Quantity]*[RentalRate]

ItemTotals: Sum(tblOrderDetails.Quantity)*[RentalRate]

Wait, I did something before you responded and it works now...Man, I really do appreciate your help. It's the small stuff that throwing me off. I think my employer will be sending me to some Access Training sometime soon so I won't be on UtterAccess ALL the time. I really have learned a lot from this website though. Much Thanks!!!

Wait! Don't leave yet...another question. Need to show the date that I enter on the report in a textbox somewhere. How can I get that date that I enter in the parameter box on the report?

Anybody know how to get an unbound text box to dislpay a date that is entered into a parameter. In my SQL statement behind my report I have [Date] <= [Enter Date] under a "Where" Group By. I would like the Date I enter in the parameter box to display on my report. Need a little help here...