Age change during period 1

[traycee]

I have a query that contains a calculated field that determines the age of a client. Let's say Johnny's birthday is July 3, 2004 and I am running a report from July 1, 2009 through July 31, 2009. Johnny shows up as 5 years old. But....for the first two days of July he was 4 years old! I need to see that, too, because amount billed is based on age. So...for two days I would need to bill for a 4 year old and the rest of the month I would bill as a 5 year old. Is this possible?

Thanks for your thoughts.

 

[dhookom]

How do you expect us to have any knowledge of your table structures or queries? Apparently there is a billing rate table with an age field or fields.

If you want help, please provide enough significant information so that someone can help. It might be of value if you provided some sample records.

Duane
Hook'D on Access
MS Access MVP

 

[traycee]

Youth table connects via primary key ID to foreign key ID in Moves table. Each month each client gets a clothing allowance and an allowance. The amount they receive is determined by their age. The formulas in the query are

Clothing: IIf([SSU]=0,IIf([respite]=0,IIf([age]>=12,74.16,IIf([Age]>=9 And [Age]<=11,64.89,IIf([Age]>=5 And [Age]<=8,55.62,IIf([Age]>=1 And [Age]<=4,42.23,37.08))))))

AND

Allowance: IIf([SSU]=0,IIf([Respite]>=0,IIf([age]>=12,43.26,IIf([Age]>=9 And [Age]<=11,23.69,IIf([Age]>=5 And [Age]<=8,13.39,IIf([Age]>=1 And [Age]<=4,12.36,11.33))))))

The report is pulled each month based on this query and the amounts are determined. The age is based on the DOB field.

Age: DateDiff("yyyy",[DOB],[Forms]![Input Dates]![EndDate])+Int(Format([Forms]![Input Dates]![EndDate],"mmdd")<Format([DOB],"mmdd"))

However, when a client changes age during the reporting month, I really need them to show up in the query twice. Using the example above, I would need to see Johnny as a 4 year old for 2 days and then Johnny as a 5 year old for the balance of the month so that he is paid the correct amount.

Example:

YouthName DOB Age InCare ClothingAmt AllowanceAmt
John Smith 7/3/2004 4 31 $42.23 $12.36
John Smith 7/3/2004 5 31 $55.62 $13.39
Naomi Jones 4/13/2004 5 31 $55.62 $13.39
Sam Iam 1/1/1999 10 31 $64.89 $23.69

 

[traycee]

Oops....screwed up on the table example!

YouthName DOB Age InCare ClothingAmt AllowanceAmt
John Smith 7/3/2004 4 2 $2.72 .80
John Smith 7/3/2004 5 29 $52.03 12.52 Naomi Jones 4/13/2004 5 31 $55.62 $13.39
Sam Iam 1/1/1999 10 31 $64.89 $23.69

 

[dhookom]

I wouldn't go any further with hard-coded values in your expressions. I can't believe that clothing and allowance values wouldn't change over time. You shouldn't be maintaining values in expressions in queries.

The very least I would do is create a couple small functions that would accept the [DOB] & ReportRunDate and return the Clothing and Allowance amounts. Save these functions in a module named "modBusinessCalcs".

Do you really want to return two records where a person's birthday falls in the month? Or, do you want to return a weighted average of the Clothing and Allowance amounts?

Duane
Hook'D on Access
MS Access MVP

 

[traycee]

Thanks.

I don't really need to see two records when a person's birthday falls in the month. Just the correct sum would be fine.

I actually thought about putting the clothing values and allowance values in tables and then referring to the tables instead of hard coding them.

 

[dhookom]

You don't see two records here for John Smith?
[tt][blue]
YouthName DOB Age InCare ClothingAmt AllowanceAmt
John Smith 7/3/2004 4 2 $2.72 $0.80
John Smith 7/3/2004 5 29 $52.03 $12.52
Naomi Jones 4/13/2004 5 31 $55.62 $13.39
Sam Iam 1/1/1999 10 31 $64.89 $23.69[/blue][/tt]
I would create a couple functions so your calculations would be encapsulated in one place. When you want to create tables for managing your numbers, you can modify the function to use the tables rather than hard coded numbers in the function.

Duane
Hook'D on Access
MS Access MVP

 

[Geo21]

If you want the name to be shown twice in a table/query no matter what, I would do foll:

1) Create a select query with Name, DOB and BegDateAge (how old on 1/7/09) & EndDateAge (how old on 31/7/09).

2) Create a make table query (from the above query) with Name, DOB and BegDateAge.

3) Create an append query (from the above query also) to append Name to Name, DOB to DOB and EndDateAge to BegDateAge IF EndDateAge<>BegDateAge. If the last criteria is false, then the name will not be appended.

4) Then I would create a select quer out of the new table which will calculate the days for each person in order to come up with the amounts using IIF(month([DOB]) = Month([BegDate]) etc etc...

 

[traycee]

Thanks, Geo21. Worked like a charm. Sorry it took me so long to get back to you.

 

[MichaelRed]

The 'rate changes' will occur within a period. You should set up the table to record the start date of the change. Also the routine needs to interpolate (weighted average) the rate for the duration that it is / was active for the period.

May be guilding the lilly, but necessary to be truly correct.

Michael Red


MichaelRed