CODEWITHSUNDEEP
sqlms-access
Scott B.
Scott B.

Reputation: 33

Access Query - Summing Month to Date

I currently have a crosstab query set up to return amounts by month in 2017. This displays month totals for each location individually. I want to sum year to date (month 1-7) and have it only return one value for the location. I am having trouble doing this for some reason.

TRANSFORM Sum(Location.Amt) AS SumOfAmt
SELECT 
    Location.Div
  , Location.Store
  , Location.Year
  , Location.Month
FROM 
  [Location Name] 
  INNER JOIN Location 
    ON [Location Name].Line = Location.Line
WHERE 
  (
    Location.Store     <> 100 
    AND Location.Year   = 2017 
    AND Location.Month  < 8 
    AND Location.Line   < 11
GROUP BY 
  Location.Div
  , Location.Store
  , Location.Year
  , Location.Month
ORDER BY 
  Location.Div
  , Location.Store
  , [Location Name].LineDesc
PIVOT [Location Name].LineDesc
;

Upvotes: 1

Views: 73

Answers (1)

Jacobm001
Jacobm001

Reputation: 4539

In your select and group by clauses you've listed Year and Month which means it will aggregate for each value in those columns. If you don't want that behavior, you should remove those columns from the query.

TRANSFORM Sum(Location.Amt) AS SumOfAmt
SELECT 
    Location.Div
  , Location.Store
FROM 
  [Location Name] 
  INNER JOIN Location 
    ON [Location Name].Line = Location.Line
WHERE 
  (
    Location.Store     <> 100 
    AND Location.Year   = 2017 
    AND Location.Month  < 8 
    AND Location.Line   < 11
GROUP BY 
  Location.Div
  , Location.Store
ORDER BY 
  Location.Div
  , Location.Store
  , [Location Name].LineDesc
PIVOT [Location Name].LineDesc
;

Upvotes: 1

Related Questions