Simple query questions

[robin123]

I hope that this will be simple for you -

I have a table which includes the fields Date of Birth and Age Group.

Age Group is based on the persons age so I created a query (qryAge) to calculate age using the statement: DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

This query works fine and when i run it it displays each persons age.

Now to calculate the Age group I have created an update query which when run will update the Age Group field, however it does not work. The if statement I have used is -

IIf([qryAge]![Age]<"18","Under 18",(IIf([qryAge]![Age]>"17" And [qryAge!Age]<"35","Under 35",(IIf([qryAge]![Age]>"34" And [qryAge]![Age]<"45","Under 45",(IIf([qryAge]![Age]>"44" And [qryAge!Age]<"55","Under 55",(IIf([qryAge]![Age]>"44","55+")))))))))

If someone could tell me what is wrong it would be very greatly appreciated, as no matter what I try i cannot get it to work!

 

[FoFa]

Well, just for starters, that last one is displaying over 55 but you are checking for > 44
Is it not updating, are you getting an error?

 

[robin123]

Well, just for starters, that last one is displaying over 55 but you are checking for > 44
Is it not updating, are you getting an error?

Okay I have fixed the first mistake!!

Whenever I run it it says it is going to update 16 rows, yet I only have four records in the database (just to test if it is working). Then when I go into the table all the age groups in the four records have been set to 'Under 18'.

 

[FoFa]

Sounds like you are not updating the records correctly. 4 X 4 = 16 so you are getting one update per record, per record, and only the last one is taking on all of them.
Is that big ole IIF in your UPDATE TO cell? Are there any joins? How many rows is your first query returning?

 

[robin123]

Yeh, the IIF statement is in the update to cell.

The [qryAge!Age] query returns four results as there are currently only four records in the table. There are no joins in the statement.

Is there any other easier way of doing this?

 

[FoFa]

I would go to SQL display and cut/paste you SQL here

 

[robin123]

Okay. The SQL is -

UPDATE Athletes, qryAge SET Athletes.[Age Group] = IIf(qryAge!Age>"18","Under 18",(IIf(qryAge!Age>"17" And [qryAge!Age]<"35","Under 35",(IIf(qryAge!Age>"34" And qryAge!Age<"45","Under 45",(IIf(qryAge!Age>"44" And [qryAge!Age]<"55","Under 55",(IIf(qryAge!Age<"44","Over 55")))))))));

Thanks for all the help. It is greatly appreciated.

 

[robin123]

Oh. If it helps the SQL for the query to calculate age from date of birth is -

SELECT DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")) AS Age
FROM Athletes;

 

[FoFa]

Nice, but what ties this AGE back to a single record in your Athletes

 

[robin123]

Nice, but what ties this AGE back to a single record in your Athletes

I think that could be the problem, but how do I relate it back to a record in the Athletes table?

 

[raskew]

Hi -

Common wisdom says that neither Age nor Age Group should be stored since they are both moving targets. Rather they would be represented as calculated fields in a query.

Here's a working example, just tested, that returns both Age and AgeGroup. It employs the Switch() function, which is a lot less messy than multiple nested Iif() statements.

Try copying it to a new query, changing table and field names as necessary:

SELECT
    tblStaff1.Name
  , tblStaff1.DOB
  , DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB]))) AS Age
  , Switch([Age]<18,"Under 18",[Age]<35,"Under 35",[Age]<45,"Under 45",[Age]<55,"Under 55",True,"55+") AS AgeGroup
FROM
   tblStaff1;

HTH - Bob

 

[robin123]

Bob - Thank you so much that does exactly as I wanted. It is very much appreciated.