DSum Question

[Perissos]

In reading through some of the older posts on the different uses of DSum I am wondering if it is something like this that I should be using. My problem is though, its not letting me use the Dsum with an if statement. This is an example of what I have

Code Desc Period Year amt
123 yellow 5 2010 65.00
123 yellow 6 2010 20.00
123 yellow 6 2009 10.00

What I am rying to do is have the following columns running the query for period 6 and year 2010

Code CY CYTD PY PYTD
123 20.00 85.00 65.00 65.00

CY = data for period 6 year 2010
CYTD = data < = period 6 2010
PY = data for period 6 2009
PYTD = data <= period 6 2009

Currently I have seperate queries to gather each piece and then combine them in order to create the report and I am trying to find a better way to do this.

 

[vbaInet]

You can use DSum() of use a subquery. My advise would be to use the DSum first, see how things work then go onto using a subquery.

Here's what you do:

1. In your query include only Code, Period, and Year Amt fields
2. Under the criteria for Period you put 6, and for Year 2010
3. Now perform a Sum on the Amt field and change the alias to CY. So that's CY sorted.
4. Show only the Code and (obviously) the CY field
5. For the others use a DSum (for starters), e.g. PY:
PY = data for period 6 2009

PY: DSum("[Amt]", "tableName", "Period=6 AND Year=2009")

Subquery (aircode):

PY: (Select Sum(Q.[Amt]) AS SumOfPY FROM tableName as Q WHERE Q.Period=6 AND Q.Year=2009)

Names like Year should not be used in Access. It's a reserved keyword.

 

[Perissos]

sorry.. that was just a typo, its like missing a semi colon after you've looked at something for too long. I will be more careful in the future.

 

[vbaInet]

Tired eyes! I know the feeling

Let us know you get on.

 

[vbaInet]

cikoo - reported for spam