Current Children Only

chrisjnelson

New member
Local time
Today, 02:27
Joined
Jan 22, 2002
Messages
9
I need to create a query that works out only current children attending a nuresry.

The children need to be split into seperate age ranges. (0-1) (1-4) (4-5).

I have already established the dates that the children progress within the nursery but cannot seem to run a succesful query that brings back only current children for each group.
 
Since you do not provide info regarding your structure, I may well miss the point. But I believe that you should manage to get children for any group by using a "BETWEEEN" type of criteria for any of your groups.

Ex.
SELECT TblChildreInfo.FirstName, TblChildreInfo.Surname, TblChildreInfo.Age
FROM TblChildrenInfo
WHERE TblChildrenInfo.AgeBETWEEN 0 AND 1

(Just type "Between 0 and 1" in the Age field of your query)

Alex

[This message has been edited by Alexandre (edited 02-01-2002).]
 
Alex, Thanx for your reply.

My structure is as follows, i have three tables.
( [Children] / [Date] / [Customers] )
The [Customers] table is not really relevant for this query as it contains info on the parents.

I have the beginnings of a form called [attendance] that has a field called [date] from the [date] table. (i.e [date]![date])

What i need the query to do is look at the [date] field and pigeon hole lets say the childs first name into 1 of four categories. (0-1) (1-4) (4-5) (Not Present)
Based on which year the [date] field falls in.

E.g if you had a child that started on the 08/09/01, and the current date at the top of you register was 01/02/02 then the query would show the cilds name in the (0-1)category as it has not yet reached its first birthdate. But if you had a child born 08/09/98, its return would categorise it as (1-4)

This may be really confusing but its quite hard to write waht you can only visualise in your head.

Thank you so much in advance
 
Well, I see.
Fisrt of all, do not use reserved names like Date (which is the name of a function) for your objects. And do not use the same name for different objects. Use for example frmDate for your form, and txtDate for your textbox control.

Now, open an new query in design view, place the Children table into it, and place the BirthDate field into the grid.
In another column write
Age: DateDiff("m";[TableA]![BirthDate];Data())\12
This will calculate the number of years between today s date and the birth dates, and place it into a column called Age in your query.

In another column, write:
Category: iif([expr1]<1;"0-1";iif([expr1]<4;"1-4";iif([expr1]<5;"4-5";"Not present")))
This will return the name of the category the children belong to , and place it into a column called Category in your query.

Have a look to the DiffDate and Iif functions in Access help to understand how it works.

Alex

[This message has been edited by Alexandre (edited 02-01-2002).]
 

Users who are viewing this thread

Back
Top Bottom