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

Selasa, 26 Mei 2015

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

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:

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)