DB Table/Query Layout for Ranking System

15 pts.
Tags:
Access Database
Database
Microsoft Access 2007
Microsoft Access queries
Query
Hello, I'm building a Membership DB where Members can achieve certain ranks (Rank1 - Rank14). I would like to present two things: - a list of all Members with their current rank and the Date the rank was achieved - for each Member a list of their ranks and the corresponding dates of achieving them. I don't know exactly how to do the table design and the following queries for this to work. Do I go on creating a table for the members - tblMember with MID, Name, etc. and a table for each individual rank - tblRank1 - tblRank14 with RID, RankName and Date ?? How would I then query for the current rank ? I'm really stuck here and would appreciate any help. hope the explanation wasn't too fuzzy, greetings

Software/Hardware used:
Win7, MS Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

tbl_Member with MID, Name etc
tbl_Rank with RID, rank_name, Description etc
tbl_Member_Rank with MID, RID, Effective_From (date), Effective_To (date)

When a member gets a rank, if they have a previous rank, then set their effective_to date as the current date for that record (using mid, rid parameters), and then create a new record with the new rank, effective_from is the current date and leave the effective_to as NULL (or if you don’t like NULLs in your db, then set a date far in the future e.g. 01/01/4000 and adapt the query). For new members, just create the new record as above (no update required)

1) Then you can select everyones current rank:
select m.name, r.rank_name, mr.effective_from
from tbl_Member m, tbl_Rank r, table_Member_Rank mr
where m.mid = mr.mid
and r.rid = mr.rid
and effective_to is null

2) Select a member with all their ranks and when achieved
select m.name, r.rank_name, mr.effective_from
from tbl_Member m, tbl_Rank r, table_Member_Rank mr
where m.mid = mr.mid
and r.rid = mr.rid
and mid = ?

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Artificial
    thank you so much ! domo arrigato
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following