How to access a query, loop through it and extract info??

[BingeThinker]

Hi,

I have two tables. One table is a "family" table and the other is "family members". There is a one to many relationship.

I have a calculated field which is "family make-up" and I want to query the "family members" table based on the "Family ID" and then retrieve each family member's age and gender. I want to loop through it based on the number of family members

The problem I am having is how to extract that information in Access. Below is what I have tried. It will open the query entitled "familymembersorted" but I don't know how to filter in only those family members based on a family id and then how to extract their age and gender.

DoCmd.OpenQuery "familymembersorted"
DoCmd.GoToRecord acDataQuery, "familymembersorted", acFirst
FamilyMemberGender = Queries!familymembersorted!Gender
FamilyMemberAge = Queries!familymembersorted!Age
FamilyCompositionString = FamilyMemberGender + FamilyMemberAge

For i = 1 To FamilyCount
DoCmd.GoToRecord acDataQuery, "familymembersorted", acNext
FamilyMemberGender = Queries!familymembersorted!Gender
FamilyMemberAge = Queries!familymembersorted!Age

FamilyCompositionString = FamilyCompositionString +
FamilyMemberGender + FamilyMemberAge

Next i


Any help would be greatly appreciated.

 

[Tranman]

Hi BingeThinker,
I'd be happy to write a snippet for you that does what you want to accomplish. Just need answers to a couple of questions first:

Access version? 97/2K/XP

Names of the two tables?

Name and Format of the ID field in both tables? Text or number

Name of the "Family make-up" field in the family table?

Let me know.

Tranman

 

[BingeThinker]

Tranman,

Here is the info you requested. Thanks for your help!

BT
***

Access 97

Table 1 : Family
Primary Key: FamilyID (autonumber long integer)
FamilyType (text)

Table 2 : FamilyMember
Primary Key: PersonCode (autonumber long integer)
Foreign Key: FamilyID (long integer)

Note: the form is based on a query which uses Table 1 and 2.