Help with database (1 Viewer)

Locco

New member
Local time
Today, 11:07
Joined
Apr 14, 2019
Messages
6
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!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:07
Joined
Oct 29, 2018
Messages
21,358
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.
 

June7

AWF VIP
Local time
Today, 08:07
Joined
Mar 9, 2014
Messages
5,423
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.
 

Cronk

Registered User.
Local time
Tomorrow, 03:07
Joined
Jul 4, 2013
Messages
2,770
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.
 

Locco

New member
Local time
Today, 11:07
Joined
Apr 14, 2019
Messages
6
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

New member
Local time
Today, 11:07
Joined
Apr 14, 2019
Messages
6
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.
 

June7

AWF VIP
Local time
Today, 08:07
Joined
Mar 9, 2014
Messages
5,423
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.
 

Locco

New member
Local time
Today, 11:07
Joined
Apr 14, 2019
Messages
6
Copy of db file is attached. I was unable to zip it.
 

Attachments

  • testdb.accdb
    1.7 MB · Views: 94

June7

AWF VIP
Local time
Today, 08:07
Joined
Mar 9, 2014
Messages
5,423
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.
 

Locco

New member
Local time
Today, 11:07
Joined
Apr 14, 2019
Messages
6
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.
 

June7

AWF VIP
Local time
Today, 08:07
Joined
Mar 9, 2014
Messages
5,423
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.
 

Micron

AWF VIP
Local time
Today, 12:07
Joined
Oct 20, 2018
Messages
3,476
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.
 

Locco

New member
Local time
Today, 11:07
Joined
Apr 14, 2019
Messages
6
Hi

I tend to use Autonumber in every table.

The attached is my preferred method linking.

View 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!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:07
Joined
Jul 9, 2003
Messages
16,244
Hi



I tend to use Autonumber in every table.

I think some people would argue that you don't need an autonumber in something like say, a many to many table. However like you, I do tend to add an auto number field, particularly while developing, because it gives you some insight if something is going wrong.

Sent from my SM-G925F using Tapatalk
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:07
Joined
Feb 19, 2002
Messages
42,970
In a m-m table that has a child table, you do need an autonumber as PK to give you a single field PK for use in combos or listboxes but when the m-m table has no child tables, you can just use a compound PK comprised of the two FK's. For consistancy, I generally add an Autonumber as the PK even though it is redundant. The CRITICAL point is that you still MUST have a unique index on the two PKs or whatever makes the row unique. In some cases these m-m tables need even more columns for uniqueness. For example, if qualifications expire, there would be three parts to the unique ID - EmpID, JobID, ExpireID. That would allow the table to contain a new row for each expiration otherwise, you would need an additional child table if you needed to keep history of the certifications.
 

Users who are viewing this thread

Top Bottom