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...
rleepac
Last Activity: Nov 27 '09, 04:34 AM
Joined: Nov 2 '09
Location: Northern California
-
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!
...Code:SELECT M_Employees.empExamMonth, M_Employees.empType, M_Employees.empAgency, M_Notes.NoteID, M_Employees.empOrg,
Leave a comment:
-
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...Leave a comment:
-
Query problem
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... -
rleepac replied to Don't know how to accomplish this...show reference test data to compare current testin AccessOk. I'm going to set this problem aside for now and work on another issue I'm having. FWIW I think my data structure is normalised - I tried following all the rules, but I'll look at that and see if I've got a problem there.
Thank you for all your time and help!Leave a comment:
-
rleepac replied to Don't know how to accomplish this...show reference test data to compare current testin AccessOk - that got me MUCH closer but something still isn't quite right.
I added the M_TestsAudio table 3 times. One for the current (with the AudioReferenceL and the AudioReferenceR filtered to "No", one for the left baseline (with the AudioReferenceL filtered to "Yes") and one for the right baseline (with the AudioReferenceR filtered to "Yes").
Then I went into my form (subform) and fixed all...Leave a comment:
-
rleepac replied to Don't know how to accomplish this...show reference test data to compare current testin AccessSorry. I guess I fell way short of the line where I give you enough info!
Let me see if I can explain it better.
I have to store and track audiogram test results. There are 6 frequencies for each ear and it is typically tested annually. The annual results are compared to a baseline. The baseline is usually their first audiogram but occasionally we will revise the baseline on one or both ears if there has been a significant...Leave a comment:
-
rleepac started a topic Don't know how to accomplish this...show reference test data to compare current testin AccessDon't know how to accomplish this...show reference test data to compare current test
My db has a table called M_Employees with the employee demographics. Then I have a table called M_TestsAudio with audiogram results. These two tables are linked by the employee's SSN.
Now for my problem. Each employee has multiple audiograms. However, one audiogram for the left ear and one for the right ear are used as a reference (or baseline) and it could be a different date for the left and right ear. There are some complex... -
Maybe I should start a new thread? I'm wondering if my table set up is part of the problem.
I have a table for audiograms and a table for baseline audiograms. I can't for the life of me figure out why I did it that way. But I should probably just have one table for audiograms and a field that designates if it is a baseline or annual. I'll try messing with that a little and if I can't figure it out then I'll make a new post.
...Leave a comment:
-
Actually the AudioAgeL and BRAge are both calculated fields using the date of the test and a datediff calculation. So I guess it is a number field. I pulled the single quotes from the age operators and now I get a different error.
Now I get:
Run-time error '2450':
Microsoft Access can't find the form 'F_TestsAudioBa seline' referred to in a macro expression or Visual Basic code.
The BRAge data is pulled...Leave a comment:
-
It's a text field. I tried putting single quotes where you suggested but I got an error that said "expected an expression"Leave a comment:
-
I read the link and I bookmarked it. It's a little confusing but if I read it a few dozen times I'm sure it will start to sink in... Thank you!Leave a comment:
-
Ok. Here is what I have now...
...Code:Private Sub AudioR6000_AfterUpdate() 'Get values for current test from L_AgeCorrection table If Me.AudioAgeL > 60 Then Me.Adj2000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=2000") Me.Adj3000CR = DLookup("[AgeValue]",Leave a comment:
-
Ok. Fixed all that and still same error. I'd love to see the article if someone would post it that would be great!Leave a comment:
-
Yup. I fixed all instances of it and I get the same error.Leave a comment:
-
Unfortunately that didn't do it...Leave a comment:
-
problem with DLookup code. Getting error that I can't figure out
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue
This is a table used to assign an Age Correction value to hearing test results - since some degree of hearing loss naturally occurs with aging - OSHA lets us calculate that in before determining if the employee has an actual "significan t" hearing loss.... -
Thank you that is much "cleaner" logic.
I figured out my problem...I was using Police Officer where I should have been using the ID number from the table that is was using as a row source.
Problem solved!
Thanks again...Leave a comment:
-
Problem with If/Then/Else code
I have a form with a tabbed control on it. I am trying to set certain criteria for some of the tabs to show only when info from other fields meet criteria.
Specifically, I have two fields. Both are combo boxes (using tables as the row source).
What I want is that when empType combo box is set to Prehire (8 in the table) AND the empPosition combo box is set to Police Officer then the Police_MSK tab is visible. But... -
Got it working with a tabbed control using a subform in that tab. I decided on a tabbed control because I have several other tables that I want to use the same way.
Thank you!Leave a comment:
No activity results to display
Leave a comment: