Query problem

[rleepac]

I have 2 tables in my query. M_Employees and M_Notes

M_Employees
empSSN - number, pk
empLastName - text
etc.

M_Notes
notesID - autonumber, pk
notesSSN - number, linked to SSN in M_Employees table
notesNote - text
etc.

I put both of these tables in a query because I need to make a report with data from both.

My problem: My query will only return results if there is a note in the M_Notes table. I've tried changing it to a LEFT join, RIGHT join, and back to INNER join but I still don't get any employee data (even if they meet the criteria I set) unless there is an associated note in the notes table.

Here is the SQL for my query:

SELECT M_Employees.empExamMonth, M_Employees.empType, M_Employees.empAgency, M_Employees.empOrg, L_Org.OrgPOC, M_Employees.empSSN, M_Employees.empNameManpower, M_Employees.empPayPlan, M_Employees.empSeries, M_Employees.empPosition, M_Employees.empTOD, M_Employees.empHours, M_Employees.empSupervisor, M_Notes.NoteType, M_Notes.NoteDetail, M_Notes.NoteStatus
FROM (L_Org INNER JOIN M_Employees ON L_Org.OrgID = M_Employees.empOrg) INNER JOIN M_Notes ON M_Employees.empSSN = M_Notes.NoteSSN
WHERE (((M_Employees.empExamMonth) Like [Enter Month]) AND ((M_Employees.empType)=6) AND ((M_Employees.empAgency)=1) AND ((M_Notes.NoteType)=2) AND ((M_Notes.NoteStatus)=Yes)) OR (((M_Employees.empType)=7) AND ((M_Employees.empAgency)=2));

Any idea how to fix this so I get the employee data even if they don't have any notes in the M_Notes table?

Thanks,
Bekah

[Frinavale]

I could be wrong but I think this problem has to do with your Where statement you have:

AND ((M_Notes.NoteType)=2) AND ((M_Notes.NoteStatus)=Yes

If the NoteStatus is not Yes or NoteType is not 2, then the record won't meet the criteria.

-Frinny

[rleepac]

Frinny, I could see how you would come to that conclusion but I don't think that's it.

NoteStatus just indicates if it is a current note or not. So NoteStatus = Yes just pulls the current notes.

NoteType just indicates what category the note falls in. So NoteType = 2 means that I only want notes that fall into category 2.

I thought those criteria might be the problem too so I tried running the query without those criteria and I still only got employee records that had a note (of any type or status) attached.

Maybe I need to put criteria in the M_Notes.NoteID like = Null or NotNull?
I don't know the right way to put that in there but I'll play with that option next.

[Frinavale]

(Wiki for:SQL Join)

When using an inner join, if there is no matching data to join the records on then these records (that have no match) will not be included in the resulting table.

An outer join does not require each record in the two joined tables to have a matching record....but you said that this doesn't work?

Did you try LEFT OUTTER JOIN?

(see wiki link because I could be wrong about LEFT)

-Frinny

[rleepac]

Got it! I was entering the criteria wrong. I had it as an AND instead of an OR.

This is the code that got me all the employees that met the criteria regardless of if they had any notes attached. But if they did have any notes they would show up if they met certain criteria.

Whew....thanks for your help!

SELECT M_Employees.empExamMonth, M_Employees.empType, M_Employees.empAgency, M_Notes.NoteID, M_Employees.empOrg, L_Org.OrgPOC, M_Employees.empSSN, M_Employees.empNameManpower, M_Employees.empPayPlan, M_Employees.empSeries, M_Employees.empPosition, M_Employees.empTOD, M_Employees.empHours, M_Employees.empSupervisor, M_Notes.NoteType, M_Notes.NoteDetail, M_Notes.NoteStatus, M_Notes.NoteDetail
FROM (L_Org INNER JOIN M_Employees ON L_Org.OrgID = M_Employees.empOrg) LEFT JOIN M_Notes ON M_Employees.empSSN = M_Notes.NoteSSN
WHERE (((M_Employees.empExamMonth) Like [Enter Month]) AND ((M_Employees.empType)=6 Or (M_Employees.empType)=9) AND ((M_Employees.empAgency)=1 Or (M_Employees.empAgency)=2) AND ((M_Notes.NoteType)=2 Or (M_Notes.NoteType) Is Null) AND ((M_Notes.NoteStatus)=Yes Or (M_Notes.NoteStatus) Is Null));

[topher23]

I was looking over your tables and realized that you might be walking a fine line here. Your tables are using the employee's SSN as an identifier, which is a security no-no, particularly in the state of California, which has the strictest SSN privacy laws in the nation.

The best practice for employee records is to use an AutoNumber field to generate a unique value for each employee, then use that value elsewhere in the database to link to the employee. If the SSN must be in the database, it should be stored once and not referenced by any other table. My preference is actually to store it in a separate table linked to the original employee record with the AutoNumber field, just to maintain a certain level of separation.

Also, any database containing social security numbers needs to be on a secure system, development copies need to be isolated from the network at large (do development on a firewalled computer and don't store development copies on the server), and production databases should be compiled to .mde or .accde.

SSN's are not something to take lightly in this era of identity theft, and if someone gets even a single SSN from your database, your company could be facing major fines and you may even lose your job.

[rleepac]

Yes, I'm well aware of the problems with using the SSN. I don't have a choice. I'm a contractor providing medical services and the agency I work for files their medical records by SSN. It would literally take a congressional action to change that. However, I do like your idea of storing them in a separate table. I'll have to look at how that will affect the way we access our data. Definitely worth taking a look at. Thanks.

That said, I am on a secured server with development copies isolated, and the production database will be compiled to accde.

Thanks for the info...definite ly don't want to end up in jail or unemployed!

[topher23]

I totally understand. I worked as a Patient Administrator in the Army for 8 years - we lived and died by the SSN.

When I had to build a deployable medical tracking system for my regiment, I spent a lot of time and several major rewrites of my system making the SSN accessible to those who needed it, yet at the same time hard to access for someone without a need to know. After a lot of study, I ended up with what I outlined previously.

The nice part is, you can still have forms and reports display all or part of the SSN by using queries, but by not using it directly as an identifier in the tables you restrict access by making it only available to people with permissions to view those forms and reports.

[NeoPa]

It seems this was a JOIN problem AND a WHERE criteria problem.

Frinny got both so all good now.