Create a query to list which tables an item is located in

[Sean Byrne]

We have a Microsoft Access 2000 database consisting of 20 tables
covering 20 different events. In each table, there are 3 Team
members, a date of the event and several unique fields for the event,
which vary from table to table. There is also another table that
holds the team members' details. There is a one-to-many relation
between the Team member table and the other table's 3 Team member
fields, so that the event tables only holds three team ID's rather
than the Team member details directly.

All went well until my manager asked me that he needs a query that
will give a list of members along with which events they were involved
in including the dates the event took place. Sounds simple? Yet I
cannot figure out how to build the query :-(

Ideally, the query will give three columns: 1st column showning the
Team member ID, 2nd column showing the table name (i.e. the name of
the event) and the third column showing the date of the event.

Is this possible in Microsoft Access?

The only solution I can think of is to create another table that would
hold the team member ID, Event name and date such that each time an
addition would be made to any table, a Macro would add the team member
ID's along with the table name and dates to this extra table.
However, I would also have to add more Macros to cover should someone
change an existing record (such as change one of the team member IDs)
or even delete a record.

[Geoff]

Hi Sean,

It looks like the problem is with the design of your database.

It might have been better to have just three tables:

1. Events
2. Team Members
3. Data

The Events Table should have an EventID field (primary key). The
table should hold 20 records, one for each event, holding its date,
time, venue, etc.

The Team Members Table should have a TeamMemberID field (primary key).
The table should hold one record for each Team Member, name,
department, etc.

The data table will have just two fields: (1) fkEventID and (2)
fkTeamMemberID. (These are known as foreign keys.) You only store
two numbers in each record of this table, which link back to the other
two tables.

You need a one-to-many relationship from the EventID field (in the
Events Table) to the fkEventID field (in the Data Table).

Also, you need a one-to-many relationship from the TeamMemberID field
(in the Team Members Table) to the fkTeamMemberID field (in the Data
Table).

You need one record in the Data Table for each combination of Event
and Team Member (ie three records for each event, but you can have as
many as you like), eg:

Record 1 - fkEventID=1 - fkTeamMemberID= 5
Record 2 - fkEventID=1 - fkTeamMemberID= 11
Record 3 - fkEventID=1 - fkTeamMemberID= 16
Record 4 - fkEventID=2 - fkTeamMemberID= 7
Record 5 - fkEventID=2 - fkTeamMemberID =4
Record 6 - fkEventID=2 - fkTeamMemberID= 17
Record 7 - fkEventID=2 - fkTeamMemberID= 9
etc

So for 20 events and three Team Members per event, the Data Table will
hold 60 records.

With your data structured in this (normalised) way, you should be able
to run any reports/groupings you like.

For your report, create a query showing all three tables in the QBE
grid and drag in all fields to the grid. You can drag down the
asterisk from each table.

For your report, base your report on the query. Use the Grouping and
Sorting toolbar button to group and sort as your boss wants.

HTH
Geoff

[Reggie]

Sean, I think you would be best to redesign your db to a relational database configuration to
something like this:

tblMembers:
MemberID(Primar yKey)
fName
lName
activeMember(Ye sNo field)
.....

tblEvents:
EventID(Primary Key)
EventName
EventDate
activeEvent(Yes No field)
.....

tblMemberEvents : (these 2 fields form the PrimaryKey)
MemberID(Foreig nKey)
EventID(Foreign Key)

Now you can assign a member to any event and any event to any member. It's now easy to pull a
member and display all the events they are associated with and the dates of those events. Now if
you change the description of a member or event you only need to do it in one place and it will be
reflected in all your records. This will also prevent the user from deleting a member or event if
has a record in the tblMemberEvents table. If you don't want to use the member or event anymore set
the active field to false and filter those records out.

Hope this helps!

--
Reggie

----------
"Sean Byrne" <se************ *****@yahoo.com > wrote in message
news:8b******** *************** ***@posting.goo gle.com...

We have a Microsoft Access 2000 database consisting of 20 tables
covering 20 different events. In each table, there are 3 Team
members, a date of the event and several unique fields for the event,
which vary from table to table. There is also another table that
holds the team members' details. There is a one-to-many relation
between the Team member table and the other table's 3 Team member
fields, so that the event tables only holds three team ID's rather
than the Team member details directly.

All went well until my manager asked me that he needs a query that
will give a list of members along with which events they were involved
in including the dates the event took place. Sounds simple? Yet I
cannot figure out how to build the query :-(

Ideally, the query will give three columns: 1st column showning the
Team member ID, 2nd column showing the table name (i.e. the name of
the event) and the third column showing the date of the event.

Is this possible in Microsoft Access?

The only solution I can think of is to create another table that would
hold the team member ID, Event name and date such that each time an
addition would be made to any table, a Macro would add the team member
ID's along with the table name and dates to this extra table.
However, I would also have to add more Macros to cover should someone
change an existing record (such as change one of the team member IDs)
or even delete a record.

[Sean Byrne]

Thank you very much for your help ;-)

As I originally thought the database layout was designed as the way it
should be implemented in Access, I done the mistake of building all
the tables and forms only to realise the above problem when I started
working on the Query.

In the end, I got out a blank of paper and redesigned the database
trying to keep the event tables, but moving Team Members to a Team
Member table, Event details (comments, location, date) to an Event
table. I added an Event ID to the Event Table as a primary key and
then used it has a primary key to the 20 other tables and Team member
table. I created a one-to-one relationship between the 20 event info
tables and the event table.

Once built, it made querying based on Team Members and Event locations
very simple. The Forms and Reports were very simple to build as a
result by using Subforms and also means that each record is not
restricted to 3 Team Members should this requirement change later on.

Sorry that I could say what is on these 20 tables or what this
database is used for as the information stored in database is
confidential and we must have it ready to supply to the customer later
this week. Every table has its own set of unique fields (apart from
Team Members, location and certain event details) which meant I had to
stick with 20 tables, but moving out the duplicates present on each
table made such a difference and I was able to build all the necessary
queries and reports.

Best Regards,
Seán.

[Reggie]

Glad to hear!

--
Reggie

----------
"Sean Byrne" <se************ *****@yahoo.com > wrote in message
news:8b******** *************** ***@posting.goo gle.com...

Thank you very much for your help ;-)

As I originally thought the database layout was designed as the way it
should be implemented in Access, I done the mistake of building all
the tables and forms only to realise the above problem when I started
working on the Query.

In the end, I got out a blank of paper and redesigned the database
trying to keep the event tables, but moving Team Members to a Team
Member table, Event details (comments, location, date) to an Event
table. I added an Event ID to the Event Table as a primary key and
then used it has a primary key to the 20 other tables and Team member
table. I created a one-to-one relationship between the 20 event info
tables and the event table.

Once built, it made querying based on Team Members and Event locations
very simple. The Forms and Reports were very simple to build as a
result by using Subforms and also means that each record is not
restricted to 3 Team Members should this requirement change later on.

Sorry that I could say what is on these 20 tables or what this
database is used for as the information stored in database is
confidential and we must have it ready to supply to the customer later
this week. Every table has its own set of unique fields (apart from
Team Members, location and certain event details) which meant I had to
stick with 20 tables, but moving out the duplicates present on each
table made such a difference and I was able to build all the necessary
queries and reports.

Best Regards,
Seán.

[Geoff]

Good luck with your project.
Regards
Geoff