Question What is the best way to display multiple fields from multiple tables?

[SimonSezz]

I have a form that has to show an employee's work history details. There are many details it must display so it has a couple tabs on the form. The name of this form is "frmEmployeeWHInfo". To get to this form, the user double-clicks on an employee's name on ListBox control on a form called "frmShops". So upon double-clicking on the ListBox control, the function is run:

Private Sub lstEmployeeRoster_DblClick(Cancel As Integer)
    Dim strEmpSSN As String
    Dim strShopNum As Double
    strEmpSSN = lstEmployeeRoster.Column(1)
    strShopNum = Me.ShopNumber
    DoCmd.OpenForm "frmEmployeeWHInfo", , , "SSN = '" & strEmpSSN & "'", , , "&EmpSSN=" & strEmpSSN & "&ShopNum=" & strShopNum
End Sub

The reason I have the OpenArgs is because the Employee's Social Security number and the shop for which they work needs to be passed to the new form. The employee can work for multiple shops so that is why it is important.

Now that the double-click function is run the form "frmEmployeeWHInfo" is opened, and it runs the Form_Load function:

Private Sub Form_Load()
    Dim strEmpSSN As String
 
    strEmpSSN = ParseArgString("EmpSSN", Me.OpenArgs)
    txtEmpSSN.Caption = strEmpSSN
    txtShopNum = ParseArgString("ShopNum", Me.OpenArgs)
    txtSSN.SetFocus
    txtSSN = strEmpSSN
End Sub

the ParseArgString function is:

Public Function ParseArgString(ByVal strKeyword As String, ByVal strArgString As String) As Variant
' argstring format:
' &keyword=value&keyword=value
' arg to this function should not include the &
On Error GoTo ErrorHandler
    Dim intKeywordPos As Integer
    Dim intNextKeyWordPos
 
    If IsNull(strKeyword) Or IsNull(strArgString) Then
        ParseArgString = Null
    ElseIf Len(strKeyword) = 0 Or Len(strArgString) = 0 Then
        ParseArgString = Null
    Else
        intKeywordPos = InStr(1, strArgString, "&" & strKeyword, vbTextCompare)
        If IsNull(intKeywordPos) Or intKeywordPos = 0 Then
            ParseArgString = Null
        Else
            intNextKeyWordPos = InStr(intKeywordPos + 1, strArgString, "&", vbTextCompare)
            If intNextKeyWordPos = 0 Then
                intNextKeyWordPos = Len(strArgString) + 1
            End If
        ParseArgString = Mid(strArgString, intKeywordPos + Len(strKeyword) + 2, _
        intNextKeyWordPos - (intKeywordPos + Len(strKeyword) + 2))
        End If
    End If
ExitSub:
    Exit Function
ErrorHandler:
    On Error Resume Next
    Debug.Print "ParseArgString encountered error " & Err.Number & " " & Err.Description
    GoTo ExitSub
End Function

In that form I have a bunch of text boxes that must display relevant information such as the employee's full name, SSN, hire date, transaction dates, billing status, fees, etc. This information is stored in different tables. How do I make the form display only the information that matches the employee's SSN and Shop they work for?

The tables I have are:

tblEmployees
-SSN
-LastName
-FirstName
-MiddleInitial

tblWorkHistory
-EmpSSN
-WHShopNumber
-WHTransactionDate
-WHHireDate
-WHBillingStatus
-WHFeesDue

So I would have to have the form find and display all of this information in appropriate text boxes on one form. I tried writing a query but I cannot get it to work.

qryEmpWH:

SELECT tblWorkHistory.WHShopNumber, tblWorkHistory.WHTransactionDate
FROM tblBillingBalance, tblBillingHistory, tblWorkHistoryPlan, tblEmployees INNER JOIN tblWorkHistory ON tblEmployees.SSN = tblWorkHistory.EmpSSN
WHERE (((tblWorkHistory.WHShopNumber)=[Forms]![frmEmployeeWHInfo]![txtShopNum]));

I need the query to show only values where "tblEmployees.SSN = tblWorkHistory.EmpSSN" AND where "tblWorkHistory.WHShopNumber = [Forms]![frmEmployeeWHInfo]![txtShopNum]"

