Nasty Multigenerational Query Parameter Help 
Author Message
 Nasty Multigenerational Query Parameter Help

I have a {*filter*} query that consists of several sub queries with all of
the parameters going into the first generation.  The final query, a make
table, runs fine when done manually, however,  I need to run it  from
VBA.  How do you place the parameters in the sub-sub queries that you
can't see directly, where if you could a DoCMD.RunSQL would work.

Should I do the whole thing in DAO, join the recordsets and append the
final result to a table?

Here is an example of one of the intermediate queries, having a Union
query as a sub query to it:

SELECT DISTINCT Agents.Name AS DivisionLeader,
[qryUnionAgentBusiness+Leads].SSN AS AgentSSN,
[qryUnionAgentBusiness+Leads].DivID, Agents_1.Name AS AgentName,
[qryUnionAgentBusiness+Leads].DivID AS Division
FROM AgentHierarchy INNER JOIN (Agents AS Agents_1 INNER JOIN
([qryUnionAgentBusiness+Leads] INNER JOIN Agents ON
[qryUnionAgentBusiness+Leads].DivID = Agents.EAID) ON Agents_1.SSN =
[qryUnionAgentBusiness+Leads].SSN) ON AgentHierarchy.AgentID =
Agents_1.EAID
WHERE ((([qryUnionAgentBusiness+Leads].DivID)<>"080" And
([qryUnionAgentBusiness+Leads].DivID)<>"001") AND
((AgentHierarchy.GAID)="001"));

Thanks for any help,

Steve House



Fri, 27 Apr 2001 03:00:00 GMT  
 Nasty Multigenerational Query Parameter Help
All nested parameters can be treated as members of the parameters
collection of the outermost query (the one you call).

 -- Andy

Quote:

>I have a {*filter*} query that consists of several sub queries with all of
>the parameters going into the first generation.  The final query, a make
>table, runs fine when done manually, however,  I need to run it  from
>VBA.  How do you place the parameters in the sub-sub queries that you
>can't see directly, where if you could a DoCMD.RunSQL would work.

>Should I do the whole thing in DAO, join the recordsets and append the
>final result to a table?

>Here is an example of one of the intermediate queries, having a Union
>query as a sub query to it:

>SELECT DISTINCT Agents.Name AS DivisionLeader,
>[qryUnionAgentBusiness+Leads].SSN AS AgentSSN,
>[qryUnionAgentBusiness+Leads].DivID, Agents_1.Name AS AgentName,
>[qryUnionAgentBusiness+Leads].DivID AS Division
>FROM AgentHierarchy INNER JOIN (Agents AS Agents_1 INNER JOIN
>([qryUnionAgentBusiness+Leads] INNER JOIN Agents ON
>[qryUnionAgentBusiness+Leads].DivID = Agents.EAID) ON Agents_1.SSN =
>[qryUnionAgentBusiness+Leads].SSN) ON AgentHierarchy.AgentID =
>Agents_1.EAID
>WHERE ((([qryUnionAgentBusiness+Leads].DivID)<>"080" And
>([qryUnionAgentBusiness+Leads].DivID)<>"001") AND
>((AgentHierarchy.GAID)="001"));

>Thanks for any help,

>Steve House




Fri, 27 Apr 2001 03:00:00 GMT  
 Nasty Multigenerational Query Parameter Help
Are the parameters of a base query accessable from the outermost query, if
the base query is a Union query?  I only ask since I can right click on all
queries except a union query and get the parameters collection.
Quote:

> All nested parameters can be treated as members of the parameters
> collection of the outermost query (the one you call).

>  -- Andy


> >I have a {*filter*} query that consists of several sub queries with all of
> >the parameters going into the first generation.  The final query, a make
> >table, runs fine when done manually, however,  I need to run it  from
> >VBA.  How do you place the parameters in the sub-sub queries that you
> >can't see directly, where if you could a DoCMD.RunSQL would work.

> >Should I do the whole thing in DAO, join the recordsets and append the
> >final result to a table?

> >Here is an example of one of the intermediate queries, having a Union
> >query as a sub query to it:

> >SELECT DISTINCT Agents.Name AS DivisionLeader,
> >[qryUnionAgentBusiness+Leads].SSN AS AgentSSN,
> >[qryUnionAgentBusiness+Leads].DivID, Agents_1.Name AS AgentName,
> >[qryUnionAgentBusiness+Leads].DivID AS Division
> >FROM AgentHierarchy INNER JOIN (Agents AS Agents_1 INNER JOIN
> >([qryUnionAgentBusiness+Leads] INNER JOIN Agents ON
> >[qryUnionAgentBusiness+Leads].DivID = Agents.EAID) ON Agents_1.SSN =
> >[qryUnionAgentBusiness+Leads].SSN) ON AgentHierarchy.AgentID =
> >Agents_1.EAID
> >WHERE ((([qryUnionAgentBusiness+Leads].DivID)<>"080" And
> >([qryUnionAgentBusiness+Leads].DivID)<>"001") AND
> >((AgentHierarchy.GAID)="001"));

