Subquery in Access?? Is that possible?

[Jason B]

I have a query that pulls: groupName Where the effective
dates are current (orgin_dt, term_dt). Each group has
multiple rows due to group terminating then going back
into production. So I believe that calls for a subquery.
I was thinking it would be something like this:
SELECT groupName
FROM group
WHERE term_dt =
(SELETCT MAX term_dt
FROM group
WHERE ?)

Am I on the right track? How do you write that in Access
SQL?

Thanks for the help in advance.

 

[Tom Ellison]

Dear Jason:

I'm not sure just what you want. Do you simply want the names of all
group where there is at least one current effective date? And what is
the definition of what is a current effective date?

Can you write a simple query that returns the groupName for all rows
that are current? If so, then reduce that query to show only
groupName and add DISTINCT, as in:

SELECT DISTINCT groupName FROM group
WHERE term_dt < whatever means it is currnet >

Change "< whatever means it is current >" to something that determines
whethere a term_dt value is, well, current.

If you really need a subquery, I need to know the additional details
as I asked above.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts