ACCESS FEED: Re: [MS_AccessPros] Weird problem with Query- different results for same field

WOW that was great, returned in seconds

I like the formatting of the first query, I can use that with other queries I need to develop

thank you so much, live savor as usual!!! Have a wonderful weekend

really appreciate you help, you are so gracious with your time and expertise. I just need to get more done in your book to expand my knowledge, there just are not enough hours in the day.

THANK YOU

Patty

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

Patty-

I was afraid of that. The problem is the query engine has to run the subquery (the C2 stuff) once for each row returned by the main query. If you have a lot of rows, and especially if there's no index on modify_timestamp or person_id, it can take a long time.

Let's try a different method:

First, build a query like this:

qryMaxChart

SELECT dbo_wo_chart1_.*

FROM dbo_wo_chart1_ INNER JOIN

(SELECT person_id, MAX(modify_timestamp) As TimeMatch

FROM dbo_wo_chart1_ As C2

WHERE C2.PCP_DBP Is Not Null

AND C2.exclude_from_reporting < 1

GROUP BY C2.person_id) As MaxRecord

ON dbo_wo_chart1_.person_id = MaxRecord.person_id

AND dbo_wo_chart1_.modify_timestamp = MaxRecord.TimeMatch;

Now try this modified final result query:

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, qryMaxChart.insurance1, qryMaxChart.insurance2, qryMaxChart.PCP_DBP, qryMaxChart.modify_timestamp, qryMaxChart.asthma_persist_ind, qryMaxChart.enc_id
FROM (dbo_person RIGHT JOIN qryMaxChart ON dbo_person.person_id = qryMaxChart.person_id) LEFT JOIN Pt_Status ON qryMaxChart.person_id = Pt_Status.person_id
WHERE (((qryMaxChart.PCP_DBP) Is Not Null) AND ((qryMaxChart.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null)) AND ((qryMaxChart.modify_timestamp)>#4/1/2013 0:01:01#)

What I have done is use a more efficient INNER JOIN to extract the chart records with the latest time stamp for each person, THEN put that result in your original query.

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 May 28, 2015, at 11:20 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John
I tried to run it and it just stalled like it was running but never returned anything,
I pasted it here for you.
SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null)) AND ((dbo_wo_chart1_.modify_timestamp)>#4/1/2013 0:01:01#) AND dbo_wo_chart1_.modify_timestamp = (SELECT MAX(modify_timestamp) FROM dbo_wo_chart1_ As C2 WHERE C2.person_id = dbo_wo_chart1_.person_id AND C2.PCP_DBP Is Not Null AND C2.exclude_from_reporting < 1)
ORDER BY dbo_wo_chart1_.PCP_DBP;

I have tried to see if I can find something that might have a spelling issue, or space or ) but nothing I seem to try makes it finish the query run
sorry, thought we had this one
I just am not sure what else to try to change, I am getting a better grasp on the C2 feature so that is a plus on this one :)
Patty
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

There's an error in my SQL. Try this:

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null))
AND ((dbo_wo_chart1_.modify_timestamp)>#4/1/2013 0:1:1#))
AND dbo_wo_chart1_.modify_timestamp = (SELECT MAX(modify_timestamp) FROM dbo_wo_chart1_ As C2 WHERE C2.person_id = dbo_wo_chart1_.person_id AND C2.PCB_DBP Is Not Null AND C2.exclude_from_reporting < 1)
ORDER BY dbo_wo_chart1_.PCP_DBP;

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 May 28, 2015, at 8:54 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi John
I have a problem it is asking for parameter information for: Modify timestamp , at first I thought maybe the C2.person_id=dbo_wo_chart1_modify_timestamp was the issue but changing that to person_id (although I think that needs to change unless I do not understand the C2 still) did not stop the parameter popup

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.modify_timestamp)>#4/1/2013 0:1:1# And (dbo_wo_chart1_.modify_timestamp)=(SELECT MAX(modify_timestamp) FROM dbo_wo_chart1_ As C2 WHERE C2.person_id = dbo_wo_chart1_modify_timestamp AND C2.PCP_DBP Is Not Null AND C2.exclude_from_reporting < 1)) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null))
ORDER BY dbo_wo_chart1_.PCP_DBP;

I have tried to see what I could fix but it is beyond my skills,
thank you so much
Patty

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

