Don't know how to accomplish this...show reference test data to compare current test

[rleepac]

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 (complex to me anyway) lookups and calculations which I have pretty much figured out but the only way I've gotten it to work is to put separate fields in the M_TestsAudio table for the current results and the baseline results. I know that is not the right way to do it as there are twice as many data fields as are necessary. Plus when I go to enter a new current audiogram in my form it brings up new blank fields for my reference audiogram. I can't figure out how to keep the reference audiogram on the form even when I add a new current audiogram.

So how do I indicate that a particular audiogram is to be used as the reference? And then show (and keep) that reference audiogram on the form with the current audiogram?

I've thought about putting the baseline data in a separate table but that also seems like extra data fields that are unnecessary.

Any help would be appreciated here...

Thanks in advance,
Bekah

[NeoPa]

I was reading through waiting for the bit that explains what your problem is (relative to anything I already know - rather than looping round referencing items I know nothing of). Unfortunately it never seemed to arrive.

Can you explain this in terms that don't depend on already on understanding what you're trying to say.

[rleepac]

Sorry. 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 shift in their hearing.

So - I have a table for the audiogram results: M_TestsAudio with the following fields:
AudioID - PK, autonumber field
AudioSSN
AudioDateL
AudioTypeL - combobox rowsource "B";"A";"F1";"F 2";"RB"
AudioL500 - number
AudioL1000 - number
AudioL2000 - number
AudioL3000 - number
AudioL4000 - number
AudioL6000 - number
AudioLReference - yes/no
and the same fields for the right ear.

This table is linked to the table with the employee's demographics: M_Employees by the employee's SSN.

I have a main form: F_Demographics with a tabbed control. One of the tabs is for entering audiograms.

On that tab I need to enter the new audiogram results and show the baseline audiogram BUT have the baseline test stay constant for each new current audiogram entered until I mark a different audiogram as the new reference.

So I guess my problem is that I don't know the right/best way to set it up. I have tried a subform on the tab but then when I go to enter a new current audiogram the reference audiogram also moves to a "new record". I have tried 2 subforms - one for the baseline (using a select query on the AudioReferenceL or AudioReferenceR field) and one for the current.

This seems to be the best solution so far except that some of the calculations that I need to do use data from unbound fields on both forms. Data that I know shouldn't be stored in a table - like the employee's age and a value from a DLookup.

For example I need to take the employee's age when they did their baseline test (got that with a calculation no problem) and do a DLookup to obtain an age adjustment value (again - got that no problem). Then both the age and the age adjustment value are used by the other subform for even more calculations. But then when I try to use those calculations in the other subform I get a message saying that Access cannot locate the form SF_TestsAudioRe f.

But it's right there on the tab with the other form SF_TestsAudio. ??? I don't understand what I'm doing wrong.

I hope this is enough info to help explain my problem. I'm leaving work for the day but will be checking back in tomorrow morning. If you need more info about the fields on the forms let me know.

Thank you

[NeoPa]

It wasn't so much about volume of info, as clarity of it. Luckily enough (No accident - You did a good job), your second post makes it clearer even with more info included (it's actually harder to understand larger posts full of information generally).

If I understand the problem correctly then, you actually need (Forget about the Right Ear for the moment. It will need to be treated in the same way, but separately.) to include the table twice in your FROM clause (of the Record Source of the form). If working in the Query by Example window (Design View) this means adding the table twice into the upper part. One of those tables will return values only for the baseline record (and needs to be filtered accordingly) and the other will return the data you already had and need to work on.

Does this make sense?

[rleepac]

Ok - 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 the field sources to match what I needed them to.

I have the subform linking to the master form using the employee's SSN from the employees table and the employee's SSN from the original table that I have in my query.

I now have two problems:
1)The reference audiogram for employee x is showing up for all the employee's reference audiograms. Of course it's currently the only audiogram I have marked as a reference but it's supposed to be filtering on the employee's SSN from the main form. Do I need to add the employee's table to my query design window too?

2) When I go to add a new current audiogram it clears all the data for the reference but I want the reference audiogram data to stay when I enter a new current test.

What did I forget?

[NeoPa]

1. I guess you either need to link this table (LEFT JOIN probably) to an existing one in your query that already selects only records matching the Employee SSN, or filter this table too against your required Employee SSN. I can't tell much more from here.
2. I'm not sure about this one. Normally I'd say post the SQL, but I know that even if you do it will take a lot of thinking about to see what you're even trying to do. ATM I feel I'm seeing through a glass darly into a relatively complicated and non-standard (not normalised) data structure.
   
   Give it a try. I'll have a quick look, but don't hold your breath. It may be beyond me to figure it out in the sort of time available (I try not to spend hours on a single question ever, as that leaves me unavailable for others' needs).

[rleepac]

Ok. 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!

[NeoPa]

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.

The only point that is non-normalised to my mind, is the storage of left and right data in the same record. I would expect to see a field in the record indicating which it is and separate records for each.

I can't claim to follow everything well enough for the rest, and this seems very minor as far as issues go, so I can understand why you feel it is probably normalised.

Good luck anyway :)