Solved

Access Query

Posted on 2016-08-21
7
35 Views
Last Modified: 2016-08-21
I'm struggling with a query in access. I'm using the DSUM function to calculate a cumulative total for COGS that starts over each year. The issue is that I need the cumulative total to not start over each year. I'm using the design view in access to attempt the query, but have copied the sql below (as this may be most helpful for the experts).  So I need a cumulative total by Acctnbr and by Class that doesn't restart annually. Right now I can get it to a cumulative total by Acctnbr and by Class that restarts annually. I've exhausted my capabilities and am hoping for some good suggestions. Thanks.


SELECT Perpost, Year, Month, LocationCode, LocationDescription, LocationName, Class, State, LocationType, BenchmarkFacilityLocationCode, City, ZipCode, RentStartDate, RentEndDate, AcctNbr, AcctName, COGSFlag, MonthEndDate, Amount, DSum("Amount","tblUploadOccupancy(All)Flat","DatePart('m',[MonthEndDate])<=" & [Month] & " And  DatePart('yyyy', [MonthEndDate])=" & [Year] & " And [AcctNbr]=" & [AcctNbr] & " And [Class]='" & [Class] & "' ") AS CumAmount INTO tblCumulativeCOGS
FROM [tblUploadOccupancy(All)Flat]
GROUP BY Perpost, Year, Month, LocationCode, LocationDescription, LocationName, Class, State, LocationType, BenchmarkFacilityLocationCode, City, ZipCode, RentStartDate, RentEndDate, AcctNbr, AcctName, COGSFlag, MonthEndDate, Amount
HAVING (((COGSFlag)="COGS"))
ORDER BY AcctNbr;
0
Comment
Question by:Bcn78
  • 3
  • 3
7 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
@bcn78,

What are you using this for?  is it a report?  Using domain functions is queries is generally considered a bad idea, unless the remainder of the query needs to be updatable.  In generally, I would write the query lik:

SELECT T1.Perpost, T1.[Year], T1.[Month], T1.LocationCode, T1.LocationDescription, T1.LocationName, T1.Class, T1.State, T1.LocationType, T1.BenchmarkFacilityLocationCode, T1.City, T1.ZipCode, T1.RentStartDate, T1.RentEndDate, T1.AcctNbr, T1.AcctName, T1.COGSFlag, T1.MonthEndDate, T1.Amount, SUM (T2.Amount) as CumAmount
FROM [tblUploadOccupancy(All)Flat] as T1
LEFT JOIN [tblUploadOccupancy(All)Flat] as T2
ON T1.[AcctNbr] = T2.[AcctNbr]
AND T1.[Class] = T2.[Class]
AND DateSerial(T1.[Year], T1.[Month] +1, 0) >= DateSerial(T2.[Year], T2.[Month] + 1, 0)
WHERE (T1.COGSFlag="COGS")
GROUP BY T1.Perpost, T1.[Year], T1.[Month], T1.LocationCode, T1.LocationDescription, T1.LocationName, T1.Class, T1.State, T1.LocationType, T1.BenchmarkFacilityLocationCode, T1.City, T1.ZipCode, T1.RentStartDate, T1.RentEndDate, T1.AcctNbr, T1.AcctName, T1.COGSFlag, T1.MonthEndDate, T1.Amount
ORDER BY T1.AcctNbr, T1.Year, T1.Month

This query uses a computed values and a non-equi-join in the join clause so it will not be editable in the query design grid.  The reason I used the computed columns on the Year and Month is that you cannot really query on those two items individually, they have to be viewed as a date, so I chose the last day of each of those months, as depicted by DateSerial([Year], [Month] + 1, 0).  Also, the non-equi join is used so that all of the records in T2 which match on account#, and class, and which have a date <= the Year/month combination in T1 are included in the query; this is what will get you the running sum.

HTH
Dale
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Firstly, replace this:
HAVING (((COGSFlag)="COGS"))

Open in new window

with this
WHERE (((COGSFlag)="COGS"))

Open in new window


It might help if you posted some representative sample of the data.  I'm not sure what you mean when you write:
doesn't restart annually
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Also note that I changed the "HAVING" clause to a "WHERE" clause.  I did this to limit the number of records that get considered in the main query.  If you have the criteria in a WHERE clause, it excludes records which don't meet the criteria before the majority of the processing.  If you put it in a HAVING clause, the query will process the SUM for all of the [COGSFlag] values and then exclude those that don't meet your final criteria from the final results.

