Calculate quarterly figures from cumulative data

[scubadiver007]

Hello,


I am able to calculate the differences for each quarter in a query if all the data is present using a DLookup in an IF statement. I think this is a bit more difficult than a query problem. The problem is that there is no guarantee the data will exist for each quarter.

Required fields

Quartercode
Practicecode
FieldID
Activity

The practice code and FieldID stays the same and I need to calculate the difference in the activity between the current quarter and the most recent quarter.
There are 9 different variations (see attachment).

If it is possible to create a module or VBA to do this, I would need some help because I'm no expert.

Thanks

 

[vbaInet]

So like you guessed, you need some code to do this. The steps are:

1. Get all 9 possible parameters using ParamArray when declaring the array variable in the function's argument
2. Loop through the array from step 1 and only subtract the non Nulls.

Somehing like (aircode):

Public Function GetQuarterDiffs(ParamArray Qs() As Variant)
    Dim varItem As Variant
    Dim intDiffs As Long

    For Each varItem In Qs
        ... perform the check here and subtract from intDiffs for non Nulls ...
    Next
End Function

 

[scubadiver007]

Thanks for replying,

There are 15 different combinations depending on whether I get 1, 2, 3 or 4 quarters worth of data, which may or may not complicate things.

Parameter arrays are new to me so I need some more help on how I would translate this information into the code.

Thanks

 

[scubadiver007]

Just to give a bit of extra info, the data is uploaded each quarter and there is a field in the table that records the following (for example)

Q1 2010_11
Q2 2010_11
Q3 2010_11
Q4 2010_11

So for Q3 data I need to determine, on a case-by-case basis, whether I need to use Q1 or Q2 data to calculate the difference, or if either of them exist at all in which case I just use Q3.

 

[vbaInet]

Thanks for replying,

There are 15 different combinations depending on whether I get 1, 2, 3 or 4 quarters worth of data, which may or may not complicate things.

That's fine. You will just give the 15 fields and start from the last item in the array.

Parameter arrays are new to me so I need some more help on how I would translate this information into the code.

Have a search on the forum or on Google and see what you can come up with. Some real data will help us determine if the suggested code would actually suffice, because the code was simply based on your spreadsheet.

 

[scubadiver007]

Hello,

Thanks for reply.

Some actual data would make a lot of sense, so I have attached. The data is from one table but I have separated the rows just to make it easier to understand.

The calculation is dependent on the Practice, fieldname and quartercode. The first record is determined by what year I am selecting and the 2nd character in the string.

I was using an IF statement using Q1 and then a Dlookup subtracting 1, but this obviously wouldn’t always hold.

QFigs: IIf(Left([Quartercode],2)="Q1",[numberofpatients],[numberofpatients]-DLookUp("numberofpatients","dbo_ES_factbase","fieldname=""" & [fieldname] & """ And gppracticecode=""" & [data check].gppracticecode & """ And Mid(quartercode, 2, 1)=""" & Mid([Quartercode],2,1)-1 & """"))

After some thought, what I really need is for the "first" record to be the lowest number (whether it would 1, 2, 3 or 4) and the desired result would just be the activity and then to calculate the differences for all subsequent quarters.

I think including an extra field which isolates the second character might help.

Thanks for any help

 

[vbaInet]

The actual data isn't quite the same as what you provided in your initial post.

Will you be doing this in a report?

 

[scubadiver007]

The original attachment was meant to make it easier to understand, but maybe not.

I need to link the data to another table containing unit costs so I can then calculate the total payment.

 

[vbaInet]

The original attachment was meant to make it easier to understand, but maybe not.

It didn't You represented it in a column-wise fashion which was why I recommended using a function. If I knew they were actually records I would have advised differently.

I need to link the data to another table containing unit costs so I can then calculate the total payment.

Ok, but will you be displaying this in a report or in the query? It will be easy to do in a report.

 

[scubadiver007]

Unfortunately, I would rather keep it in a query.

 

[vbaInet]

How are the QuarterCode values generated? I know we can extract the 1 from Q1 but I would like to know how Q1 2010_11 (for example) is generated.

 

[scubadiver007]

I have no control over the fields of the table or how they are generated. There are two other numerical fields which should be more helpful. [Quarternum] is the number that represents the quarter and [yearnum] is a reference for the year (so 2010_11 is given 1, 2011_12 is given 2 etc).

 

[vbaInet]

Not tested but try:

Difference: [[COLOR=Red]Activity[/COLOR]] - (SELECT T.[[COLOR=Red]Activity[/COLOR]] FROM [COLOR=Red]TableName[/COLOR] AS T WHERE Mid(T.[[COLOR=Red]quartercode[/COLOR]], 2, Instr(1, T.[[COLOR=Red]quartercode[/COLOR]], " ") - 1) <   Mid([COLOR=Red]TableName[/COLOR].[[COLOR=Red]quartercode[/COLOR]], 2, Instr(1, [COLOR=Red]TableName[/COLOR].[[COLOR=Red]quartercode[/COLOR]], " ") - 1) ORDER BY Val(Mid([COLOR=Red]TableName[/COLOR].[[COLOR=Red]quartercode[/COLOR]], 2, Instr(1, [COLOR=Red]TableName[/COLOR].[[COLOR=Red]quartercode[/COLOR]], " ") - 1)) DESC)

Amend the red bits only and write it exactly as given.

 

[scubadiver007]

I have tried it and it says only one number can be returned but I've got the gist of what you are thinking. I hadn't thought about using a subquery (but then I am not really an expert on those either!)

Thanks

 

[vbaInet]

Oops... add TOP 1 to it:

SELECT TOP 1 .. etc

 

[scubadiver007]

This is what I have

Difference: [numberofpatients] - (SELECT TOP 1 T.[numberofpatients] FROM dbo_es_factbase AS T WHERE Mid(T.[quartercode], 2, Instr(1, T.[quartercode], " ") - 1) < Mid(dbo_es_factbase.[quartercode], 2, Instr(1, dbo_es_factbase.[quartercode], " ") - 1) ORDER BY Val(Mid(dbo_es_factbase.[quartercode], 2, Instr(1, dbo_es_factbase.[quartercode], " ") - 1)) DESC)

Still get the same error

 

[vbaInet]

That's almost impossible. Did you move away from the field after making the changes and before opening it in Datasheet view?

Let me see your db.

 

[scubadiver007]

Database attached.

Instead of using [quartercode], [quarternum] would be easier to deal with and including the year number would be a great help as well.

Thanks.

 

[vbaInet]

And where's the query you were working on? How can I prove that what you did is still throwing that error message?

 

[scubadiver007]

Re-attached with query.