Sub Query question

[dysgraphia]

[Apologies, this ended up in someone else's thread at first post]

I have a table with fields:
MDate PatientName TestA TestB

I would like to run a query that returns, say, the
5 most recent TestA and TestB results for all the PatientNames.
I think I need to do a sub query and hence write the SQL
rather than use the design grid. I have checked back in the newsgroup
and googled and found plenty of refs to sub queries but cannot find
a specific solution. Any suggestions appreciated!
....dysgraphia

 

[John Spencer]

Hopefully PatientName is unique (two patient's don't have the same name) and
it is consistently entered (John P Spencer and not John Spencer or John P.
Spencer or John Spencer Jr) . All those variations are one person.

SELECT *
FROM [YourTable]
WHERE MDate In
(SELECT TOP 5 Tmp.MDate
FROM [YourTable] as Tmp
WHERE Tmp.PatientName = [YourTable].PatientName)

 

[John Spencer]

Dab nag it!

SELECT *
FROM [YourTable]
WHERE MDate In
(SELECT TOP 5 Tmp.MDate
FROM [YourTable] as Tmp
WHERE Tmp.PatientName = [YourTable].PatientName
ORDER BY Tmp.MDate Desc)

Forgot the dad burn Order by clause in the subquery.

Hopefully PatientName is unique (two patient's don't have the same name)
and it is consistently entered (John P Spencer and not John Spencer or
John P. Spencer or John Spencer Jr) . All those variations are one
person.

SELECT *
FROM [YourTable]
WHERE MDate In
(SELECT TOP 5 Tmp.MDate
FROM [YourTable] as Tmp
WHERE Tmp.PatientName = [YourTable].PatientName)

[Apologies, this ended up in someone else's thread at first post]

I have a table with fields:
MDate PatientName TestA TestB

I would like to run a query that returns, say, the
5 most recent TestA and TestB results for all the PatientNames.
I think I need to do a sub query and hence write the SQL
rather than use the design grid. I have checked back in the newsgroup
and googled and found plenty of refs to sub queries but cannot find
a specific solution. Any suggestions appreciated!
...dysgraphia

 

[dysgraphia]

John, Thanks for the quick response!
Maybe I was not precise enough. Each PatientName can appear in this
table many times, that is they can have had a particular TestA many
times. Once I get a handle on say collecting the most recent 5 tests
I can then do a GROUP BY PatientName with Avg([TestA]) etc.
The PatientNames themselves are uniquely identified in an ID table
to overcome possible confusion where patients can have the same name.
Apologies if my wording was a bit vague...queries at 5am are not my forte!
Cheers, pete

Hopefully PatientName is unique (two patient's don't have the same name) and
it is consistently entered (John P Spencer and not John Spencer or John P.
Spencer or John Spencer Jr) . All those variations are one person.

SELECT *
FROM [YourTable]
WHERE MDate In
(SELECT TOP 5 Tmp.MDate
FROM [YourTable] as Tmp
WHERE Tmp.PatientName = [YourTable].PatientName)

[Apologies, this ended up in someone else's thread at first post]

I have a table with fields:
MDate PatientName TestA TestB

I would like to run a query that returns, say, the
5 most recent TestA and TestB results for all the PatientNames.
I think I need to do a sub query and hence write the SQL
rather than use the design grid. I have checked back in the newsgroup
and googled and found plenty of refs to sub queries but cannot find
a specific solution. Any suggestions appreciated!
...dysgraphia

 

[John Spencer]

So what is the structure of the table(s)? You probably need the PatientID field
(and should be using that in the tests table and not the name. Also, are TestA
and TestB in separate fields in the same row or do you have separate rows for
each combination of Patient, Date, Test Type, and Results?

The query I proposed would work with the structure you posted. If your data
structure is different, then you need to post it.

You might post the SQL statement of a query that gives you all you want EXCEPT
the most recent tests per patient. That along with the name of the field
(MDate?) that determines the most recent tests may be all that is needed to give
you a more detailed answer.

John, Thanks for the quick response!
Maybe I was not precise enough. Each PatientName can appear in this
table many times, that is they can have had a particular TestA many
times. Once I get a handle on say collecting the most recent 5 tests
I can then do a GROUP BY PatientName with Avg([TestA]) etc.
The PatientNames themselves are uniquely identified in an ID table
to overcome possible confusion where patients can have the same name.
Apologies if my wording was a bit vague...queries at 5am are not my forte!
Cheers, pete

Hopefully PatientName is unique (two patient's don't have the same name) and
it is consistently entered (John P Spencer and not John Spencer or John P.
Spencer or John Spencer Jr) . All those variations are one person.

SELECT *
FROM [YourTable]
WHERE MDate In
(SELECT TOP 5 Tmp.MDate
FROM [YourTable] as Tmp
WHERE Tmp.PatientName = [YourTable].PatientName)

[Apologies, this ended up in someone else's thread at first post]

I have a table with fields:
MDate PatientName TestA TestB

I would like to run a query that returns, say, the
5 most recent TestA and TestB results for all the PatientNames.
I think I need to do a sub query and hence write the SQL
rather than use the design grid. I have checked back in the newsgroup
and googled and found plenty of refs to sub queries but cannot find
a specific solution. Any suggestions appreciated!
...dysgraphia

 

[Peter Jamieson]

G'day John, Thank you for your comments. Apologies for the tardy reply:
computer monitor
problems has had me off the ether.
After reading your comments it has struck me that my table design could do
with some revision and yes
the suggestion you made does work on a revised table structure I tested on a
mini database.
Many thanks for your excellent help!...cheers Peter

So what is the structure of the table(s)? You probably need the PatientID
field
(and should be using that in the tests table and not the name. Also, are
TestA
and TestB in separate fields in the same row or do you have separate rows
for
each combination of Patient, Date, Test Type, and Results?

The query I proposed would work with the structure you posted. If your
data
structure is different, then you need to post it.

You might post the SQL statement of a query that gives you all you want
EXCEPT
the most recent tests per patient. That along with the name of the field
(MDate?) that determines the most recent tests may be all that is needed
to give
you a more detailed answer.

John, Thanks for the quick response!
Maybe I was not precise enough. Each PatientName can appear in this
table many times, that is they can have had a particular TestA many
times. Once I get a handle on say collecting the most recent 5 tests
I can then do a GROUP BY PatientName with Avg([TestA]) etc.
The PatientNames themselves are uniquely identified in an ID table
to overcome possible confusion where patients can have the same name.
Apologies if my wording was a bit vague...queries at 5am are not my
forte!
Cheers, pete

Hopefully PatientName is unique (two patient's don't have the same
name) and
it is consistently entered (John P Spencer and not John Spencer or John
P.
Spencer or John Spencer Jr) . All those variations are one person.

SELECT *
FROM [YourTable]
WHERE MDate In
(SELECT TOP 5 Tmp.MDate
FROM [YourTable] as Tmp
WHERE Tmp.PatientName = [YourTable].PatientName)



[Apologies, this ended up in someone else's thread at first post]

I have a table with fields:
MDate PatientName TestA TestB

I would like to run a query that returns, say, the
5 most recent TestA and TestB results for all the PatientNames.
I think I need to do a sub query and hence write the SQL
rather than use the design grid. I have checked back in the newsgroup
and googled and found plenty of refs to sub queries but cannot find
a specific solution. Any suggestions appreciated!
...dysgraphia