Problem with summing fields

[texasalynn]

I have been trying to figure this out and thought it would be a simple thing. I have a query that I need to sum all the fields preceding it. For example I have a calculated field 11/1/2004 that adds the previous fields from 12-31-2003 through 10-1-2004. The problem is when I try formula it tells me the "query is too complex". Does anyone know how I can do this?

Thanks . . . texasalynn

 

[The_Doc_Man]

My site policies do not allow me to download your zip file so I'm shooting from the hip here.

Something about your question bothers me, though I can't put my finger on it. Probably would do better if I could unzip your file. But I can't, so here goes a shot in the dark.

Whenever you want to do a sum that involves a computed field, you often have to do this in two steps. FIRST build a query that generates the computed value for each applicable day. SECOND build the sum query as the sum of the computed fields in each instantiation of the first query. You CAN do a query of a query. Works great! That way you have a very straightforward summation query as the second query and a simple computational query as the first query. The basic rule here is DIVIDE AND CONQUER.

 

[texasalynn]

I had tried that but also ran into an error. My query is going to be rather large because a have fields that start 12-2003 and go to 2-2007 (with a field for each month). What then happens is that I take a field "Amount" and put into the first 2 (12-2003 and 1-2004). Then in 2-2004 I add 12-2003 + 1-2004. Then in 3-2004 I add 12-2003 + 1-2004 + 2-2004, etc. So each month is done that way. So you see it will get to be a very large formula by the time it gets to 2-2007.

HTH
texasalynn

 

[texasalynn]

No one knows??? I must not be asking the question right.

texasalynn

 

[Rich]

Why not Normalise the db?

 

[texasalynn]

So now I'm a rocket scientist???? Thanks for that vote of confidence - but I don't even know what that means Rich.

texasalynn

 

[Jon K]

I take a field "Amount" and put into the first 2 (12-2003 and 1-2004). Then in 2-2004 I add 12-2003 + 1-2004. Then in 3-2004 I add 12-2003 + 1-2004 + 2-2004, etc. So each month is done that way.

It seems what you need is a query that returns the cumulative total for each month.

I have added a series of two queries in your database. You can run the second query to see if it is what you needed.

 

[texasalynn]

Thanks Jon but that is not quite what I need. I'll see if I can't write this out in length.

I will use the first line for my example. I have class5 with $15 and a term of 5. The first calculated field = 12/31/2003 which is compared with class5 data = 1/31/2003. If that date is less than the class5 date field then put 0, else put the amount $15. So in this example the answer is $15.

The second calculated field = 1/31/2004 does the same thing; compare calc date to class5 date - the answer is $15

The third calculate field = 2/29/2004; compare with class5 date, is true $15 but now in addition we need to sum the 1st and 2nd calc amounts (15+15=30) if equal to (term=5 * amount=$15 equals $75), true=-15(class5 amount), false=0
so we have $15 +0 = $15

The fourth calculated field = 3/31/2004; same as before but now add in 3rd calc amount
(15+15+15=45) if equal to (term=5 * amount=$15 equals $75), true=-15(class5 amount), false=0
so we have $15 + 0 = $15

So this process continues for each field. The query breaks down with the summarizing the totals of the calculated fields. The query you have doesn't do that - I'm sure what you are doing with SQL statements will do what I need but I don't know how to change it so that will calc the way I need it.

Hope I didn't loose you with this and you will be able to help me out.

Thanks . . . texasalynn

 

[Jon K]

I find that your query works if you simplify the IIF expressions by summing and displaying the amount of Dec 03 to Jul 04 as a field and applying the field in the expressions for the subsequent months (see query in database):-

[12/31/2003]+[1/31/2004]+[2/29/2004]+[3/31/2004]+[4/1/2004]+[5/1/2004]+[6/1/2004]+[7/1/2004] AS [SumToJuly],
IIf(#8/1/2004#<[Start Month],0,[Amount])+IIf([SumToJuly]=[Term]*[Amount],-[Amount],0) AS [8/1/2004],
IIf(#9/1/2004#<[Start Month],0,[Amount])+IIf([SumToJuly]+[8/1/2004]=[Term]*[Amount],-[Amount],0) AS [9/1/2004],
IIf(#10/1/2004#<[Start Month],0,[Amount])+IIf([SumToJuly]+[8/1/2004]+[9/1/2004]=[Term]*[Amount],-[Amount],0) AS [10/1/2004],


If you want to get rid of the [SumToJuly] field, you can change the query into a Make-table Query to create a temporary table and then delete the field from the table.

Hope it helps.

 

[texasalynn]

Thanks Jon - I will give that a try. But I'm thinking that same process will have to be done more than once since I need to go all the way to 2007? Don't you agree? I'll let you know how it works out.

Thanks . . . .texasalynn

 

[texasalynn]

Ok Jon I tried it and put into the next place your fix but still get the error "Query Too Complex". I left it this time in the query. Attaching the DB with error

Thanks . . . texasalynn

 

[Jon K]

I think the query has exceeded the maximum number of IIFs that Access can handle and you will need to use VBA code.

I have added an empty table tblTemp in the database. When you open the form and click on the command button, data up to December 2004 will be appended from Sheet2 to this table.

The code used is in the On Click event of the command button. You can add similar code for the months of 2005 to 2007.

 

[Jon K]

Since after #3/31/2004# the comparison dates all fall on the 1st day of each month, the VBA code in the attached database has been simplified by using a loop for the months of 4/1/2004 to 2/1/2007.