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
(Paris, France)
On May 22, 2015, at 9:49 PM, pattykf@cox.net [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 :
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
(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 (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar