? Database design (1 Viewer)

Hi All

I am not sure where I should put this post but I will start here.

I have created a membership database for a racebike club. I have successfully recorded all their personal infor and bike details and monies paid to the club by each member.

What i want to do now is add a feature for the Accounts side. Accounts as in recording total monies paid by each menber and for what eg Membership, clothing, Parts, Misc. Sort of like a transactions record. I tried to modify the Transactions Database from the Mircosoft Templates site but it got too hard.

Can anyone suggest the path I should take please. I have attached a screen shot of my relationship I tried to set up with out success. DuesRecords is the table that contains the information.

Many thanksTee

you need a separate transaction table linked to each member, with key eg member and transaction date/time. This can be a unique key. In the transaction table you store the date, the cost, the item purchased - eg sub, racewear, spare etc. You can then easily pick up what each user has spect, or eg all the subs paid within a certain period. etc. Secondary keys might be used to manage the item purchased, but this will only affect the performance if you have many records.

Define queries you need by joining the members table and the transactions table, then base forms on the saved query.

As you get used to it, you can have forms with subforms - eg, the main form will step through members, and the sub form will bring up their appropriate transactions as you step through them, all automatically.

Good Luck