By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,493 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Table Relation Question

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


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

Employees 1 --> oo EmployesToClientsIndex oo <-- 1 Clients

Employees in the Employee table, clients in the client table. Store the
date of the meeting in the EmployeesToClientsIndex 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
EmployeesToClientsIndex table as your unique index. To display the data,
you need to use a crosstab query.

Hope this helps.

-DC Fan

"No Spam" <no****@earthlink.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

Nov 12 '05 #2

P: n/a
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.............Client 1..............Client 1..............Client 2
.......................Client 2..............Client 3.................
.......................Client 3

Mary X...........Client 3..............Client 1.................
.................................................C lient 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 EmployesToClientsIndex oo <-- 1 Clients

Employees in the Employee table, clients in the client table. Store the
date of the meeting in the EmployeesToClientsIndex 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
EmployeesToClientsIndex table as your unique index. To display the data,
you need to use a crosstab query.

Hope this helps.

-DC Fan

"No Spam" <no****@earthlink.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


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.