Between 2 Dates in Data Field

[bhipwell via AccessMonster.com]

Hello,

I have a table that has the following fields:

ItemID
MinimumAge
Cost

The data would look like this:

1234 25 $100
1235 30 $200
1236 35 $300
1237 40 $400

I have another table with the following:

PersonID
LastName
Age

The data would look like this:

1234 Jones 26
1235 Smith 42

What I need is a query that returns the Cost corresponding to the persons age.
Thanks in advance!

Brandon

 

[John W. Vinson]

Hello,

I have a table that has the following fields:

ItemID
MinimumAge
Cost

The data would look like this:

1234 25 $100
1235 30 $200
1236 35 $300
1237 40 $400

I have another table with the following:

PersonID
LastName
Age

The data would look like this:

1234 Jones 26
1235 Smith 42

What I need is a query that returns the Cost corresponding to the persons age.
Thanks in advance!

Brandon

First off... you should not be storing Age in a table field, unless you can be
absolutely certain that none of your customers will get any older during the
useful life of the database. The one thing you can be certain about an Age
field is that it will be wrong for every single record in your table within a
year from the date it's entered! Store the birthdate instead, and calculate
the age with an expression like

DateDiff("yyyy", [DOB], Date()) - IIF(Format(Date(), "mmdd") > Format([DOB],
"mmdd"), 1, 0)

That said - use a Subquery: in a vacant Field cell type

Cost: (SELECT Min(Cost) FROM Costtable WHERE Costtable.Age >= yourtable.Age)
AS CurrentCost

This assumes that Cost increases with age - a two-level subquery may be needed
if the cost can stay static or go down with increasing age.

 

[bhipwell via AccessMonster.com]

John,

Thanks for the info. I do have the DOB birth instead of age, thanks for the
tip though. Regarding your solution, the cost can be static or increase (it
will never decrease). So for example, I could have the following data set:

ID Age Cost
1234 25 $100
1235 30 $100
1236 35 $200
1237 40 $200
1238 45 $250
1239 50 $300
1240 55 $350

You mentioned a two lever query. Is that necessary with a data set like the
one above?

 

[John W. Vinson]

John,

Thanks for the info. I do have the DOB birth instead of age, thanks for the
tip though. Regarding your solution, the cost can be static or increase (it
will never decrease). So for example, I could have the following data set:

ID Age Cost
1234 25 $100
1235 30 $100
1236 35 $200
1237 40 $200
1238 45 $250
1239 50 $300
1240 55 $350

You mentioned a two lever query. Is that necessary with a data set like the
one above?

Probably not - someone who's 38 or 42 will get the $200 price.

For future reference, or for a more rigorous general solution, you could use
something like

SELECT <fields>, (SELECT A.Cost FROM Costs AS A
WHERE Costs.Age = (SELECT Max(B.Age) FROM Costs AS B
WHERE B.Age <= tablename.Age));

 

[Dale Fye]

Using this example, how much would someone who is 24 pay?

If these are the only fields in the Cost table, then I cannot possibly see a
need for the records containin Age = 30 or 40. They are redundant.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[bhipwell via AccessMonster.com]

Ah, very good point. Having age 30 and 40 both equal the same amount is
going back to an old database structure that wasn't normalized. Great catch.
Regarding age 24, we would have a minimum age amount for 0, or for those
between the ages of 0-24.

BH