To do this in design view, you must add a separate column in the query in the Totals row of the grid, select "WHERE" instead of "Group By"

BTW,  if you have more than one record for each AcctNbr, Class, Year, Month combination in your table, then you may need to sum the [Amount] column as well, to get the sum of [Amount] for each month.

One last thing, [Year] and [Month] are reserved word and really should not be used as field names.  You should generally give your fields meaningful names, so these might be better named like Transaction_Year and Transaction_Month, or something like that.  You should also be advised that if you persist in using these field names, you should always wrap them in brackets [ ] to prevent confusion with the functions associated with those names.

Dale
1
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Bcn78
Comment Utility
Thanks Dale. I appreciate your help. Understanding your solution is little beyond my current capability, so it's very helpful for me to study and understand your response to help advance my skill set. I'm going to work with this for a while and may circle back with a question or two if you don't mind. Thanks again.

brett
0
 

Author Comment

by:Bcn78
Comment Utility
Separately, I am using this to make a table that I can use in a subsequent query to forecast WIP and finished goods inventory
0
 

Author Closing Comment

by:Bcn78
Comment Utility
This is great. I really appreciate it. The below was very helpful as well, it made the query run 10x faster. Thanks again Dale.

-Brett

Also note that I changed the "HAVING" clause to a "WHERE" clause.  I did this to limit the number of records that get considered in the main query.  If you have the criteria in a WHERE clause, it excludes records which don't meet the criteria before the majority of the processing.  If you put it in a HAVING clause, the query will process the SUM for all of the [COGSFlag] values and then exclude those that don't meet your final criteria from the final results.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
glad to help, Brett.

non-equi JOINs can be confusing, but can generally be re-written as a WHERE clause as below:

SELECT T1.Perpost, T1.[Year], T1.[Month], T1.LocationCode, T1.LocationDescription, T1.LocationName, T1.Class, T1.State, T1.LocationType, T1.BenchmarkFacilityLocationCode, T1.City, T1.ZipCode, T1.RentStartDate, T1.RentEndDate, T1.AcctNbr, T1.AcctName, T1.COGSFlag, T1.MonthEndDate, T1.Amount, SUM (T2.Amount) as CumAmount
FROM [tblUploadOccupancy(All)Flat] as T1
INNER JOIN [tblUploadOccupancy(All)Flat] as T2
ON T1.[AcctNbr] = T2.[AcctNbr]
AND T1.[Class] = T2.[Class]
WHERE (T1.COGSFlag="COGS")
AND (DateSerial(T1.[Year], T1.[Month] +1, 0) >= DateSerial(T2.[Year], T2.[Month] + 1, 0))
GROUP BY T1.Perpost, T1.[Year], T1.[Month], T1.LocationCode, T1.LocationDescription, T1.LocationName, T1.Class, T1.State, T1.LocationType, T1.BenchmarkFacilityLocationCode, T1.City, T1.ZipCode, T1.RentStartDate, T1.RentEndDate, T1.AcctNbr, T1.AcctName, T1.COGSFlag, T1.MonthEndDate, T1.Amount
ORDER BY T1.AcctNbr, T1.Year, T1.Month

In this version, which you could edit in the query design grid, I've simply moved the non-equi JOIN and moved that clause into the WHERE clause.  Consider a simple example of a table (tblItemSales) with sales for a single item and fields (ItemID, SalesDate, Quantity) and just a couple of records:

ItemID      SalesDate   Quantity
1                 7/1/16           100
1                 7/2/16           150
1                 7/3/16           125

if you create a query:

SELECT T1.ItemID, T1.SalesDate, T1.Quantity, T2.SalesDate, T2.Quantity
FROM tblItemSales as T1
INNER JOIN tblItemSales as T2 ON T1.ItemID = T2.ItemID

you would get 9 records (three records from T2 for each record in T1).

If you added a WHERE clause to only include the record from T2 which are <= T1.Sales date then you would initially get 9 records in the recordset, but the WHERE clause would filter out three, leaving 6 records (1 from T2 for T1.SalesDate = 7/1/16, 2 records from T2 for T1.SalesDate = 7/2/16, and 3 records from T2 for T1.SalesDate = 7/3/16).

Theoretically, if you move that WHERE clause into a non-equi JOIN, you would only get the 6 records initially.  So, the non-equi JOIN would get reduce the number of records to be processed.

Hope this helps.

Dale
1

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now