Design question

  • Thread starter Thread starter Striker
  • Start date Start date
S

Striker

I am pretty new to Access 2007 so pardon.

I need to keep track of members attendance percentage at events. The
problem is their attendance percentage is for the number of events they
attend from the time they become members or from the Create Date in the
Members TBL.

1. How should I track the total number of events? Do I need another table
for this?
2. How do I query and get a percentage of events attended since they became
a member?

I built Members TBL with
UserName txt20 (Primary Key)
Create Date Date/Time General
Other fields that include password, address, phone etc.

Events TBL
AutoNumber (Primary Key)
UserName txt20
Event Date Date/Time General

Can someone suggest a better table structure? and a query that joins the
table properly to give me a attendance percentage from member create date.
 
Your current user allows only 1 member to attend an event.
If several members attend, you need
TblMember
MemID
FirstName
Surname etc
fields only about the member

TblEvent
EventID
EventDate
EventVenue or whatever
fields only about the event

TblMemberEvent
MemEventID
MemID
EventID
fields only about the member attending the event

Evi
 
That seems reasonable, how do I query and get an attendance percentage from
the time the member joined?
 
One possible way would be a formula like this in your query
PercAttend: DCount("[MemID]","TblMemEvent","[MemID]=" &
[TblMemEvent].[MemID])/DCount("[EventID]","TblEvent","[Event Date]>=" &
Format([Create Date],"0"))

Format the results of this as a %

Change the table and field names to your real ones.

Evi
 
Back
Top