thank you so much John,
I didn't make the table but I am sure the person who did was thinking it would have something after the _, It is actually the data from a table in an electronic health record that shows the clinical staff the patient info, insurance and primary care provider so that the information could be verified before moving on in the record. that way they are entering on the correct patient (electronic health records are not well designed to say the least).
I will run the queries in a bit, multitasking right now, very much appreciate your help
Patty

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

Patty-

OK, let's start by adding the date filter to your original query:

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null))
AND ((dbo_wo_chart1_.modify_timestamp)>#4/1/2013 0:1:1#))
ORDER BY dbo_wo_chart1_.PCP_DBP;

Now, let's find the last one:

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null))
AND ((dbo_wo_chart1_.modify_timestamp)>#4/1/2013 0:1:1#))
AND dbo_wo_chart1_.modify_timestamp = (SELECT MAX(modify_timestamp) FROM dbo_wo_chart1_ As C2 WHERE C2.person_id = dbo_wo_chart1_modify_timestamp AND C2.PCB_DBP Is Not Null AND C2.exclude_from_reporting < 1)
ORDER BY dbo_wo_chart1_.PCP_DBP;

By the way, who invented the funky table names? A table name ending in an underscore?? (dbo_wo_chart1_) Gimme a break!

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 May 26, 2015, at 7:15 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Ok to the basics,
I have 3 tables, the chart 1, person and status (status will identify those that are not active patients)
so pulled in the patients with data on chart 1 and linked it up to find out who they were (person) and that they were active pts

so ultimately I want the patients on chart1, that are active and what the last information for insurance 1 and 2 and PCP_DBP (excluding those pts without information in PCP_DBP)

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null))
ORDER BY dbo_wo_chart1_.PCP_DBP;

I have been trying to do it one step at a time since I am not confident in my query skills. I thought if I did it one step at a time it was less likely that I pulled in the wrong data.

Patty

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

Patty-

First, LAST won't work - it returns the last physically stored record, which in many cases will not be the record with the highest value, particularly if there have ever been any record deletions. It's not reliable.

Is Chart_1_filter also a query? If so, I need to see the SQL for that. Let's get down to brass tacks.

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 May 26, 2015, at 6:35 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I was wondering if this would also give me the last time the information was entered on a person?

SELECT Chart1_Filter_date.person_id, Last(Chart1_Filter_date.modify_timestamp) AS LastOfmodify_timestamp, Last(Chart1_Filter_date.person_nbr) AS LastOfperson_nbr, Last(Chart1_Filter_date.insurance1) AS LastOfinsurance1, Last(Chart1_Filter_date.insurance2) AS LastOfinsurance2, Last(Chart1_Filter_date.PCP_DBP) AS LastOfPCP_DBP
FROM Chart1_Filter_date
GROUP BY Chart1_Filter_date.person_id
ORDER BY Last(Chart1_Filter_date.modify_timestamp) DESC;

or would this be flawed in design?
Patty

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

Ok that makes more since, this is the query that the max one is pulling from

Chart1_Filter_date is below:

SELECT Chart_1_filter.person_id, Chart_1_filter.person_nbr, Chart_1_filter.last_name, Chart_1_filter.first_name, Chart_1_filter.date_of_birth, Chart_1_filter.sex, Chart_1_filter.insurance1, Chart_1_filter.insurance2, Chart_1_filter.PCP_DBP, Chart_1_filter.modify_timestamp, Chart_1_filter.asthma_persist_ind, Chart_1_filter.enc_id
FROM Chart_1_filter
WHERE (((Chart_1_filter.PCP_DBP) Is Not Null) AND ((Chart_1_filter.modify_timestamp)>#4/1/2013 0:1:1#));

thankx so much,

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

Patty-

The (SELECT …. ) stuff in the WHERE clause is a subquery. That subquery is looking in the same "table" (query in this case) as you're using in the main FROM clause, and we're looking for the highest value from the person_id found in the outer query current row. The AS C2 assigns an alias name to the subquery table so that we can clearly identify that we want the person_id from the table in the subquery (C2.person_id) matches the current person_id in the main query recordset (Chart1_Filter_date.person_id).

What is the SQL of Chart1_Filter_date??

There's something in that query that is causing the problem.

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 May 26, 2015, at 5:46 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote: