Specialixed Query

[travismp]

Ok Stick it out with me.

See DB. Table named “tbl_testing”

15 total records.

I need a query that will give me:

Only the most recent “Drug” & most recent “alcohol” test for each social.

Ex: Steve Smith has 2 “Alcohol” tests and 3 “drug” tests. I only want the most recent of each. The most a social can be listed is 2 in the query, one alcohol & one drug test.

Is this possible? Thanks

TravisDownload DB

 

[Jon K]

You can do it with a series of two queries.

Run the second query in database.
.

 

[travismp]

Thanks!

That is perfect I have been trying to figure that our for 4 months. I have posted the same zipped db of about 4 different forums and no one has been able to figrue that out!

Thank you soooooo much!

Travis

 

[ByteMyzer]

You don't need two queries to do this; you can do it with one query (query with a subquery):

SELECT t1.*
FROM tbl_testing AS t1
WHERE t1.TESTDATE=(
    SELECT Max(t2.TESTDATE)
    FROM tbl_testing AS t2
    WHERE t2.[D/A TEST]= t1.[D/A TEST]
    AND t2.SS=t1.SS;
);

 

[Jon K]

Avoid subqueries whenever you can.

You don't need two queries to do this; you can do it with one query (query with a subquery):

SELECT t1.*
FROM tbl_testing AS t1
WHERE t1.TESTDATE=(
    SELECT Max(t2.TESTDATE)
    FROM tbl_testing AS t2
    WHERE t2.[D/A TEST]= t1.[D/A TEST]
    AND t2.SS=t1.SS;
);

There are usually more than one way to do a thing.

Where queries are concerned, there is no such a thing as The Fewer The Better.


As an illustration, I have increased the number of records in the table to 2,000 in the attached database and pasted ByteMyzer's SQL statement in a query. When you run my query "qryTwo" and ByteMyzer's query, you will see how much longer ByteMyzer's one-query approach will take to return the same seven records.


Subqueries are not optimised well in Access and should therefore be avoided as far as possible.
.

 

[ByteMyzer]

All right, then, try it with a query/inline-query scenario:

SELECT tbl_testing.[D/A TEST], tbl_testing.CompanyName, tbl_testing.LASTNAME, tbl_testing.FIRSTNAME, tbl_testing.SS, tbl_testing.TESTDATE, tbl_testing.TEST_RESULTS, tbl_testing.TYPE
FROM tbl_testing INNER JOIN 
    (SELECT [tbl_testing].[SS], [tbl_testing].[D/A TEST],
    Max([tbl_testing].[TESTDATE]) AS MaxOfTESTDATE
    FROM tbl_testing
    GROUP BY [tbl_testing].[SS], [tbl_testing].[D/A TEST])
AS qryOne 
ON (tbl_testing.SS = qryOne.SS) 
AND (tbl_testing.[D/A TEST] = qryOne.[D/A TEST]) 
AND (tbl_testing.TESTDATE = qryOne.MaxOfTESTDATE);

Now, quit puffing up your chest, Jon K; there is never only one way.

 

[raskew]

Jon -

Very nice demo. Speed difference was illuminating.


ByteMyzer -
"Syntax error in FROM clause"

Bob

 

[ByteMyzer]

raskew, you will of course get a Syntax Error if you use A97. The database is in A2K. I tested it, with NO Syntax Errors.

 

[Rose412]

I pasted the query in the database, but when I wanted to save it I also got an error: Syntax error in FROM Clause.

When I clicked the OK button, the word SELECT in the subquery is highlighted.

 

[Rose412]

Oh you beat me to that. So I can't use one query.

 

[raskew]

Mr. ByteMyzer-

Appears you're on a roll today.

I'm relatively new to this stuff. Could you explain (simple terms, please)
why a A97 query shouldn't be expected to work when applied against
tables in an A2000 application.

Thanks in advance,

Bob

