Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Formula or Module to Count Change in SSN Number

Posted on 2016-11-09
14
Medium Priority
?
66 Views
Last Modified: 2016-11-09
Hi All,

I need to create a query that will count the number of dependents that are listed under an employee.  Currently, the table will list the employee's SSN in one column and either their own SSN in the second column (if they have no dependents) or their dependent's SSN.  I'm looking for a way that, in a subsequent column of the table, I can calculate how many dependent's the employee has.  Any ideas on this?  

Below is a table example and where I'd want the count to be.  

Member SSN     Dep SSN     Count
123456789        123456789    3
123456789         987654321
123456789         00000001


-Anthony
0
Comment
Question by:Anthony6890
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
14 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41880986
...Currently, the table will list the employee's SSN in one column and either their own SSN in the second column (if they have no dependents) or their dependent's SSN...


Member SSN     Dep SSN     Count
123456789        123456789    3
123456789         987654321
123456789         00000001

Your sample data above does not follow this rule.

Does Member #123456789 have two dependents (#987654321 & #00000001), or three (#123456789, #987654321, & #00000001)?
0
 
LVL 2

Expert Comment

by:Mark Wood
ID: 41881020
=COUNTIF($A1:$A20,SSN#)
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41881028
Mark:
=COUNTIF($A1:$A20,SSN#)

You have specified an MS-Excel in-cell formulae.  This is an MS-Access question.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 41881030
Are these the only two columns in this table?

Try :

SELECT [Member SSN], SUM(iif([Member SSN] = [Dep SSN], 0, 1))
FROM yourTable
GROUP BY [Member SSN]
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41881037
Fanpages, good catch.  Employee 123456789 has only two dependents, should there should be a 2 listed and not a 3.

Dale, there are about 20 columns in the table and not just those two I indicated.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41881049
Anthony, should still be able to create that query.

I'm just wondering whether all of the other info in those 20 records relates to the dependent, if the Dep SSN is different than the Member SSN?
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41881054
The other columns relates to employee information that coincides with dependent information.  The table is a result of a join of two other tables, employees and dependents... I will try the query...
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41881090
You have several problems.
1. You cannot use SSN as a primary key since it violates privacy laws.  There are also situations where SSN may not be available at the time you need to add a record.
2. You either need to use two tables or change the logic of the single table to be a self-referencing relationship.  Your schema violates second normal form.

To use a single table:
tblPersons
PersonID (autonumberPK)
SSN (indexed but not the PK, not required since not all dependents will have an SSN)
FirstName
LastName
EmployeeID (FK to PersonID - same value for employee but the dependents will have the employee's PersonID here)

To use two tables:
tblEmployees
EmployeeID (autonumber PK)
EmployeeSSN
FirstName
LastName

tblDependents
DependentID (autonumber, PK)
EmployeeID (FK to tblEmployees)
DependentSSN
LastName
FirstName
Relationship

In both cases, you would establish RI
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41881127
Hi Pat,

Thanks for your information.  Just to clarify, we have have clearance from outside counsel to use the SSN as the identifier so I don't know what privacy laws you are speaking of; however, we are in complete compliance with all HIPAA related privacy laws.  Also, there will never be an instance where an SSN will be unavailable, you cannot get onto the system without one... in any event...

I will have to look at splitting the table, if it gets to that; however, I'm holding out for a possible solution where I don't need to do that...
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41881159
I don't know what type of application you are developing but under no circumstances would SSN ever be required as a primary key.  You can always use a surrogate key.  That doesn't preclude saving SSN as data and even searching on it.  Is only the use as a primary key that causes a vulnerability.  Think about Medicare.  Medicare uses SSN as the identifier so it is printed on your identification card which must be presented when receiving certain medical services.  That puts my Medicare card in my wallet with my SSN in large print along with my drivers license which contains my name, address, and birth date - a perfect storm for identity theft.

Why would you not want to normalize the schema?  A simple count query gives you a count of dependents for an employee.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41881168
Thanks Pat, and I can completely understand where you are coming from; however, I don't have an option to not use the SSN.  I'm creating a flat file from a bunch of queries and programs that are run off our mainframe system and through a couple of access databases.  The files are being created for medical insurance enrollment, so the SSN is required regardless.  

I can attempt to normalize the schema; however, there is a lot more going on than just what I'm explaining here...
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41881190
While I concur with Pat regarding issues with your table structure, I understand that we are not always at liberty to make changes which normalize the data properly.

Have you tried the query I suggested?
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41881196
Dale, I need to adjust some queries that result in the table that I'm trying to work with before I can try your option.  It's just taking some more time...
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41881210
Dale your idea works, I'll just making a separate pass to get the total amount of depends and then append the results to the main table.

Thanks again.

-Anthony
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question