Best Design Approach

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ed Bloom - 12 Apr 2006 15:28 GMT

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.

Reply to this Message

Allen Browne - 12 Apr 2006 15:45 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Inherited DB with multiple tables that hold similar data. Need best
> design
[quoted text clipped - 10 lines]
> Is this a valid approach? I am not allowed to redesign the database, only
> add the new report.

Reply to this Message

John Vinson - 12 Apr 2006 23:41 GMT

>Inherited DB with multiple tables that hold similar data. Need best design
>approach to creating a report.
[quoted text clipped - 9 lines]
>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]

Reply to this Message