Help with Relationships and how to achieve a goal.

[kimhuff]

I am using Access 2007. I know many of the long-time access programmers seem to hate this version but it is the only version I have.



Although I am not a novice when it comes to Access... I am definitely not good at complex databases. (Granted this will probably seem easy to all of you.) Anyway, I am trying to create a database that will track the membership for a non-profit sci-fi group I belong to.

I have a membership table with all the normal name, address, phone numbers, email address, etc. We also need to track promotions within the group, when a new membership was purchased and the renewals.

I created a Promotions table with the date of the promotion and the new rank. When I create the Membership form.. I would like for there to be three subforms.. one for a list of all the renewals, one that lists all the promotions and a final one that shows a list of notes left on the account. I am going to want the most recent entry listed at the top. I am confused by exactly which fields I need in each table and how the relationships need to be set. Each member will only have one rank at any given time, but he or she can have several promotions. How will Access know which "rank" to pull for a report or on a form?

I also need to be able to track those that need to renew and be able to print a list of current and expired members. I did not create a field for Active and Expired members because I was hoping the database would be able to calculate this by the month and year the person joined. This too is an aspect I dont know how to setup.

I have attached what I have done so far and I was curious if someone can look at what I have done and let me know how far off I am and what should be my next step. I know it is important to get the relationships right and to have a good normalization. PLEASE, take a look and maybe you can point me in the right direction.

Thank you,

Kim Huff
Indianapolis, IN

[kimhuff]

Hmm.. with 59 views I thought someone would be willing to help me with Relationships or at least point me in the right direction. Someone?? Please??

[orange]

Many peple who participate in these forums do NOT have Acc 2007 or 2010, and so cannot work with accdb. So many view your post and go no further.

[kimhuff]

Orange,

Thank you for your reply. I was concerned about this. Is there a way I can change it to a 2003 so at least I can get some insight?

Kim

[orange]

As I per my profile, I have 2003. Based on comments within the forum I'm sure there is an option to save in mdb format.

[ssanfu]

You might start out by printing your relationship window to a .pdf. We could at least see your relationships..

[radex7]

I played with your DB a little, and although I am not a pro, I feel obligated to help someone out, just like I have been helped in the past. Your relationships were slightly confusing, and I am not a fan of autonumbers so I changed things to fit the way I think and work. This may be a good starting point.

Attachment 2892

Keep us posted.

[ssanfu]

<snip>
and I am not a fan of autonumbers so I changed things to fit the way I think and work.
<snip>

radex7, I am always interested in the how and why of database design (still trying to develop the art and skill). I use autonumbers 99.9% of the time. Would you mind me asking why you don't like to use autonumbers?

Thanks

[radex7]

radex7, I am always interested in the how and why of database design (still trying to develop the art and skill). I use autonumbers 99.9% of the time. Would you mind me asking why you don't like to use autonumbers?

Thanks

ssanfu, I am not that good at it myself, and the reason I don't like autonumbers is because they are meaningless to me. Maybe it is because of the data type I deal with. I am a big fan of composite keys.
Take a look at my thread here:
https://www.accessforums.net/databas...html#post44054

That approach allows me to see what is what and the output from any query I create is ready for statistical analysis.
It may be important that I collect and add data on annual basis?