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
(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, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(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, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(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, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(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, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL 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: pattykf@cox.net
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar