If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relationships?
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. |
#2
|
|||
|
|||
Relationships?
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 "John S" wrote: 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. |
#3
|
|||
|
|||
Relationships?
On Wed, 31 Dec 2008 10:23:01 -0800, John S
wrote: 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 |
#4
|
|||
|
|||
Relationships?
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" wrote: On Wed, 31 Dec 2008 10:23:01 -0800, John S wrote: 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 |
#5
|
|||
|
|||
Relationships?
On Wed, 31 Dec 2008 13:40:11 -0800, John S
wrote: 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 |
#6
|
|||
|
|||
Relationships?
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 "John S" wrote in message ... 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" wrote: On Wed, 31 Dec 2008 10:23:01 -0800, John S wrote: 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 |
Thread Tools | |
Display Modes | |
|
|