Consolidate duplicate reports

[winshent]

I am rewriting a reporting database. One report is essentially duplicated several times. The report will output columns:
quantityA, quantityB and quantityC..

another report will output columns:
quantityD, quantityE and quantityF

I would like to have a report which outputs different column data thru one report ie the first pages output data for A, B and C.. then outputs D, E and F..

I know how to reset the controlsource of controls at runtime in VBA. But that restricts to only running one set of data at a time, whereas i would like to be able to run off all reporting from A - Z from one user click.

Is this possible?

 

[namliam]

You can use a UNION query....

Assuming you have the queries already to make your reports on abc and def... then make a new query...
Select * from ABCQuery
UNION ALL
Select * from DEFQuery
UNION ALL
Select * from GHIQuery
etc...

Note:
The queries must have EXACTLY the same number of columns and EXACTLY the same column types, they must be as EXACTLY the same as possible...
Column names can be different... The column names in the query will be used from the first query.

Regards from amsterdam

 

[winshent]

thanks..

i've just re-read my post.. what i should have made clear is that i need column titles A, B and C.. then when the data for those finishes, then a page break and new column titles D, E and F..

A union doesnt help i'm afraid.. i wish it was that easy !!

 

[DCrake]

All is not lost. If you place a case statement in the group section header where your column heading labels are and evaluate the data to set the column heading.

First you will need to add a further column(s) to your queries inside your union query that denote the group by, Say "ABC", "DEF", "ETC". Then you case statement

Select Case MyGroup
Case "ABC"
Me.LblColumn1.Caption = "Hay"
Me.LblColumn2.Caption = "Bee"
Me.LblColumn3.Caption = "Sea"
Case "DEF"
Etc
End Select

David

 

[namliam]

A union doesnt help i'm afraid.. i wish it was that easy !!

A union is the only way to Unify multiple datasets that I know off....

You could do as David suggests... adding some code to the report... and grouping by a new field which you add to each query to specify where this record is comming from i.e. what headers it needs.