Full Version: Date Expressions
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
Grassy7
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!
truittb
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
Orderreserved 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
Grassy7
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.
truittb
CODE
SELECT Jobsites.JobsiteID,
    Jobsites.JobsiteName,
    tblOrderDetails.Category,
    tblOrderDetails.Description,
    Sum(tblOrderDetails.Quantity) AS Quantity,
    tblItems.ItemType,
    Format([Date],"yyyymm") 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
WHERE Jobsites.JobsiteID=[Enter JobsiteID]
    AND tblItems.ItemType="R"
    AND Format([Date],"yyyymm")<[Enter YYYYMM]
GROUP BY Jobsites.JobsiteID,
    Jobsites.JobsiteName,
    tblOrderDetails.Category,
    tblOrderDetails.Description,
    tblItems.ItemType,
    Format([Date],"yyyymm");
Grassy7
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?
truittb
CODE
SELECT Jobsites.JobsiteID,
    Jobsites.JobsiteName,
    tblOrderDetails.Category,
    tblOrderDetails.Description,
    Sum(tblOrderDetails.Quantity) AS Quantity,
    tblItems.ItemType
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
WHERE Jobsites.JobsiteID=[Enter JobsiteID]
    AND tblItems.ItemType="R"
    AND [Date] <= [Enter Date]
GROUP BY Jobsites.JobsiteID,
    Jobsites.JobsiteName,
    tblOrderDetails.Category,
    tblOrderDetails.Description,
    tblItems.ItemType;
Grassy7
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]
truittb
ItemTotals: Sum(tblOrderDetails.Quantity)*[RentalRate]
Grassy7
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!!!
Grassy7
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?
Grassy7
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...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.