Oopsi-daisy: Just realized I mispoke her. Question should be:

What is there about query-SQL that you'd expect not to work in A97?

Me bad, sorry - Bob

 

[ByteMyzer]

As simple as possible, raskew:
In A97, an inline query expression is enclosed in braces with a period []. like so:
[SELECT * FROM MyTable;]. AS qryInline

In A2K, an inline query expression is enclosed in parenthesis () like so:
(SELECT * FROM MyTable AS qryInline


raskew, Rose412:
As to why either one of you gets a Syntax Error with my Inline-Query example, I couldn't tell you. I DO know that when I open the original file posted by travismp, in A2K, and I insert the query, I do NOT get a Syntax Error.

 

[Rose412]

ByteMyzer,

Since I can't test it in A97, I'm curious to know if your query runs much faster than Jon K's two queries in A2K.

I have difficulty understanding your SQL statement, particularly because it doesn't work in the version I am using. However, I can easily build Jon K's two queries in Design View and I understand what each of them does.

 

[ByteMyzer]

Rose412, see my above explanation to raskew as to why it won't work in A97.

Unfortunately, if you were to run this specific database in A97, the inline query scenario would not be an option. Because at least one field referred to in the inline query has a field name with a space character - [D/A TEST] - it must be enclosed in braces as shown, which will not work with the A97 Syntax for an inline query: braces inside of the inline-query braces cause a Syntax Error. In this case, the two-query scenario is a better option.

Since the original file is in A2K format, I offered the option for a single query with inline query, since the A2K inline syntax, with parenthesis instead of braces and without the period, will work for this scenario. It is just as fast as the two-query scenario offered by Jon K.

 

[GGib7711]

In A2K, an inline query expression is enclosed in parenthesis () like so:
(SELECT * FROM MyTable; ) AS qryInline

..... I offered the option for a single query with inline query, since the A2K inline syntax, with parenthesis instead of braces and without the period, will work for this scenario. It is just as fast as the two-query scenario offered by Jon K.

I have read where people complained about Access changing the ( ) brackets to [ ] when a query with an inline expression was saved. ( http://www.access-programmers.co.uk/forums/showthread.php?t=61934&highlight=[Select )


Since both a single query with a subquery (as you offered in your first post) and a single query with an inline expression (as you offered later) can't run faster than Jon K's two queries, the development in this thread has brought out the correctness in Jon K's statement that:

Where queries are concerned, there is no such a thing as The Fewer The Better.​

I would prefer using Jon K's two-query approach for three reasons:
(1) I don't need to worry about Access versions.
(2) I don't need to worry about Access changing my query when it is saved.
(3) I can easily build the two queries in Design View with the mouse without having to type a complicated SQL statement in SQL View.

 

[ByteMyzer]

Take a breath, GGib7711, you'll run out of hot air. For one, you're not even the one who posted the original question. Two, I offered an alternative; I did not present it as gospel. Whatever works best for you, that's what you go with.

 

[GGib7711]

For one, you're not even the one who posted the original question.

Though I'm not the original poster, I posted a related link about inline expression.

.

Two, I offered an alternative; I did not present it as gospel.

But what you wrote sounded you did in your first post:-

You don't need two queries to do this; you can do it with one query (query with a subquery)​

 

[ByteMyzer]

No, GGib7711, if I had presented it as gospel, it would have been more like:

"You should not be using two queries to do this; you must only use one query".

What I wrote was:

You don't need two queries to do this; you can do it with one query (query with a subquery)

As such, whether travismp decides to go with the two-query method, the query-subquery method, the query/inline-query method, or even a THREE query method, or whatever he wants to use, I really don't give a husky-doo.


I think we're starting to lose sight of what these forums are really about. These forums are about questions and answers on how to do various things with a powerful, versatile tool. Last I checked, the idea is not intended to be whose way is right and whose is wrong. Now let's drop it and move on, shall we?