Thanks in advance!

 

[RainLover]

>>>The reason I have the OpenArgs is because the Employee's Social Security number and the shop for which they work needs to be passed to the new form. The employee can work for multiple shops so that is why it is importantThe reason I have the OpenArgs is because the Employee's Social Security number and the shop for which they work needs to be passed to the new form. The employee can work for multiple shops so that is why it is important<<<

Why do you not simply pass the Primary Key????

 

[SimonSezz]

How would I do that? Here's a picture of the table relationships if it helps...

 

[RainLover]

Yes that does help.

My opinion is simply my opinion. If you want to do things differently then that is fine by me. We all have our preferences.

I would use as the Primary key an Unique Identifer. Not a combination of fields. The easiest way to do this is to make the Primary Key AutoNumber. Then all searches such as you have mentioned would be on that Primary Key and nothing else.

I noticed in your diagram that not all of your tables are related. Seems strange to me but I only had a quick look.

Hope I have helped.

 

[SimonSezz]

The primary key in tblEmployees is SSN, this is the unique key for each employee.

 

[RainLover]

Yes but you are opening tblWorkHistory. This table has two primary keys, or more correctly a Primary Key made up of more than one field.

I am suggesting that an Autonumber would be a better choice here and also in other tables.

EmpSSN should be a Foregin Key to tblEmployees

tblShopBilling and tblShopPlanBilling seem to be very similar in design.

The more I look the more confusing your design becomes.

Suggest you search this site for articles on Normalisation

 

[RainLover]

After a further look, tblEmployees is not normalised. all those fields starting with OLD are redundant. You are duplicating fields.

City and State should be in a separate table as lookups.

 

[RainLover]

I had a look at tblWorkHistory.

Here you have a Hire Date. This is a once only entry. You also have UnionFeesPaid which is most likely to have many entries.

So this table is not going to do what you need it to do.

 

[Pat Hartman]

Using SSN as a primary key is not allowed. It is a violation of the privacy laws. SSN is an ultra-sensitive piece of data and should not be just hanging out there. In fact, it should be encrypted! Also, not all dependents would have SSN's so that is an impossible choice of keys for the dependent table.

I have also gone over to the dark side and recommend using only autonumbers as primary keys. To implement your business rules, add unique indexes comprised of the fields you are currently using as the PKs.

 

[RainLover]

Using SSN as a primary key is not allowed. It is a violation of the privacy laws. SSN is an ultra-sensitive piece of data and should not be just hanging out there. In fact, it should be encrypted! Also, not all dependents would have SSN's so that is an impossible choice of keys for the dependent table.

I have also gone over to the dark side and recommend using only autonumbers as primary keys. To implement your business rules, add unique indexes comprised of the fields you are currently using as the PKs.

Pat

That is very interesting information about privacy. Thanks for that.

The Dark Side? LOL

It is a shame people do not understand the concept of Unique Indexes instead of using composite Primary Keys.

 

[Pat Hartman]

The problem with the example table is that it is all the information a person would need to steal someone's identity. I don't know how much of a problem this is down under but it is a really big deal in the US. We also have the added issue of 20,000,000 illegal immigrants all of whom need a stolen SSN to get a job.

 

[SimonSezz]

I never heard of using the SSN as a violation of privacy laws. Are you sure about that? Even the Access 2007 book I have in front me of says you can use the SSN as the unique key. Really it's just me using this program at our office so I don't know how anyone would be able to get to the information. When we print out our billing information for our shops, everyone's SSN is listed and that's how the insurance companies want it to be.

Also I need to mention that all of these tables I'm importing from an old Access program that a previous programmer did, but he doesn't want to give the actual program, just the data so I have to re-write the entire program. That's kind of the reason I want to keep the tables the way they are for now at least because I'm going to be importing all of the old data in the future.

 

[Pat Hartman]

SSN is not a national identity number although some people treat it as such. Its only valid use is reporting income to the IRS. Therefore, your employer needs to know your SSN as does your bank, broker, and any other institution that pays you money.

