Query from two tables.

[stevey03]

Hi I'm having trouble with this query which I'm sure is possible to do. The system is a nursery system, and the query involves taking the child's surname and returning all the details on all of the carer's. The problem is that no data shows in the query.

The way the carer's table relates to the child table using a carer Id number. The Child table has 3 of these fields (Carer ID 1, Carer ID 2 and Carer ID 3). but the Carer's table only relates to one of these. Here's the tables.

CHILD (ID,Surname,Forename,Carer 1, Carer 2,Carer 3...) (Other fields have no relevance to the query).

CARER (ID, Surname,Forename,etc...)

In the Carers table the ID number relates to the carer Id numbers in the Child table, but these all have different values.

If anybody could help, I would Appreciate it.

Steve

 

[Norie]

Can each child be cared for by more than one carer and each carer care for more than one child?

If that is the case then you have a many-to-many relationship between child and carer.

You should probably reorganise your data structure to include a table that represents the relationship cares for/is cared for by.

CHILD(ChildID, Surname, Forename, etc)

CARER(CarerID, Surname, Forename, etc)

CARESFOR(CarerID, ChildID)

 

[stevey03]

Each child can have up to 3 Carers. All of the ID numbers are recorded in the child table. The carer can appear in the child table more than once though, i.e if there are two children from same family. However the carers table doesn't hold the child ID number.

So one child can have many carers, and one carer, can have many children. I think I've overcomplicated the database.

 

[Andrew Fergus]

Hi Steve
You haven't made this overly complex and based on what you have described you have a many-to-many relationship between the carers and children. The advice Norie has provided is spot on and there is a reference page from Microsoft here that supports Norie's suggestion. Having the right database design means you won't be grappling with your data in future.
HTH, Andrew.

 

[stevey03]

Yeah your right. That should do it. Thanks for the advice Norie/Andrew. I'll post back if i have problems.

 

[stevey03]

Have put the additional table in and it works like a dream, just had to tweak all my forms. Thank you for all your help there!

Steve