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

 

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@cox.net [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:

John,
just so I understand, Last does not always pull in the last line of data, but MAX does?
thank you so much
Patty

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

Patty-

The Last aggregate function doesn't do what you think.  It returns the "last" physical record, which is not necessarily the one with the highest value.  Forget the Totals query and do something like:

WHERE Chart1_Filter_date.insurance1 = (SELECT MAX(insurance1) FROM Chart1_Filter_date As C2 WHERE C2.person_id = Chart1_Filter_date.person_id)

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

I hope this makes since:
I have a query that I wrote to pull the last time the data was entered
I run the query it works fine and gives me results that appear fine (query will tell me patients and then the last information on two insurance fields and who their primary provider is)
I then use that query in another query, and at least one patient has a different result for the provider (it was a weird result so that is why I found it)
this is the original query

SELECT Chart1_Filter_date.person_id, 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;

and then this is the query I wrote to combine what the prior query had for the LastofPCP_DBP, and I am getting different results

SELECT Person_filter_New.person_id, Chart1_unique_pt_list_last.LastOfPCP_DBP, Person_filter_New.person_nbr, Person_filter_New.last_name, Person_filter_New.first_name, dbo_person.date_of_birth
FROM (Person_filter_New INNER JOIN dbo_person ON Person_filter_New.person_id = dbo_person.person_id) INNER JOIN Chart1_unique_pt_list_last ON Person_filter_New.person_id = Chart1_unique_pt_list_last.person_id
WHERE (((dbo_person.date_of_birth)<"19970330"));

 This query is basically giving me the names of all the patients over the age of 18 and who their provider
any assistance would be appreciated. I am sure it is something I wrote incorrectly but I just do not know what it is
thank you so much
Patty

__._,_.___

---

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 (6)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___