ACCESS FEED: Re: [MS_AccessPros] Last test result query

Kamis, 05 Februari 2015

Re: [MS_AccessPros] Last test result query

No i have no people in A1c that are not in person, my error thank you for pointing that out, I need to do some reading/learning

It resulted in too many returns a bunch with no results in the the A1c result field
Patty

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

Last typically won't work because it returns the last physical record from the table - which in many cases won't be the "last" one you want. Try this:

SELECT dbo_wo_A1C_.person_id, dbo_wo_A1C_.A1_last_in_office_result, dbo_wo_A1C_.A1_last_office_result_dat, dbo_wo_A1C_.A1C_result, dbo_wo_A1C_.create_timestamp, dbo_wo_A1C_.enc_id
FROM dbo_person RIGHT JOIN dbo_wo_A1C_ ON dbo_person.person_id = dbo_wo_A1C_.person_id

WHERE dbo_wo_A1C_.create_timestamp =

(SELECT MAX(create_timestamp)

FROM dbo_wo_A1C_ AS T2

WHERE T2.person_id = dbo_wo_A1C_.person_id);

Why are you doing a RIGHT JOIN? Do you have some records in dbo_wo_A1C_ that have no matching person in dbo_person?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 5, 2015, at 5:46 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am trying to write a query that shows the last result for each patient,
the person nbr is the patient identified, the create timestamp would be the date range to identify the last test done it is a date/time field,
I have tried the last in totals but it is not returning the right amount of patients (I took the table to excel and sorted it to identify the correct result. Oh and I am looking for tests after a certain date (also date/time field)

this is the query I wrote,

SELECT dbo_wo_A1C_.person_id, dbo_wo_A1C_.A1_last_in_office_result, dbo_wo_A1C_.A1_last_office_result_dat, dbo_wo_A1C_.A1C_result, dbo_wo_A1C_.create_timestamp, dbo_wo_A1C_.enc_id, Last(dbo_person.person_nbr) AS LastOfperson_nbr
FROM dbo_person RIGHT JOIN dbo_wo_A1C_ ON dbo_person.person_id = dbo_wo_A1C_.person_id
GROUP BY dbo_wo_A1C_.person_id, dbo_wo_A1C_.A1_last_in_office_result, dbo_wo_A1C_.A1_last_office_result_dat, dbo_wo_A1C_.A1C_result, dbo_wo_A1C_.create_timestamp, dbo_wo_A1C_.enc_id
HAVING (((dbo_wo_A1C_.A1C_result)>0) AND ((dbo_wo_A1C_.create_timestamp)>#1/1/2014 0:1:0#));

Thank you for your help