Multiple Column Query - results based on age

[Blastedkane]

Hi Guys,

I have been searching and failing to find the answer to a question :banghead::banghead::banghead:

I am creating a database for a music school. the families are split in the following way


Hub table - this is the family unit and can contain multiple members, both students and non student contacts (parents) it is a minimal table

Members Table - This is the table that holds the individual member details. Members can be

• students,
• parents
• adult students who are also parents or
• just adult students

Contact table - this is the table that holds the information for all contact details relating to a member and is ascribed to a Hub through the use of a FK

I have added the relationships diagram for the above 3 tables.

This way I am able to have multiple contact numbers for a single hub grouping.

The issue that I am running into is to do with the contact priorities.

For example -

there are 5 members in a hub,

• Parent 1 - Primary contact for the children - not a student
• Step-Parent - Emergency contact for the Children - IS a student
• Child 1 - age 11 - IS a student
• Child 2 - Age 12 - IS a student
• Child 3 - Age 19 - IS a student

We will have telephone numbers for the hub

• Parent 1 - (00000) 000 001 (currently set to Priority 1)
• Step parent - (01111) 111 112 (currently set to Priority 2)
• Child 3 - (03333) 333 332 (currently set to Priority 2)

When making contact with any student I need a query that will pick the correct number.

For a student under the age of 18 we need to contact the primary contact (00000) 000 001

For any student over the age of 18 we need to contact them directly

Step parent - (01111) 111 112
Child 3 - (03333) 333 332


This is where I am falling down, I have a calculated field in the query that defines the age based on the Date of Birth so I know if a member is over 18.

IN the query I have been able to use the IIF ( IIf([age]<18,1,2) ) function to return the correct contact for the students that are under 18 however each of the adult now has multiple contact numbers.

IMages Attached

I need to be able to run a secondary filter that further refines the results where the >18 members have the number ascribed to them in the [contactID] field. If I currently add any additional filters to show this then it removes the under 18 students from the results.



Any Ideas???????

 

[Ranman256]

Your tHub table should hold infinite members as a Household table.
HubID
Parent1
Parent2
Address
ChildID


Here the 2 parents can have many children.
EVERYONE should be in tPerson table.
Some of the tPerson will appear in tStudents.
You could also have a relationship field to show how 1 person is related to another.

 

[MajP]

Do this with a union query. For ease build qryOver18 that includes the direct contact and qryUnder18 that does not include the over 18 students and returns primary contact. Then union the two.

The other way to do it would be with a custom function that wraps a dlookup to return the correct contact base on the age of the student. The union would be more efficient.

 

[Blastedkane]

Hiya,

THis worked perfectly

Thank you so much