Table Relation Question

[No Spam]

To fellow Access 2K users:

I have run into a newbie situation where I have been assigned to
create an Access database to act as a schedule. Basically, it is a
very simple setup: the end product is an Excel like grid that needs
to have:

* The periods along the top of the grid (which are weekly periods)
* The names of the emploees along the left
* The clients in the body of the grid

Here's a sample:
Name: Feb 9-13 Feb 16-20 Feb 23-27
*************** *************** *************** *************** *************** **********
Joe X Client X Client Y
Client Z
Client Y Client Z
Client X

Bob X Client Z Client X
Client Y
Client X

My main concern is that there can be more than one client for a weekly
period for an employee.

Can anyone please help me set up the tables (relationships and key
fields)? Thanks a million in advance!

Kevin

[DC Fan]

Looks like you need a set of three tables creating a many-to-many
relationship:

Employees 1 --> oo EmployesToClien tsIndex oo <-- 1 Clients

Employees in the Employee table, clients in the client table. Store the
date of the meeting in the EmployeesToClie ntsIndex table along with the
foreign references to the employee ID and the client ID. If one employee
can't meet with one client more then once on the same day, then the
combination of the employee, client and date becomes your unique index. If
they *can* meet with a client more then once in a day, you proabably want to
create a PrimaryKey that is some sort of autonumber "meeting ID" in the
EmployeesToClie ntsIndex table as your unique index. To display the data,
you need to use a crosstab query.

Hope this helps.

-DC Fan

"No Spam" <no****@earthli nk.net> wrote in message
news:cm******** *************** *********@4ax.c om...

To fellow Access 2K users:

I have run into a newbie situation where I have been assigned to
create an Access database to act as a schedule. Basically, it is a
very simple setup: the end product is an Excel like grid that needs
to have:

* The periods along the top of the grid (which are weekly periods)
* The names of the emploees along the left
* The clients in the body of the grid

Here's a sample:
Name: Feb 9-13 Feb 16-20 Feb 23-27
*************** *************** *************** *************** *************** *
********* Joe X Client X Client Y
Client Z
Client Y Client Z
Client X

Bob X Client Z Client X
Client Y
Client X

My main concern is that there can be more than one client for a weekly
period for an employee.

Can anyone please help me set up the tables (relationships and key
fields)? Thanks a million in advance!

Kevin

[No Spam]

The table structure worked great! However, displaying it is another
story. When I created a crosstab query, it appears as though I can
only display count, max, etc. of my clients. Is there any way to
display the employee names along the left, the periods along the top,
and the clients in the "grid" or body? Thanks!

Name........... .Period 1.............. Period 2.............. Period 3
*************** *************** *************** *************** *********
Joe X.............C lient 1.............. Client 1.............. Client 2
............... ........Client 2.............. Client 3.............. ...
............... ........Client 3

Mary X...........Cli ent 3.............. Client 1.............. ...
............... ............... ............... ....Client 3.............
Kevin


On Wed, 11 Feb 2004 11:14:12 -0500, "DC Fan"
<gr************ *******@comcast .net> wrote:

Looks like you need a set of three tables creating a many-to-many
relationship :

Employees 1 --> oo EmployesToClien tsIndex oo <-- 1 Clients

Employees in the Employee table, clients in the client table. Store the
date of the meeting in the EmployeesToClie ntsIndex table along with the
foreign references to the employee ID and the client ID. If one employee
can't meet with one client more then once on the same day, then the
combination of the employee, client and date becomes your unique index. If
they *can* meet with a client more then once in a day, you proabably want to
create a PrimaryKey that is some sort of autonumber "meeting ID" in the
EmployeesToCli entsIndex table as your unique index. To display the data,
you need to use a crosstab query.

Hope this helps.

-DC Fan

"No Spam" <no****@earthli nk.net> wrote in message
news:cm******* *************** **********@4ax. com...

To fellow Access 2K users:

I have run into a newbie situation where I have been assigned to
create an Access database to act as a schedule. Basically, it is a
very simple setup: the end product is an Excel like grid that needs
to have:

* The periods along the top of the grid (which are weekly periods)
* The names of the emploees along the left
* The clients in the body of the grid

Here's a sample:
Name: Feb 9-13 Feb 16-20 Feb 23-27

************** *************** *************** *************** *************** **
*********

Joe X Client X Client Y
Client Z
Client Y Client Z
Client X

Bob X Client Z Client X
Client Y
Client X

My main concern is that there can be more than one client for a weekly
period for an employee.

Can anyone please help me set up the tables (relationships and key
fields)? Thanks a million in advance!

Kevin