I would like some help please.

I have Access 2007

The fields are ID, Name, Sponsor #

Each person, member and guest will have a separate record and ID number, Guests will also have a sponsor number which is the ID number of the member they are the guest of. Each record will have a check boxes to denote member or guest

What I’m looking to do is have a query that will look at each record and bring back a list of each member and the ID number of each of their guests.

Thanks you for any help

A person can only be a member or a guest? Once checkbox is sufficient to indicate Member. Not ticked means guest.

Indeed simply placing a value in the sponsor field would indicate Guest and the check boxes would be entirely redundant.

Your form could still include a checkbox for Guest.

Its Control Source would be:
=IsNull(Sponsor)

Default value : False

Changing the checkbox would trigger code that checked for the presence of a Sponsor if necessaryand demanded it. Otherwise it would set the Sponsor to Null.

Attempting to save the record would also perform the test.

BTW Don't use special character like # in names. These characters have special meanings and are best avoided for readability and reliability of the code.

There are multiple check boxes to note if a person is a member, guest or spouse.

A little background might clear things up. When a person fills out a paper registration form they will be asked their name, the name of their spouse and the names of any guests they have attending the event. when we put the info into the Data base each person is put into a separate record and given an ID number. If the person is marked as a guest or spouse of a member they will have the ID number of that member entered into their sponsor field. when I run a report the info I'm trying to get is

Member name - Spouse ID, Guest 1 ID, Guest 2 ID

something like this:

john Smith - 325, 234, 332

I hope this makes sense

I would use a Guest subform in Datasheet mode on the Member mainform.
The subform could list any number of Guests.

Otherwise you will have to use a crosstab query to generate the fields as you have indicated.

Thank you for all your help, Could you explain how to set up a crosstab query that would list anyone who had that members ID in the sponsors field as I showed.

I don't mean to be imposing, I am just not well versed in Access. Again thank you

There is a good explanation on this page.
http://allenbrowne.com/ser-67.html

I think you will need the type explained at the bottom of the page.
It uses another table for the crosstab column headings.

Thank you.

I tried the subform like you suggested. The problem I am running into is the query will not show members that do not have a guest listed. when I put a guest in the subform the member shows up in the query. I would like to list all members with and without guests in the subform. Thank you