Looping through queries to build a continious report 1

[dandough]

Hi all,

I have a master report that has 3 sub reports which are based on 3 different queries, respectively. I generate the report from a Form. The queries use the same variable that is selected from a list. I now have a my report which is based on one item in the list. How can I open the report and keep it open while I loop through my list creating a complete report as opposed to an itemized report?

As always I'm just looking to be pointed in the right direction.

Thanks,
Dan

 

[hneal98]

Your question is not quite clear. Is your report supposed to be based on the three queries? Can you give more detail?

 

[dandough]

Hi hneal98,

I created 3 queries all of which use the same variable taken from a form list. I then created 3 Reports, one from each query. I then dragged and dropped each report into a main report. I now have a report based on a single investor(the variable criteria). What I would like to accomplish is the following:

One Report for all Investors. It would have the following style:

Investor1
info1
info2
info3
Investor2
info1
info2
info3
Investor...


I hope this helps.

Thanks,
Dan

 

[hneal98]

Ok, I see. Is there a way to combine the queries? Since I don't know what your data looks like, I don't know. If you could do that, it would be easier to build your report. If you can, post the SQL of your queries here so we can look at them.

 

[dandough]

The Report now reads like this:

INVESTOR
One on One
manager
manager
...
Capital Introduction Event
manager
manager
...
Capital Intrduction Referrals
manager
manager
...

I would like to append the next investor and so on...

Thanks again,
Dan

SELECT [2003].Manager
FROM 2003
WHERE ((([2003].Investor)=fncGetInvestor()) AND (([2003].["One on One" Manager Meetings])>0));

SELECT [2003].Manager
FROM 2003
WHERE ((([2003].Investor)=fncGetInvestor()) AND (([2003].[Capital Introductions Event "Manager Sessions" Attended])>0));

SELECT [2003].Manager
FROM 2003
WHERE ((([2003].Investor)=fncGetInvestor()) AND (([2003].[Capital Introduction Referrals])>0));

 

[teddyatjorundotnet]

YOu may be able to do this with an OR operator in your where clause.

SELECT [2003].Manger
FROM 2003
WHERE (([2003].Investor=fncGetInvestor) AND ([2003].["One on One" Manager Meetings])>0) OR ([2003].[Capital Introductions Event "Manager Sessions" Attended])>0) OR ([2003].[Capital Introduction Referrals])>0));

Perhaps something to that effect? Basically instead of returning a manager based on three queries with three different criteria, you're returning a list of managers that meet any one of your defined criteria.. ?

 

[dandough]

Thanks All,

Manipulating the query got me the information I needed for my report into one table which was basically a table sort:

SELECT [Investors Sort].Investor, [2003].Manager, [2003].[Capital Introduction Referrals], [2003].["One on One" Manager Meetings], [2003].[Capital Introductions Event "Manager Sessions" Attended]
FROM [Investors Sort] LEFT JOIN 2003 ON [Investors Sort].Investor = [2003].Investor;