Can you have several query results in a report?

[Toga]

I need to display a report that shows everythig about a lot# through all the stages of production including shipment information. Obviously several different production steps (seperate tables) involved.

I've seen reports like this but I'm not sure where to start.

Can a report be the result of several query's somehow?

Thanks in advance

 

[MichaelRed]

The REPORT can only have a single record source. You can use seperate queries or domain functions as the CONTROLSOURCE for individual controls, however this gets to be cumbersome for more than a few controls. The more normal practice is to 'collect' the information relevant to the report in a single query. This 'final' query is often the last in a series of queries which simply join the results of several recordsets which are joined to collect the results. The layers of the queries often use a similar (or same) set of Parameters and sortings to get the data from the seperate recordsets for the report.

I beleive the sample databases which are available to every copy of Ms. Access give some examples of the approach.

Without knowing a great deal more about the details than I should want to know, this is about as far as I can go.


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Toga]

What about using several subreports

 

[JoeMiller]

Several subreports would be the way I'd do it. I normally have a main report that just pulls the MainID I'm reporting on (Lot# in your case) and then sub-reports for each thing I'm interested in reporting on, sales, shipments, manufactured, etc... Each subreport can then also be printed by itself to show you only that part of the report or in the main report to show it next to the other data. Each subreport goes in the detail section of the main, that way you get a subreport for each Lot# if you group on Lot#.

HTH Joe Miller
joe.miller@flotech.net

 

[MichaelRed]

SubReport works -up to a point. The page length limit may be a problem after a bit. For me, the SubReport is more like unto the band-aid. Works fine for little stuff, but not for industrial grade problems. Wheather you can use the subreport, of course, depends on the volume (e.g. cummulative Lenth * Height of controls of info to be presented + white space) wheather it is easier is more a matter of style / taste.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[roddy17]

hi
i'm working with a similar type of problem where i need to get data from a couple of crosstab queries and a table.
the main information is gotten from a table (UNIT_NUMBER, TOTAL_UNITS, and DATE), and the other information is gotten from the queries after doing some manipulation. Suffice to say that the UNITS are voted on - either FOR or AGAINST.
and i made up queries to separate the units voted FOR and AGAINST and UNITS with no votes.
here are the different statements that i tried in the CONTROL SOURCE (one statement for the crosstab, and one for a regular query i created from the crosstab):
= [Crosstab_Main_FOR]![Total Of NUM_OF_UNITS] where [REPORT ONE]![FUND_NUMBER] = [Crosstab_Main_FOR]![FUND_NUMBER]

=[Query Crosstab_Main_FOR]![Total Of NUM_OF_UNITS] where
[REPORT ONE]![FUND_NUMBER] = [Query Crosstab_Main_FOR]![FUND_NUMBER]

Is this correct?

 

[Toga]

Thanks guys..... Multiple sub-reports is the approach I took for now. The lot # is entered on the form that I access the reports from and the query's for each sub-report references the lot # I entered. Seems to work pretty good.

Note: Another part of the challenge for me was that the format of the lot # in the database is XXX-XX-XXXXXX and what I want to query is the XX-XXXXXX part of the lot # so my query's had to be set up to retrieve (Like *XX-XXXXXX).
I couldn't figure out how to do this or have the control I wanted from within the report.

I don't think the page length is going to be too much of an issue but I'll keep an eye on it. I have the Can Grow / Can Shrink parameters set to Yes for each of the sub-reports......so if there's no data, they just don't print or take up any space on my report.

 

[mustangirl]

You can have a subreport in the orginal report. The subreport can contain the other query data you want. The button is with the text, label, control button, ect.