Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-16-2018, 11:15 PM   #1
eugenelim0410
Newly Registered User
 
Join Date: Aug 2018
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
eugenelim0410 is on a distinguished road
Ms Access Relationship

I have built several tables for my database which includes the employee, client, engagement, team and task table.

I found that my tables contain a many to many relationship so I created a table to link the both tables.

Please advise if the relationship have any problem.

1 client will have many engagements.
1 engagement will have 1 team each time.
1 team consist of many employees.
1 employee can be allocate into different teams.

Please check the attachment for the relationship.
Attached Images
File Type: png relationship.PNG (25.8 KB, 34 views)

eugenelim0410 is offline   Reply With Quote
Old 08-17-2018, 12:38 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,696
Thanks: 138
Thanked 1,532 Times in 1,504 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Ms Access Relationship

You should remove the employeeID from the Team table, as that is handled by the Junction table.
I'm unsure of why the Task Table is linked to the Engagement table ?
Should the task not be allocated to a team and then employees for the task follow that team?

Generally you shouldn't have circular relationships.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 08-17-2018, 01:07 AM   #3
eugenelim0410
Newly Registered User
 
Join Date: Aug 2018
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
eugenelim0410 is on a distinguished road
Re: Ms Access Relationship

I have remove the employeeID from team table.

For the task part, what I want is after I allocate employee into a team and then assigned tasks for the team member. So should I linked the task table with team-employee table? Please advice.

Thank you so much!

eugenelim0410 is offline   Reply With Quote
Old 08-17-2018, 01:18 AM   #4
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,587
Thanks: 308
Thanked 402 Times in 387 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Ms Access Relationship

Speaking from experience, I'd also alter any 'ID' fields to a better more meaningful name, as you have done with the other tables..
If you combine tables in a query with same named fields, Access asks you which is which.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is online now   Reply With Quote
Old 08-17-2018, 05:16 AM   #5
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,934
Thanks: 10
Thanked 2,147 Times in 2,102 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Ms Access Relationship

2 big issues, then some nits to picks:

1. Ciruclar path in your relationships. There should only be one way to travel between tables in your relationships, you have a circuit which is incorrect. You can go clockwise or counter clockwise to get from Team to Task Allocation, that's wrong, one of those relationships need to go.

2. Storing redundant data. [Client Name] should not be in Engagement. You've already got it in Client and its linked via [Client ID] so its connected, no need to store in multiple places.

Only use alphanumeric characters in names. That means no spaces, no slashes, no dashes, etc. It just makes coding and querying easier down the line. For example, that means [Team - Employee] should probably become [TeamEmployee] and [Employee ID] should become [EmployeeID].

What's the attachment? Generally you store files in a directory and put the path to them in the database. So, Photo7.jpg gets saved on the network and "P:\\DatabaseImages\Photo7.jpg" goes into a text field in the database.

Ungenericize field names. Not only would I prefix all those ID fields with what ID they are (EmployeeID, TaskAllocationID), but all the other field names that are not unique across the database (ClientCity, ClientSTate, EmployeeCity, EmployeeState).
plog is offline   Reply With Quote
Old 08-17-2018, 05:48 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,572
Thanks: 57
Thanked 1,874 Times in 1,824 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Ms Access Relationship

In addition to the advice given so far, I'd like to see a 4-5 line description of the "business process(es)" in plain, simple English. Then, based on your description, a list of business rules that you have started.
1 client will have many engagements.
1 engagement will have 1 team each time.
1 team consist of many employees.
1 employee can be allocate into different teams
.

Please tell us a little about "engagement".
Can an Employee be on multiple teams at the same time?
It sees you are interested in which team member is associated with an engagement.

Perhaps you could describe a "day in the life of the business" to put the pieces into context.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 08-18-2018, 06:35 AM   #7
eugenelim0410
Newly Registered User
 
Join Date: Aug 2018
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
eugenelim0410 is on a distinguished road
Re: Ms Access Relationship

The system that I wish to create is a staff allocation. For every client, a team of employee will sent out for providing service which is call engagement. An employee will not be assigned into different team under same time.

eugenelim0410 is offline   Reply With Quote
Old 08-21-2018, 02:57 PM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: Ms Access Relationship

If a given employee cannot be on more than one team at a time, then you will want to put two dates into your "TeamEmployee" table; DtJoined, DtDeparted. These will hold when an employee is assigned to and leaves a team. These may or may not be the same for all team members, but if you prepare for them to be different NOW, you won't be working out how to handle when one member is hospitalized and needs to be replaced.

DO NOT put a flag into the Employee table to check if they are "Available". Instead do a query to return any records that have a Join date without a Depart date. This will give you the most up to date result.

Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Relationship Help! kafkat1 Tables 3 10-21-2012 04:48 AM
Relationship Advice (in Access) JamesMcS General 2 07-27-2010 11:32 PM
Access relationship question Arby General 9 11-17-2006 01:03 PM
Access relationship..the best way... Tech Tables 6 03-29-2005 03:12 PM
Relationship Problem--WITH ACCESS! :-) livingNcolorado General 1 09-26-2004 01:27 PM




All times are GMT -8. The time now is 04:15 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World