Can't get correct data from select query grouping fields

[dez5000]

I have a query that is pulling a list of patients seen within a certain time frame at certain locations and it also shows whether they had a certain test done. I want to report by location the list of patients seen and whether the test was done or not.

So in my query I'm grouping by PatientID and PatientName, for my criteria I have a couple of where statements for LocationID and Date. To show if the test was done I have a field Max([test from data].[Value]) AS Result that another field looks at and gives a a value of 0 or 1 so I can sort the report off of IIf([Result] Is Null,0,1) AS TestSort. Last I have the name of the facility as Last(Facilities.Name) AS Facility

Now here's my problem, if a patient was seen at three different locations (say location 10, 11 & 12) but had the test done at location 11 then the result of the query lists location 12 for the facility. If I change the facility name to First(Facilities.Name) AS Facility then I get location 10 in the result.

I can't find the function that applies to the facility so that the result I get comes from the location the test was done at. I've tried the built in functions from the query drop down list on the total row like Max, Min, First, Last but am thinking I need an expression to get what I need.

Can someone help me out?

[Stewart Ross]

Hi dez5000. We'd need to know a bit more about your table design to give a definitive answer - and in particular, how and where do you store the facility code for the facility at which the test was done? First and Last won't help you here, as these functions cannot 'know' what the actual location of the test was. This value has to be stored somewhere for it to be used in your query, and if you could point out where then we can help you with the SQL you need.

To move things along, could you post the metadata for your tables (the relevant fields and their types, along with any related tables), and the actual SQL for your query so far.

I suspect, though, that the problem lies with non-optimal table design and normalisation. I would have expected there to be a facility location field in your table reflecting a normalised relationship between patient test and facility, that could be joined back to your facility table and then grouped within your summary query.

-Stewart

[dez5000]

I think I solved my problem. Our medical software uses a MySQL database with tons of tables for the different data it's capturing and since I don't need every table I'm extracting the data I need and in MS Access I created my own single source table to base my query on so that all the data is now stored in a single table called encounters.

Here's a little more information on how I have it working now.

My source table is called Encounters and the table contains an numeric EncounterID field, a date field, numeric PatientID field, numeric FacilityID field, and text fields for patient first and last name (PtFirst & PtLast), a text field for the Location, and a numeric field for the test I'm looking for. I have another query that updates this test field to a 1 if the test was done during that encounter and leaves it blank if it wasn't done.

So my query is:

SELECT [Encounters].PatientID, [Encounters].PtFirst, [Encounters].PtLast, First([Encounters].Location), Sum([Encounters].[test]) as test
FROM [Encounters]
WHERE (([Encounters].FacilityID) Between 10 And 12) AND (([Encounters].date) Between [Forms]![Input Form]![txtStartDate] And [Forms]![Input Form]![txtEndDate])
GROUP BY [Encounters].PatientID

In my report I have a group header for Location then I sort by test so that the patients without the test done show first and it looks like the patients are being sorted correctly by location using this new source table.