Sub Query Basics for Newbie...

  • Thread starter Thread starter Tym
  • Start date Start date
T

Tym

I know this is probably basic stuff, but I never could get the hang of
subquerires!

I have a table which contains a plethora of data, all realted to a date
field (JD). I also have a verision field. (VERSION)

How do I write a query that will show me the full table, but only one line
per day with only the highest version for the day?

Tym
 
Tym said:
I know this is probably basic stuff, but I never could get the hang of
subquerires!

I have a table which contains a plethora of data, all realted to a date
field (JD). I also have a verision field. (VERSION)

How do I write a query that will show me the full table, but only one line
per day with only the highest version for the day?
Click to expand...


SELECT tbl.*
FROM tbl
WHERE tbl.VERSION = (SELECT Max(X.VERSION)
FROM tbl As X
WHERE X.JD = tbl.JD)
 
Marsh has answered your question. Depending on the way you set up your
table, you may need to choose fewer fields, and set the query's Unique
Values property to Yes to eliminate the duplicates.

If you are looking for some examples of how to build subqueries, here's a
starting point:
http://allenbrowne.com/subquery-01.html
 
Thanks to both...

Marshall, The query worked perfectly,

Allen, I'll have a look at your sub query page when I have more time - thank
you

Tym

Allen Browne said:
Marsh has answered your question. Depending on the way you set up your
table, you may need to choose fewer fields, and set the query's Unique
Values property to Yes to eliminate the duplicates.

If you are looking for some examples of how to build subqueries, here's a
starting point:
http://allenbrowne.com/subquery-01.html
Click to expand...
 
Back
Top