Help with table/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

 

[Ziggy1]

you will need 2 tables. The first table will be for members info and the second one will be the details related to the first. What you need is to create a main/Subform with these 2. So you will enter Each member info once, and then using the subform you will select the race details.

The way it works is in the main table the MainMemberID is unique to each member, the 2nd table uses the related field MemberID. the MainMemberID and MemberID will both be the same number for each member record. So if Billy Brown were MainMemberID 1001 then in the detail MemberID would also equal 1001 and every detail below would also be 1001 but the ClassDetID will increment to give a unique reference for each of the detail records.

MemberMain Table

MemberMainID [Autonumber]
MainMemberID [Number]
Members (plus Additional fields like Address)
Annual Membership Y/N
Insurance Y/N

ClassDetail

MemberID [Number]
ClassDetID [Autonumber]
Race Class (plus additional Race related fields)

You need to create a join (Union) query between the 2 tables linking the related fields, because of the One to Many relation your data will will appear to duplicate for those members with more than one race, which is ok as this is how it needs to be presented.

In a Report you will need to group you data by Race in order to display the way you want.


search this Forum for additional examples and sample databases for ...Subform