Relationships?

[John S]

Hi,

I have a database with two similar tables: "Members" and "Non-Members". The
members and non-members make-up "Volunteers" (table) who work at "Events"
(table).

I have used a Union Query in a Combo Box to assign members/non-members to
the "Volunteers" table.

When creating a "Volunteers by Event" report, I am unable to return the
personal details of the members/non-members which are stored in the "Members"
/ "Non-Members" tables.

This may be because I haven't set the appropriate relationships. I have
considered combining the "Members" and "Non-Members" tables but if possible
would like to avoid this.

Any suggestions would be greatly appreciated.

Regards,
John.

 

[Clifford Bass]

Hi John,

Combine those tables into a general people table and add a boolean
Member field so that you can distinguish members from non-members.

tblPeople
PersonID
Names
Address
Member
etc.

Or, it can get more complicated if your membership comes and goes. For
example: Jack Anderson was a member last year, is not this year, but is a
volunteer and next year will be neither. In which case you may want a
person-associations table that lists who is/was a member, volunteer or
whatever and when.

tblPersonAssociations
PersonID
Association (possibly use an association codes
table and make this an AssociationCode)
StartDate
EndDate

Your events table should only list information specific to the event:

tblEvents
EventID
EventName
EventDescription
StartDate[/Time]
EndDate[/Time]
etc.

You also should have an event workers table to join between your people
to the events:

tblEventWorkers
EventID
PersonID
Role (possibly use a separate table to hold roles and make this a RoleID)
etc.

Now when you do your report you can use a query that uses the
tblEventWorkers table to associate the appropriate people in tblPeople (using
PersonID) with the appropriate events in tblEvents (using EventID).

Hope this helps,

Clifford Bass

 

[Armen Stein]

I have a database with two similar tables: "Members" and "Non-Members". The
members and non-members make-up "Volunteers" (table) who work at "Events"
(table).

I have used a Union Query in a Combo Box to assign members/non-members to
the "Volunteers" table.

When creating a "Volunteers by Event" report, I am unable to return the
personal details of the members/non-members which are stored in the "Members"
/ "Non-Members" tables.

When you use a Union query to drive the combobox, you are storing the
primary key in the Volunteers table, right? But you are storing
something that could refer to one of two different tables - Member or
Non-Member.

Later, when you need to look up the data, how does the query know
which one to look up?

Your thought to combine the tables is correct. Members and
Non-Members probably share a lot of fields, and you can use a status
field or even a simple checkbox to indicate if they're a Member.

If you really don't want to separate them, then you could store the
Member and Non-Member keys separately in the Volunteers table. This
is a suboptimal work-around though.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John S]

Many thanks for the suggestions - I will try them out. One of the reasons I
wanted to keep the two tables separate was that certian users will only have
access to one table. I will try to solve the data access problem with further
Queries, once I've re-designed the db.

I'll get back to this forum if I get stuck again.

Once agian - many thanks.

Regards,
John.

 

[Armen Stein]

One of the reasons I
wanted to keep the two tables separate was that certian users will only have
access to one table.

Do you mean using Access security (known as ULS)? That is one of the
reasons to split data into two tables, but it's quite complex to
implement correctly. Especially when you're trying to blend the
records together into one query or combobox.

If you're just talking about controlling access to certain records in
your application using form coding, then you could do the same thing
with the Member yes/no field.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Jeff Boyce]

John

To build on Armen's suggestion, you could use a query that returns only
Members to display for your users who are authorized to see Member data, and
another query that returns only non-Members for folks so-authorized...

Regards

Jeff Boyce
Microsoft Office/Access MVP