Help with table/query/report setup please

[teebird]

Hi All

I am having trouble setting up a table to contain this information and then putting it into a query and a report.

Members (many names)
Race Class (a member could be in 1 or more class)
Annual Membership (1 per year)
Insurance (1 per year)

My report I want to look something like this.

Race Class_Member Name_Current Membership Y/N_Current Insurance Y/N

Motoard:_Billy Brown_______Y________Y
_________Tommy Turtle______Y________N

Road Racer:___Billy Brown__Y________Y
______________Fred Bloggs__N________Y

Any suggestions would be really great.
Many thanks Tee

 

[Lhurgoyf]

What you are performing here is a Many-to-Many join, and therefore this will require THREE tables to make it work properly.

The first table, Members, will need a MemberID field (Autonumber) and a MemberName field (Text).
The second table, RaceClasses, will need a RaceClassID field (Autonumber) and a RaceClassName field (Text).
The third table, <unknown name>, will need five fields: ID (Autonumber), RaceClassID (Number), MemberID (Number), AnnualMembership (Yes/No), and Insurance(Yes/No).

You will need to set relationships between the three fields so that the one side is from the two "lookup" tables.

Finally, your query/reports will be based off the third table and can be grouped according to RaceClassID. This will provide the basic structure and then you can play with formatting and the like to get it to match your exact report style you are looking for.

Robert Johnson
Report/Application Developer
The Schenck Company

http://www.schenckco.com/