Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Wyoming, USA
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totals by date query (Access XP)

    Hello,

    I am trying to get a query to give me totals between two dates. The query that I have based my problem query on works fine for the totals that it gives me but, it does not include a field for dates.

    I will try to explain what I am wanting to do and I will include the sql in my post.

    The original query gives me these totals based on all data in the database, Totals sales, total number of units sold for each inventory item, the percentage of each inventory item compared to all inventory items sold in units and in $ generated.

    I want the current query to give me the same things but based on data between two dates. This will allow me to compare a newly introduced item to the other inventory items. The original query though helpful, doesn't present a good picture of newly introduced items. I hope this makes it understandable as to what I am looking to do. The SQL is below, thanks for any help.

    What happens is I get each individual record for each day. I would like a total of all items between the two dates.

    SELECT DateValue([DateTime]) AS [Sale Date], Invoice_Itemized.ItemNum, Inventory.ItemName, Sum(Invoice_Itemized.Quantity) AS Quantity, Inventory.Cost, Inventory.Price, Sum(Inventory.Cost) AS SumOfCost, Sum(Inventory.Price) AS SumOfPrice, [Price]/[SumOfTotal_Price] AS [Margin $], [Quantity]/[Total units] AS [Margin of Units]
    FROM [Total Sales], [# of units sold], (Invoice_Totals INNER JOIN Inventory ON Invoice_Totals.Store_ID = Inventory.Store_ID) INNER JOIN Invoice_Itemized ON (Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number) AND (Inventory.ItemNum = Invoice_Itemized.ItemNum)
    WHERE (((Inventory.Price)<>0))
    GROUP BY DateValue([DateTime]), Invoice_Itemized.ItemNum, Inventory.ItemName, Inventory.Cost, Inventory.Price, [Total Sales].SumOfTotal_Price, [# of units sold].[Total units]
    HAVING (((DateValue([DateTime])) Between [Start Date] And [End Date]))
    ORDER BY [Price]/[SumOfTotal_Price] DESC;


    Bret

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Totals by date query (Access XP)

    Open your query in design view. Change the Totals option for Sale Date: DateValue([DateTime]) from Group By to Where. This will automatically clear the Show check box. The result will be that the Sale Date field will not be displayed any more, but it will be used to filter the data.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Wyoming, USA
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals by date query (Access XP)

    Hans,

    Thank you so much. This worked perfectly. I have to say that without this group, Access would be as good as a lump of coal on my hard drive.

    You folks are wonderful.

    Bret

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •