CODEWITHSUNDEEP
sqlms-access
cthen
cthen

Reputation: 3

Access Query Total Sum between Dates

I'm looking a way to calculate the total quantity purchased between a specific dates and returns just one entry based on the item name. Here's the sample of my table and the result that I need: Sample Table

I'm not sure if Access have the capability to do this, I tried to use different formula and even grouping it together, but it didn't come out as what I need. I did quite a bit of research as well, but none of them have the result I want. I really need to know the answer, it driving me nuts. Anyway, thank you beforehand for anyone that can help me.

Upvotes: 0

Views: 9078

Answers (1)

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

  • Use query designer
  • select your table
  • select the three fields you want (ItemName, Quantity, PurchaseDate)
  • create selection criteria
  • select 'Totals' option in Ribbon
  • Make 'Date'= 'WHERE'
  • Make 'Quantity' = 'SUM'
  • Let 'ItemName' default to 'GROUP BY'
  • Run the query

Here's a sample:

SELECT Sum(Table1.Quantity'SUM) AS SumOfQuantity'SUM, Table1.ItemName
FROM Table1
WHERE (((Table1.PurchaseDate) Between #1/1/2000# And #12/31/2015#))
GROUP BY Table1.ItemName;

Upvotes: 2

Related Questions