Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-13-2019, 06:18 PM   #1
Locco
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
Locco is on a distinguished road
Help with database

I am working on a database at work. It started as a small project for myself and has morphed into something larger.

I do have Access experience, although I have not done anything in Access in more than 5 years and I wasn't exactly a power user to begin with. I do believe I can create this small database though, but I also may have bitten off more than I can chew.

Basically the DB is one which lists all employees in our department and the job(s) they are qualified on.

As of now I have 2 tables:

Table one
EmployeeID
FirstName
LastName
Shift

Table two
EmployeeID
JobID
JobDescription

I've created a form to enter the information and for the most part it works with no problem.

The issue I am having is entering multiple jobs for the same person. Say for instance I am qualified on 4 jobs, I cannot enter a 2nd, 3rd, etc... due to "The changes you requested to the table were not successful because they would create duplicate values...." error.

I've searched high and low for an answer and I might have seen it and didn't understand, but most seem geared around VBA and I wanted to stay away from that for this DB if possible.

Any help you guys could offer would be much appreciated, thanks!

Locco is offline   Reply With Quote
Old 04-13-2019, 06:27 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,009
Thanks: 23
Thanked 518 Times in 507 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Help with database

Hi. Welcome to the forum. Would more than one employee have the same job qualification at the same time? If so, you probably have a many-to-many relationship, which means you'll need additional tables to properly model your business rules.
__________________
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:
Locco (04-13-2019)
Old 04-13-2019, 06:46 PM   #3
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,641
Thanks: 0
Thanked 403 Times in 400 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Help with database

3 tables minimum.

tblEmployees
EmpID_PK
FirstName
LastName
Shift

tblJobs
JobID_PK
JobDescription

tblEmployeeJobs
ID
EmpID_FK
JobID_FK

Set EmpID_FK and JobID_FK as compound index to prevent duplicate pairs.

Conventional approach for data entry would be a form/subform. Main form bound to tblEmployees and subform bound to tblEmployeeJobs with combobox to select job.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Locco (04-13-2019)
Old 04-13-2019, 06:48 PM   #4
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,075
Thanks: 3
Thanked 451 Times in 444 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Help with database

To add to the previous response, you need 3 tables
tblEmployees (essentially your table one)
tblJobs (containing JobID and JobDescription)
tblEmployeeJobs (tblEmployeeJobID, JobID and EmployeeID)

Your main form will be frmEmployees bound to tblemployees with a subform bound to a query using tblJobs and tblEmployeeJobs.
Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
Locco (04-13-2019)
Old 04-13-2019, 06:52 PM   #5
Locco
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
Locco is on a distinguished road
Re: Help with database

Thanks for the replies.

Multiple employees can be qualified on the same job and one employee could have multiple qualifications.

I will make the suggested changes and let you know if I run into any issues.

Thanks again.
Locco is offline   Reply With Quote
Old 04-13-2019, 09:11 PM   #6
Locco
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
Locco is on a distinguished road
Re: Help with database

I guess I am either not linking the relationships correctly, or something else.

I'm getting two different errors/issues.

First, in making relationships I cannot actually make any relationships between my tables. When I try I get the error "Relationship must be on the same number of fields with the same data types."

In changing some settings (which I believe changes how I need the relationships to work anyways) I can get the relationships to make, but when I enter data I get "The changes you requested to the table were not successful because they would create duplicate values...." again.

I'm thinking with my time away from Access (and not using it since Office 2007) I'm all but lost, but I do need to get this figured out.
Locco is offline   Reply With Quote
Old 04-14-2019, 03:50 AM   #7
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 330
Thanks: 21
Thanked 71 Times in 71 Posts
mike60smart is on a distinguished road
Re: Help with database

Hi

Can you upload a zipped copy of the Db?

mike60smart is offline   Reply With Quote
Old 04-14-2019, 09:01 AM   #8
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,641
Thanks: 0
Thanked 403 Times in 400 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Help with database

The relationships are rather explicitly identified in my previous post. The PK fields would be autonumber. The FK fields would be long integer number.

The ID field in tblEmployeeJobs would also be autonumber.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 04-14-2019, 04:30 PM   #9
Locco
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
Locco is on a distinguished road
Re: Help with database