> >Thanks for any help,

> >Steve House




Fri, 27 Apr 2001 03:00:00 GMT  
 Nasty Multigenerational Query Parameter Help
Yes. Try it.

 -- Andy

Quote:

>Are the parameters of a base query accessable from the outermost query, if
>the base query is a Union query?  I only ask since I can right click on all
>queries except a union query and get the parameters collection.


>> All nested parameters can be treated as members of the parameters
>> collection of the outermost query (the one you call).

>>  -- Andy


>> >I have a {*filter*} query that consists of several sub queries with all of
>> >the parameters going into the first generation.  The final query, a make
>> >table, runs fine when done manually, however,  I need to run it  from
>> >VBA.  How do you place the parameters in the sub-sub queries that you
>> >can't see directly, where if you could a DoCMD.RunSQL would work.

>> >Should I do the whole thing in DAO, join the recordsets and append the
>> >final result to a table?

>> >Here is an example of one of the intermediate queries, having a Union
>> >query as a sub query to it:

>> >SELECT DISTINCT Agents.Name AS DivisionLeader,
>> >[qryUnionAgentBusiness+Leads].SSN AS AgentSSN,
>> >[qryUnionAgentBusiness+Leads].DivID, Agents_1.Name AS AgentName,
>> >[qryUnionAgentBusiness+Leads].DivID AS Division
>> >FROM AgentHierarchy INNER JOIN (Agents AS Agents_1 INNER JOIN
>> >([qryUnionAgentBusiness+Leads] INNER JOIN Agents ON
>> >[qryUnionAgentBusiness+Leads].DivID = Agents.EAID) ON Agents_1.SSN =
>> >[qryUnionAgentBusiness+Leads].SSN) ON AgentHierarchy.AgentID =
>> >Agents_1.EAID
>> >WHERE ((([qryUnionAgentBusiness+Leads].DivID)<>"080" And
>> >([qryUnionAgentBusiness+Leads].DivID)<>"001") AND
>> >((AgentHierarchy.GAID)="001"));

>> >Thanks for any help,

>> >Steve House




Fri, 27 Apr 2001 03:00:00 GMT  
 Nasty Multigenerational Query Parameter Help
Another approach that has worked for me is to run make-table queries.
This does go slower, but it's easy to follow the logic and debug the thing.

- Turtle



Quote:
> I have a {*filter*} query that consists of several sub queries with all of
> the parameters going into the first generation.  The final query, a make
> table, runs fine when done manually, however,  I need to run it  from
> VBA.  How do you place the parameters in the sub-sub queries that you
> can't see directly, where if you could a DoCMD.RunSQL would work.

> Should I do the whole thing in DAO, join the recordsets and append the
> final result to a table?

> Here is an example of one of the intermediate queries, having a Union
> query as a sub query to it:

> SELECT DISTINCT Agents.Name AS DivisionLeader,
> [qryUnionAgentBusiness+Leads].SSN AS AgentSSN,
> [qryUnionAgentBusiness+Leads].DivID, Agents_1.Name AS AgentName,
> [qryUnionAgentBusiness+Leads].DivID AS Division
> FROM AgentHierarchy INNER JOIN (Agents AS Agents_1 INNER JOIN
> ([qryUnionAgentBusiness+Leads] INNER JOIN Agents ON
> [qryUnionAgentBusiness+Leads].DivID = Agents.EAID) ON Agents_1.SSN =
> [qryUnionAgentBusiness+Leads].SSN) ON AgentHierarchy.AgentID =
> Agents_1.EAID
> WHERE ((([qryUnionAgentBusiness+Leads].DivID)<>"080" And
> ([qryUnionAgentBusiness+Leads].DivID)<>"001") AND
> ((AgentHierarchy.GAID)="001"));

> Thanks for any help,

> Steve House




Fri, 27 Apr 2001 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. calling parameter query from a parameter query

2. Help needed: Setting parameters in parameter queries

3. Help needed: Setting parameters in parameter queries

4. Filling a Parameter through Code (Parameter Query)

5. Using Parameters from VBA Code in a Parameter Query Export

6. parameter query in VBA when parameter not a field

7. Using Parameter Queries with Optional Parameters

8. Parameter query w/ parameter specifying field name?

9. passing parameter to a parameter query

10. Passing Parameters to stored parameter queries using VB 5's Data Controls

11. Executing Parameter Query with 2 parameters

12. Qction queries and Query Parameters

 

 
Powered by phpBB® Forum Software