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

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:

Yes it is a query and it runs, so not sure how I could determine if it is broken??
My next thought was could it be the type of field that is causing the issue (text vs date?). so I added modify timestamp field (date field) to see if that would allow it to run.
I think I modified it correctly to do that, but it shuts down access with this one too.
here it is:

SELECT Chart1_Filter_date.person_id, Chart1_Filter_date.person_nbr, Chart1_Filter_date.insurance1, Chart1_Filter_date.insurance2, Chart1_Filter_date.PCP_DBP, Chart1_Filter_date.modify_timestamp
FROM Chart1_Filter_date
WHERE (((Chart1_Filter_date.modify_timestamp)=(SELECT Max(modify_timestamp) FROM Chart1_Filter_date As C2 WHERE C2.person_id=(Chart1_Filter_date.person_id))));

(i wrote this query in stages to see if I could get it to run if some but not all was written, I did not put in the C2 where C2 etc into the query and it ran with one result row (I know it was incorrect but just trying to trouble shoot where the query might be having the issue) I do not understand the C2 stuff so that is keeping me from trouble shooting more.
Appreciate the help
Patty

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

Patty-

I should have asked earlier, but is Chart1_Filter_date a query? If so, maybe that's where it is broken.

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 23, 2015, at 12:24 AM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I just did. They will be upgrading servers this weekend, maybe it is server related. It still pulls up error message and closes the database. I will see what Tuesday brings. Unfortunately I will not have the ability to work on it with the servers being updated.
I will get back to you, after I try to compact again when tuesday arrives.
Have a wonderful weekend.
thank you
Patty

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

Patty-

There's nothing wrong with the query that I can see. There must be a problem in your database. Have you tried a compact and repair?

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 22, 2015, at 10:13 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

it says it has a problem, closes and creates a backup

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

Patty-

What do you mean by "crashing"?? Is Access blowing up?

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 22, 2015, at 10:00 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Ok I tried it but it is friday and I think the brain is fried,
it is crashing access (now 2013),

SELECT Chart1_Filter_date.person_id, Chart1_Filter_date.person_nbr, Chart1_Filter_date.insurance1, Chart1_Filter_date.insurance2, Chart1_Filter_date.PCP_DBP
FROM Chart1_Filter_date
WHERE (((Chart1_Filter_date.insurance1)=(SELECT MAX(insurance1) FROM Chart1_Filter_date As C2 WHERE C2.person_id = Chart1_Filter_date.person_id)));
what did I type wrong? or missing??
thanks a million
Patty

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

Patty-

Basically, yes. MAX returns the highest (latest) value. LAST returns the "last" record.

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 22, 2015, at 9:49 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote: