ACCESS FEED: Re: [MS_AccessPros] problem with query

Selasa, 19 Mei 2015

Re: [MS_AccessPros] problem with query

Thank you so much John it worked perfectly using the first one. Thanks so much, learned another excellent query now
Patty

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

Patty-

Does this recordset need to be updatable? If not, then this will be faster:

SELECT dbo_wo_A1C_.person_id, dbo_wo_A1C_.A1C_result, dbo_wo_A1C_.create_timestamp, dbo_wo_A1C_.enc_id
FROM dbo_wo_A1C_ INNER JOIN

(SELECT T2.person_id, MAX(create_timestamp) As MaxTime

FROM dbo_wo_A1C_ AS T2

WHERE T2.A1C_result > 1 AND T2.create_timestamp >= #3/30/2014#

GROUP BY person_id) As RSelect

ON dbo_wo_A1C_.person_id = RSelect.person_id

AND dbo_wo_A1C_.create_timestamp = RSelect.MaxTime
WHERE dbo_wo_A1C_.create_timestamp >= #3/30/2014#

AND dbo_wo_A1C_.A1C_result > 1;

Note that if you're limiting the results in SELECT MAX to A1C_result > 1, then you should do the same in the main table.

Either query will run faster if there's an index on create_timestamp.

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

I have a query that is trying to pull the last result of a test, the column with the result has blank results rows, so I need to exclude anything that is blank. this is the query as it stands right now, it tends to lag for long periods of time, so there is something wrong with it.

SELECT dbo_wo_A1C_.person_id, dbo_wo_A1C_.A1C_result, dbo_wo_A1C_.create_timestamp, dbo_wo_A1C_.enc_id
FROM dbo_wo_A1C_
WHERE (((dbo_wo_A1C_.create_timestamp)>=#3/30/2014# And (dbo_wo_A1C_.create_timestamp)=(SELECT MAX(create_timestamp) FROM dbo_wo_A1C_ AS T2 WHERE T2.person_id = dbo_wo_A1C_.person_id AND T2.A1C_result > 1)));

Patty