I know that there are examples out there that show SSN as a primary key but I wouldn't take that as gospel. If I worked for your company, I would file a complaint with the SSA.

The following laws address the use of SSN: the Privacy Act of 1974, the Family Education Rights and Privacy Act (FERPA), Gramm-Leach-Bliley Act, Health Insurance Portability and Accountability Act (HIPAA).

 

[SimonSezz]

Well you need to start filing those complaints because there are a lot of places to complain about, companies that use your social security number for verification... cable company, insurance company, credit card, cell phone, utilities. And protect your kids too because their SSN is usually used as their student ID number.

I was intrigued to research how "illegal" this is and here is an official answer:

"Can my employer use my Social Security number as an employee identification number? Yes, in most states. However, the Social Security Administration discourages employers from displaying SSNs on documents that are viewed by other people — such as badges, parking permits, or on lists distributed to employees. Employers do, however, need each employee’s SSN to report earnings and payroll taxes."

So there is nothing wrong with the way we use our SSN system. Unless you live in New York or California where the laws are more strict.

 

[Mike375]

I think about everyone uses some sort of meaningful ID in the earlier part of the journey down Access road.

I won't bore you with different reasons why I no longer use that system but there is one reason alone not to use a meaningful ID. A meaningful ID will have to be entered, the number will have to be obtained etc. Thus there is a chance that a wrong entry can be made. Depending on the DB, changing it could be a pain and unless it is corrected it destroys the idea of the ID number being meaningful.

I can't think of a single advantage in using something like your SSN number as an ID.

The ID number is for linking puposes, identifying a record etc.

 

[gemma-the-husky]

The equivalent in the UK to your SSN is our NI No (National Insurance)

it seems to me that one problem with using the SSN as a unique key is that this may raise a couple of issues

1) what do you do if you start a new employee who can't give you his SSN/Ni No initially

2) what do you do if an employee leaves, and is then rehired. Can you reopen the old employee record - because you cant have a new one, as you would have a clash with the SSN Nos

 

[RainLover]

Simon

I think you will find as you develop more that the use of a single Primary key makes life easier.

You will find that your Code will become more and more complex until you decide to use Autonumber.

I wish you well with your project.

 

[RainLover]

Simon

I was wondering if you are using composite primary keys because you want unique values. If this is the case let me know and I will show you a better way of doing that.

Make sure you tell me what version you are using.

If you wanted to do some research look up "Unique Index"

 

[SimonSezz]

So I have read a lot of material about tables and I'm sort of getting a grasp of things. I want to do this the best way possible so I took your advice and went the Autonumber route. I have uploaded my new database "skeleton" and you can download it and take a look at it here: http://home.comcast.net/~sbingier/db/newdb.zip

(It is an Access 2007 file)

The way the data is entered is exactly how it is stored in the old database from which I am importing the data.

Does everything look as it should now?

 

[The_Doc_Man]

I'll toss this in. Using SSN as a key is a highly restricted act that, if you have a contract with the U.S. Government, will get you process-audited and security-audited very quickly. We see this problem quite frequently. There are some executive orders and some laws as quoted by Pat Hartman that require us to go through our security offices whenever we find a requirement for SSN on some paperwork or some report demanded by one of our projects. Usually results in our security folks having a stern talk with the project leaders.

It is SO severe that all government contractors have to go through a yearly exercise of reviewing privacy laws for the things they have to provide to our customers. So just be aware that if you ever take on a government contract, you will be REQUIRED to observe the terms of the Privacy Act scrupulously.

The law doesn't actually say that you can never use SSN. It just says that when you do, there has to be an overriding need for that usage AND that you must take great pains to safeguard the data containing SSN in that situation. However, the rules for determining the overriding need are themselves pretty hard to override. About the only people who CAN get away with it are in the Social Security Administration offices.

We have been told to avoid it for another reason. Not all military persons are necessarily U.S. Citizens with their own SSN yet. Even the IRS has to weasel-word the way they use SSN as a taxpayer ID number.

Bottom line: Where possible, avoid the use of SSN as a prime key. In the long run, it will be much easier.