Best Design Approach

[Guest]

Inherited DB with multiple tables that hold similar data. Need best design
approach to creating a report.

Pseudo Code for macro:
Set Warnings Off
Empty temp table
Merge Table A with Table B (only fields needed for report) in Temp Table
Report from Temp Table
Set Warnings On
End

Is this a valid approach? I am not allowed to redesign the database, only
add the new report.

 

[Allen Browne]

Ed, that's doable, but could you just use an UNION query?

Create 2 queries: one based on table A, and the other on table B.
Make sure both queries have the same number of fields, in the same order, so
they mach.

Switch both queries to SQL View (View menu.)
At the end of the first query, delete the semicolon, and enter:
UNION ALL
Copy in the SQL statement from the 2nd query.

You will end up with something like this:
SELECT F1, F2 FROM TableA
UNION ALL
SELECT F9, F3 FROM TableB;

Save the query, and build the report on that.

 

[John Vinson]

Inherited DB with multiple tables that hold similar data. Need best design
approach to creating a report.

Pseudo Code for macro:
Set Warnings Off
Empty temp table
Merge Table A with Table B (only fields needed for report) in Temp Table
Report from Temp Table
Set Warnings On
End

Is this a valid approach? I am not allowed to redesign the database, only
add the new report.

The temp table is unnecessary; can you not instead simply use a Select
query joining the tables, or a UNION query if they contain the same
kind of data? A Report need not be based on a table; in fact it will
ordinarily be based on a Select Query.

John W. Vinson[MVP]