Query inside of a query

[requeth]

Hello, I am trying to run a query inside of a query (the results of
one query feeds into another). My query1(pteamquerylookup) runs fine
and returns a single result, which is what I want. I then run query2,
which I have below, and it prompts me for data (which ends up being
the result of query1) instead of taking my results. I manually wrote
all of the query below except where I added in the nested query.

SELECT PAPERAUDIT_TABLE.PA_DCN, PAPERAUDIT_TABLE.PA_AUDIT_DATE,
PAPERAUDIT_TABLE.PA_APPLICANT_NAME
FROM PAPERAUDIT_TABLE
WHERE (((PAPERAUDIT_TABLE.PA_AUDIT_DATE)>=[Forms]![auditquery]!
[startdate] And (PAPERAUDIT_TABLE.PA_AUDIT_DATE)<=[Forms]![auditquery]!
[enddate]) AND ((PAPERAUDIT_TABLE.PA_QA_FX)=[pteamquerylookup]!
[AnalystID]) AND ((PAPERAUDIT_TABLE.PA_SPCT_FX)=[Forms]![auditquery]!
[npifxid]));

Am I nesting the query correctly? The query pteamquerylookup is open
and has a populated AnalystID when I run the query above, but it still
does not function properly. I've tried adding a [Query]! to it just to
see if that would help, but it did not change anything. I am running
Access 2000. Any help would be appreciated.

Thanks!

 

[Smartin]

Hello, I am trying to run a query inside of a query (the results of
one query feeds into another). My query1(pteamquerylookup) runs fine
and returns a single result, which is what I want. I then run query2,
which I have below, and it prompts me for data (which ends up being
the result of query1) instead of taking my results. I manually wrote
all of the query below except where I added in the nested query.

SELECT PAPERAUDIT_TABLE.PA_DCN, PAPERAUDIT_TABLE.PA_AUDIT_DATE,
PAPERAUDIT_TABLE.PA_APPLICANT_NAME
FROM PAPERAUDIT_TABLE
WHERE (((PAPERAUDIT_TABLE.PA_AUDIT_DATE)>=[Forms]![auditquery]!
[startdate] And (PAPERAUDIT_TABLE.PA_AUDIT_DATE)<=[Forms]![auditquery]!
[enddate]) AND ((PAPERAUDIT_TABLE.PA_QA_FX)=[pteamquerylookup]!
[AnalystID]) AND ((PAPERAUDIT_TABLE.PA_SPCT_FX)=[Forms]![auditquery]!
[npifxid]));

Am I nesting the query correctly? The query pteamquerylookup is open
and has a populated AnalystID when I run the query above, but it still
does not function properly. I've tried adding a [Query]! to it just to
see if that would help, but it did not change anything. I am running
Access 2000. Any help would be appreciated.

Thanks!

Hello, it doesn't look like your parens are all matched, but that's a
separate problem (^: When you use a query within another query, treat
the embedded query just like it was a table. You do not need to open the
embedded query first. Since it's treated like a table use the dot
operator to separate the query name from the field name:

Instead of
[pteamquerylookup]![AnalystID]
try
[pteamquerylookup].[AnalystID]

You can also join the embedded query (just like a table) to the other
table. E.g.,

SELECT ...

FROM PAPERAUDIT_TABLE INNER JOIN pteamquerylookup
ON PAPERAUDIT_TABLE.PA_QA_FX = pteamquerylookup.AnalystID

WHERE
PAPERAUDIT_TABLE.PA_AUDIT_DATE)>=[Forms]![auditquery]!
[startdate]
And
PAPERAUDIT_TABLE.PA_AUDIT_DATE)<=[Forms]![auditquery]!
[enddate]
AND
PAPERAUDIT_TABLE.PA_SPCT_FX)=[Forms]![auditquery]!
[npifxid];

Hope this helps!