Copy of db file is attached. I was unable to zip it.
Attached Files
File Type: accdb testdb.accdb (1.69 MB, 15 views)
Locco is offline   Reply With Quote
Old 04-14-2019, 06:15 PM   #10
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,641
Thanks: 0
Thanked 403 Times in 400 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Help with database

Strongly advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Use No or Num instead of #.

You are not using autonumber as primary key for EmployeeList and Jobs tables. Why is the employee identifier key called Clock#?

You have the foreign key fields in EmployeeJobs set as text type. They must be long integer number.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Locco (04-14-2019)
Old 04-14-2019, 06:57 PM   #11
Locco
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
Locco is on a distinguished road
Re: Help with database

Quote:
Originally Posted by June7 View Post
Strongly advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Use No or Num instead of #.

You are not using autonumber as primary key for EmployeeList and Jobs tables. Why is the employee identifier key called Clock#?

You have the foreign key fields in EmployeeJobs set as text type. They must be long integer number.
I'll change the unique characters to something else.

I have made multiple changes to my tables and may have deleted the wrong ones. I'll double check my key fields to make sure they are correct and make changes as necessary.

Since every employee has a unique identifier (clock#) I was using this but can use autonumber if it will work better.
Locco is offline   Reply With Quote
Old 04-14-2019, 07:16 PM   #12
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,641
Thanks: 0
Thanked 403 Times in 400 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Help with database

If you prefer to use ClockNum as key, it will work. There are arguments on both sides about this. I have done it. If these identifiers contain only digits and any begin with 0 the field must be text type.

If the primary key is autonumber then related foreign key must be number. If primary key is text then related foreign key must be text.

I just find it an odd name for an employee identifier.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 04-14-2019, 07:46 PM   #13
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 407
Thanks: 2
Thanked 82 Times in 79 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Help with database

Quote:
I just find it an odd name for an employee identifier.
Mine was 10910 for about 20 years until they did away with time clocks for everybody - including hourly paid employees. That whole thing was an enterprise unto itself for about 10,000 employees. They saved way more money not having to print and punch cards, people to drop them off and collect them, cost of printing, maintaining clocks, running them through a processor, etc etc. than they would ever lose because of a few late-comers. I still had a permanent employee number, but I can see how smaller businesses would use a clock number as an employee number.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 04-14-2019, 11:37 PM   #14
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 330
Thanks: 21
Thanked 71 Times in 71 Posts
mike60smart is on a distinguished road
Re: Help with database

Hi

I tend to use Autonumber in every table.

The attached is my preferred method linking.

testdb (1).zip
mike60smart is offline   Reply With Quote
The Following User Says Thank You to mike60smart For This Useful Post:
Locco (04-15-2019)
Old 04-15-2019, 01:31 AM   #15
Locco
Newly Registered User
 
Join Date: Apr 2019
Posts: 6
Thanks: 5
Thanked 0 Times in 0 Posts
Locco is on a distinguished road
Re: Help with database

Quote:
Originally Posted by mike60smart View Post
Hi

I tend to use Autonumber in every table.

The attached is my preferred method linking.

Attachment 74607
This is what I'm looking for, thank you very much.

I didn't get a chance to work on it as much as I hoped tonight, and it'll be Wednesday before I can get back to it.

I'm sure I'll run into some more issues as I go but I'll let you know Wednesday how I'm coming along.

Thanks to everyone for your help!

Locco 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
Database or Object read only and Type Mismatch error for a Serial Number Database surferdude800 Modules & VBA 16 06-17-2015 07:14 AM
Question MS Access 2013 Reporting database connection to SQL Azure Database failing to connect BabaTana General 9 10-08-2013 08:18 AM
Access 2013 Web Database-Trying to display an image on a report with desktop database kmcclaff Reports 0 08-05-2013 03:35 PM
desktop icon for the database moves every time I open/close the database JSH20120710 Modules & VBA 4 07-11-2013 01:24 PM
Code connect to database and query select data from database in Ms Access 2007 veasna Queries 2 11-29-2010 12:25 PM




All times are GMT -8. The time now is 10:59 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