X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Health/dental Enrollment Db - Employees + Dependents, Access 2016    
post Aug 5 2019, 11:35 AM

Posts: 5
Joined: 17-July 19

I want to recreate my database with the ability to identify/pull/list dependent(s) of each employee. Each plan year our insurers may change, each insurer wants to see info differently so I create a table for each insurance plan year. Over all of the plan years, I listed dependents within the employee's record (single line - dep #1 name, ssn, dob, dep #2 name, ssn, dob, etc) which does not allow me to pull queries/reports easily.

My question is whether to add dependents within the same table giving them a relationship (Spouse/Child) dependent and employees (Employee), but I'm not clear on how to tie the dependent to the employee from within the same table or even from a separate table. I need to be able to enter their SSN/DOB/Gender plus the possibility of different last name for child or spouse (e.g., from a previous marriage, or kept maiden name) as well as a different address.

Currently, my employee table uses their SSN as the primary key.

I generally have approximately 100 employees and 72 dependents. I have to send in a census to the various insurance companies. Some want dependent SSN, some don't, different order of fields, additional field specific to that insurer, etc. I add the various fields the individual companies want, then run a query, then export to excel to be uploaded into the insurer's system.

Any guidance will be greatly appreciated. Thanks in advance.
Go to the top of the page
post Aug 5 2019, 12:23 PM

UA Admin
Posts: 35,873
Joined: 20-June 02
From: Newcastle, WA

Hi, this is actually a fairly typical kind of table design. You now have what is often referred to as a "spreadsheet" style table, with multiple columns for the same thing (DependentX). That's what Excel spreadsheets allow. It's not how a relational database application works.

Here's an excellent set of blog posts on the Problem of Repeating Columns. It'll give you good background.

In your database you need a table of People. Employees are people who fill a specific role for YOUR organization. Dependents are also people who fill a different, but still specific, role for YOUR organization. All of them need a record in the People table.

Now, for the roles. You can handle this in more than one way, and others will probably offer different suggestions.

I would have a field in the People table in which you enter a Foreign Key for each person who is a dependent. That Foreign Key is the Primary Key of the employee. In other words, Employee One has two dependents. Each of those two dependents has "One" as the Foreign Key value in this field, for which the only name I can think of at the moment would be "DependentOf"

Please do NOT use SSN as a primary key. Please do not even store it unless your organization's business rules require it. We simply have to keep privacy in front of us at all times these days. Using SSN like this is not desirable in that environment.

Others, I'm sure, will have additional input. Weigh all the suggestions and good luck.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Aug 5 2019, 12:40 PM

Posts: 161
Joined: 28-March 18
From: Virginia

1. Get rid of SSN unless you are encrypting all of your data. This is an invitation for hacking, use the EmployeeID.

Although I like George's idea of using a heirarchical dataset, that will not handle the case were two of your employees are married to each other, in which case you would need two columns for dependant of.

2. I would probable create two additional tables (tblDependants) and tblEmployeeDependants

In tblDependents you would store DependentID, DepFirstName, DepLastName, DepDOB, and any other fields you need for the dependents

Then you would assign dependents to employees in tblEmployeeDependants (EmpDepID, EmpID, DependentID)


Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
post Aug 5 2019, 01:33 PM

Posts: 1,003
Joined: 12-November 03
From: Iowa Lot

Since there are different ways of defining a "dependent" (e.g., for insurance, for taxes, for family relationships) and this is for insurance, I would have something similar to what's been suggested, but like:

tblPersons (as George described)

InsuredPK autonumber
PersonFK (foreign key to tblPersons)

OtherInsuredPK autonumber
PrimaryFK (foreign key to InsuredPK, identifies the primary insured)
RelToPrimary (e.g., spouse, partner, child, other dependent)

add fields to sufficiently describe the table entries.

Say a married couple both work for the company. Perhaps both will have policies (although one or both might decline?).
then each would be a primary insured under their employee number/company policy/group number. Would they both pay the extra to add dependents and include the spouse?
I suppose it's possible, but would be the exception. Then your tblOtherInsured could have duplicate entries, and a query listing all dependents might need to be a Distinct query, depending on its purpose. Should still work.

All very similar to what's been suggested, maybe with a little more detail.
This post has been edited by kfield7: Aug 5 2019, 01:42 PM
Go to the top of the page
post Aug 5 2019, 02:05 PM

Posts: 5
Joined: 17-July 19

Thanks all for your quick replies. All insurance companies require at least the employee's SSN. Our current employee ID is what we call our Badge No which is flawed as well - First two letters of last name + last 4 SSN (which I discussed in someone else's post - there can be more than one person with same first two letters + last 4 SSN).

When I initially started keeping track, I knew the repeating columns was NOT the correct way but I kept running into issues any other way I tried. Finally decided to reach out to this group. Initially, all I had to provide was the number of lives covered for each employee. As time has passed, insurers have wanted much more information, including documentation. (I'm reading the referenced articles, thanks)

I imagine I could assign an auto-number PK but will still need SSNs and will need them to be indexed as well (no duplicates). As we are a construction company, we have had the same employee terminated and rehired at least once in a plan year (Sep-Aug). Or SHOULD I allow duplicates in that case? My queries usually exclude "terminated" and "new hire waiting" in my status field.

This post has been edited by SassyTX: Aug 5 2019, 02:07 PM
Go to the top of the page
post Aug 5 2019, 02:13 PM

Posts: 1,010
Joined: 26-January 14
From: London, UK

I think it has to make sense to make SSN unique - i.e. enforce it as a key for your employees. As an employer you are permitted and required to keep that information and excluding it would be a bad idea if doing so meant you couldn't guarantee employee records weren't being duplicated.

However, data security and privacy is important and required by law in most cases. Make sure you are aware of the laws and regulations that apply. My advice would be that employee personal data should not be kept in an Access database (ACCDB) at all.

This post has been edited by nvogel: Aug 5 2019, 02:13 PM
Go to the top of the page
post Aug 5 2019, 02:28 PM

Posts: 5
Joined: 17-July 19

We do currently have a married couple that has dependents, however, one spouse declines coverage and the other lists the spouse and kids as their dependents.

We also currently have an employee who is a dependent of another employee (father and son). He had two sons working but one just aged out and had to get his own policy. We've had many of these since ACA covers kids until 26. Our company is a family owned private company in business over 100 years. We have lots of parents and children, spouses, in-laws, cousins, etc. all working for us. We've had son-in-laws retire before the father-in-law. We are blessed to have such loyal employees. Our average employee has been with us 28 years. I've only been here 25 so I'm still a newbie smile.gif
Go to the top of the page
post Aug 5 2019, 02:52 PM

Posts: 5
Joined: 17-July 19

I agree this should not be in Access but part of our payroll software. Unfortunately, our system is not flexible enough at this time. However, management is currently researching different systems and the insurance tracking will hopefully be handled in that. Until then, this is what I have to use.

With keeping the SSN unique, would you suggest keeping it as primary key or making it an indexed field?
Go to the top of the page
post Aug 5 2019, 03:23 PM

Posts: 1,010
Joined: 26-January 14
From: London, UK

It's fine to make SSN a primary key if keeping it unique is important to you. There might be reasons not to use it as a foreign key but that's a different matter. Designating another primary key and making SSN a unique index seems pointless - as a primary key it is indexed anyway so I suggest you leave it the way it is.
This post has been edited by nvogel: Aug 5 2019, 03:35 PM
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    17th October 2019 - 07:54 AM