Another MS Access question ...

[zcacogp]

Chaps,

You have been helpful with an access question before, and here is another one. If you can help, please do ... !

I have a database, with lists of children and families. Each child is placed into one of 5 groups, and there is a list of these 5 groups as well.

Each child is associated with one family, and the family surname is NOT on the child list - the family number is, and this family number is the primary key for the family to which they are associated (i.e. "FamilyNumber" is a foreign key on the "Child" list.)

The same goes for each group - each child is allocated to only one group, and the "GroupNumber" is a foreign key on the "Child" table as well.

I can run a search to display all the children in any one group, but how do I get the search results to display the family name rather than the family number? (i.e. I want the search to find "Billy" as he is in group "Jotters", and say 'He is family 48, therefore his surname is "Smith"', and display "Billy" and "Smith" rather than "Billy" and "FamilyNumber 48".)

How do I do this? (Simply, in words of few syllables!)


Oli.


>>> Edited by zcacogp on Friday 13th August 13:01

[pdV6]

Something like:

SELECT C.Forename, F.Surname FROM Child AS C INNER JOIN Family AS F ON C.FamilyNo = F.ID WHERE C.GroupNo = {whatever your number is)

I haven't run this through Access to check it, but you get the idea...

[zcacogp]

pdV6,

Thanks very much for replying.

I am doing this in Access, and am not using SQL ... how do I do this little trick in Access? (Or am I being very dense?)


Oli.

[pdV6]

Create a new query and switch to SQL view to enter the text directly.

Alternatively:

1. Create a new query in design mode add your 2 tables using the dialog it prompts you with.
2. If its not already there automatically (due to your relationship), drag a link between the Child.FamlilyNo and Family.ID fields to set up the join.
3. Double click the Child.Forename and Family.Surname fields to add them to the output of the query.
4. Double click the Child.GroupNo field to add it to the output, but then untick the "show" box for this field.
5. For the Child.GroupNo field, enter [Group No?] in the "Criteria" box.
6. Save the query and give it a name.
7. Run the query, and it should prompt you with an input box "Group No?".
8. Type the number you want and the results will be displayed.

[zcacogp]

pdV6 said:

Create a new query and switch to SQL view to enter the text directly. Alternatively: 1. Create a new query in design mode add your 2 tables using the dialog it prompts you with. 2. If its not already there automatically (due to your relationship), drag a link between the Child.FamlilyNo and Family.ID fields to set up the join. 3. Double click the Child.Forename and Family.Surname fields to add them to the output of the query. 4. Double click the Child.GroupNo field to add it to the output, but then untick the "show" box for this field. 5. For the Child.GroupNo field, enter [Group No?] in the "Criteria" box. 6. Save the query and give it a name. 7. Run the query, and it should prompt you with an input box "Group No?". 8. Type the number you want and the results will be displayed.

pdV6,

Thanks, this is about as far as I can get already. How do I arrange for the input group number to be selected from a seperate form? (My hoped-for design is a combo box, with the group names on a pick-list. Once you have picked the name, a list of the children is produced in a sub-form, with their forename, DOB and surname. Getting the surname is the difficult bit.)

Thanks again for your help.


Oli.

[pdV6]

I'm not too hot on Access Forms, but e-mail me & I'll send you a relevant example (in Access 2000 if that's ok)