Re: [MS_AccessPros] Last test result query

 

Ah, so you want to ignore records where A1C_result is less than or equal to 1?  Do 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 AND T2.A1C_result > 1);

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 6:58 PM, pattykf@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

it returned people with no A1c results not duplicates as far as i could tell

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

Patty-

That should return the "latest" record based on timestamp for each person.  It will return more than one record per person only if there are duplicate timestamp values.

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 6:32 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I added this to the end and it got rid of a bunch but it is the wrong count of duplicated patients

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))) and (dbo_wo_A1C_.A1C_result >1));

Patty

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

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

 

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (7)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___