Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-12-2019, 08:02 AM   #1
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 140
Thanks: 77
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Degrees and employees

Each employee may have various degrees from different dates.
How correctly to to build a table so that same employee will not get already existing degree?

Jonny is offline   Reply With Quote
Old 08-12-2019, 08:06 AM   #2
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: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Degrees and employees

Please post what you already have and how you determine "existing degree"?
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
Jonny (08-13-2019)
Old 08-12-2019, 08:10 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Degrees and employees

Hi. Sounds like you might need a junction table with a composite primary key field.

__________________
Just my 2 cents...

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.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Jonny (08-13-2019)
Old 08-12-2019, 08:10 AM   #4
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 140
Thanks: 77
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Re: Degrees and employees

Have a table with:
ID (key), EmpID, DegDate, Degree.
How restrict that for a same EmpID will not be added same Degree.
Jonny is offline   Reply With Quote
Old 08-12-2019, 08:15 AM   #5
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: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Degrees and employees

Do you have a list of "Degrees" that you can choose from? if so, I'd have a drop down on it. If not, you should be able to check the DegDate and make sure they don't add two degrees for the same date.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
Jonny (08-13-2019)
Old 08-12-2019, 08:28 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Degrees and employees

Quote:
Originally Posted by Jonny View Post
Have a table with:
ID (key), EmpID, DegDate, Degree.
How restrict that for a same EmpID will not be added same Degree.
Hi Jonny. I agree with Mark. I also think you need a table structure similar to the following:


tblEmployees
EmpID, pk
etc...


tblDegrees
DegreeID, pk
etc...


tblEmpDegrees
EmpID, fk, pk
DegreeID, fk, pk

DegreeDate
etc...


In tblEmpDegrees, you can create a primary key based on both the EmpID and DegreeID fields together.
__________________
Just my 2 cents...

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.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Jonny (08-13-2019)
Old 08-12-2019, 08:29 AM   #7
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 140
Thanks: 77
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Re: Degrees and employees

Quote:
Originally Posted by Mark_ View Post
Do you have a list of "Degrees" that you can choose from? if so, I'd have a drop down on it. If not, you should be able to check the DegDate and make sure they don't add two degrees for the same date.
The data is added to that table from the from, so drop down may be required on the form.Still the question is same "how I make sure" not to add two same degrees for the same employee?
Is it something that should be configured in Index propeety?

Jonny is offline   Reply With Quote
Old 08-12-2019, 09:06 AM   #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: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Degrees and employees

A unique index on Employee and date would prevent you from having two degrees issued the same date.

If you use a list of degrees, you can Join it to your table of degrees and exclude all where there is record for this employee. This means your users won't see it in the drop down list.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
Jonny (08-13-2019)
Old 08-12-2019, 09:37 AM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,768
Thanks: 93
Thanked 1,726 Times in 1,597 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Degrees and employees

More specifically, the reading topic is "compound index" - which you want to make unique. It does not have to be the PK of the table for that to work.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Jonny (08-13-2019)
Old 08-12-2019, 10:58 AM   #10
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 140
Thanks: 77
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Re: Degrees and employees

Quote:
Originally Posted by The_Doc_Man View Post
More specifically, the reading topic is "compound index" - which you want to make unique. It does not have to be the PK of the table for that to work.
Never heard before.. Seems as that's exactly what I was looking for. Indexes is the solution!
Thanks
Jonny is offline   Reply With Quote
Old 08-12-2019, 11:19 AM   #11
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Degrees and employees

Quote:
Originally Posted by Jonny View Post
Never heard before.. Seems as that's exactly what I was looking for. Indexes is the solution!
Thanks
Hi Jonny. Glad to hear you're all sorted out. Good luck with your project.

__________________
Just my 2 cents...

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.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Jonny (08-13-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
6 Degrees of Separation TV/Film - Sort of Minkey The Watercooler 3 03-08-2012 11:30 AM
rotate text 180 degrees sadie Reports 3 03-25-2007 11:26 PM
Active Employees/Inactive Employees desireemm General 2 01-14-2004 11:49 AM
data at 45 degrees in Report??? mahmudich Reports 3 02-10-2003 05:07 PM
Print text 180 degrees cliff7376 Reports 2 12-12-2002 01:27 PM




All times are GMT -8. The time now is 05:10 PM.


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

Featured Forum post


Sponsored Links


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