Sub Query - Easy How to ??

[WANNABE]

I'm sure this is an easy question for someone out there, and I used to be able
to do this..
I have 2 queries that produce the results I need, but I would like to remember
how to do it with a sub-query.
My 2 queries are below, and the results are a list of distinct records. The
newest of the last 2 years, one record per country.
I would greatly appreciate any assistance, Thank you very much.

SELECT [Country Profile].COUNTRY, [Country Profile].Period, [Country
Profile].LQR, [Country Profile].[MLT DEBT], [Country Profile].[MST DEBT],
[Country Profile].[SPLT DEBT], [Country Profile].[FLT DEBT], [Country
Profile].[Country Limit]
FROM [Country Profile], [qq_CountryLimits_Last2Yrs t2]
WHERE ((([Country Profile].COUNTRY)=[qq_CountryLimits_Last2Yrs t2].[country])
AND (([Country Profile].Period)=[qq_CountryLimits_Last2Yrs t2].[maxofperiod]))
ORDER BY [Country Profile].COUNTRY, [Country Profile].Period;

***qq_CountryLimits_Last2Yrs t2****
SELECT [Country Profile].COUNTRY, Max([Country Profile].Period) AS MaxOfPeriod
FROM [Country Profile]
GROUP BY [Country Profile].COUNTRY
HAVING (((Max([Country Profile].Period))=Year(Now()) Or (Max([Country
Profile].Period))=Year(Now())-1))
ORDER BY [Country Profile].COUNTRY, Max([Country Profile].Period);

 

[Stefan Hoffmann]

hi,

I'm sure this is an easy question for someone out there, and I used to be able
to do this..
I have 2 queries that produce the results I need, but I would like to remember
how to do it with a sub-query.

SELECT
[Country Profile].COUNTRY, [Country Profile].Period,
[Country Profile].LQR, [Country Profile].[MLT DEBT],
[Country Profile].[MST DEBT], [Country Profile].[SPLT DEBT],
[Country Profile].[FLT DEBT], [Country Profile].[Country Limit]
FROM
[Country Profile],
(
SELECT
[Country Profile].COUNTRY,
Max([Country Profile].Period) AS MaxOfPeriod
FROM
[Country Profile]
GROUP BY [Country Profile].COUNTRY
HAVING (Max([Country Profile].Period)=Year(Now())
Or
(Max([Country Profile].Period))=Year(Now())-1))
ORDER BY
[Country Profile].COUNTRY, Max([Country Profile].Period
) [qq_CountryLimits_Last2Yrs t2]
WHERE ((([Country Profile].COUNTRY)=[qq_CountryLimits_Last2Yrs
t2].[country])
AND (([Country Profile].Period)=[qq_CountryLimits_Last2Yrs
t2].[maxofperiod]))
ORDER BY [Country Profile].COUNTRY, [Country Profile].Period;

But there are some caveats:

Such a query may be "too complex" for Jet, thus some of these queries
cannot been rewritten to utilize sub queries.

Such a query is often read-only, so you can't modify data.


mfG
--> stefan <--

 

[WANNABE]

Thank you for your time. I must have phrased my question poorly, as the
response here still uses the second query and my intention is to COMBINE the 2
queries into one. Can that be done and how?
=================================================
hi,

I'm sure this is an easy question for someone out there, and I used to be able
to do this..
I have 2 queries that produce the results I need, but I would like to remember
how to do it with a sub-query.

SELECT
[Country Profile].COUNTRY, [Country Profile].Period,
[Country Profile].LQR, [Country Profile].[MLT DEBT],
[Country Profile].[MST DEBT], [Country Profile].[SPLT DEBT],
[Country Profile].[FLT DEBT], [Country Profile].[Country Limit]
FROM
[Country Profile],
(
SELECT
[Country Profile].COUNTRY,
Max([Country Profile].Period) AS MaxOfPeriod
FROM
[Country Profile]
GROUP BY [Country Profile].COUNTRY
HAVING (Max([Country Profile].Period)=Year(Now())
Or
(Max([Country Profile].Period))=Year(Now())-1))
ORDER BY
[Country Profile].COUNTRY, Max([Country Profile].Period
) [qq_CountryLimits_Last2Yrs t2]
WHERE ((([Country Profile].COUNTRY)=[qq_CountryLimits_Last2Yrs
t2].[country])
AND (([Country Profile].Period)=[qq_CountryLimits_Last2Yrs
t2].[maxofperiod]))
ORDER BY [Country Profile].COUNTRY, [Country Profile].Period;

But there are some caveats:

Such a query may be "too complex" for Jet, thus some of these queries
cannot been rewritten to utilize sub queries.

Such a query is often read-only, so you can't modify data.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Thank you for your time. I must have phrased my question poorly, as the
response here still uses the second query and my intention is to COMBINE the 2
queries into one.

Yup, that's true. My example uses a sub-query.

Can that be done and how?

As you have to filter for an aggregate, no. Because you need to build
that aggregat and then you need to join it.


mfG
--> stefan <--

 

[WANNABE]

I thought I had found a way to do just that, in a query I needed 2 years ago,
but I could be mistaken.
Thanks for your time.
========================
hi,

Thank you for your time. I must have phrased my question poorly, as the
response here still uses the second query and my intention is to COMBINE the 2
queries into one.

Yup, that's true. My example uses a sub-query.

Can that be done and how?

As you have to filter for an aggregate, no. Because you need to build
that aggregat and then you need to join it.


mfG
--> stefan <--