UnionALL

[gkaro]

Hello All,

I have many tables that I consolidate with a UnionALL query.
One common field in every table is [YEAR]
What I would like to do is filter in the main union query all records that are 2011 or 2012 only.

This is what I have

SELECT * FROM [SEARS]


UNION ALL SELECT * FROM [FS/BB]
UNION ALL SELECT * FROM [COSTCO]
UNION ALL SELECT * FROM [HBC]
UNION ALL SELECT * FROM [WALMART]
UNION ALL SELECT * FROM [HOME DEPOT]
UNION ALL SELECT * FROM [LOWES]
UNION ALL SELECT * FROM [SALES IN]
UNION ALL SELECT * FROM [BEDBATH]
UNION ALL SELECT * FROM [CANTIRE]
UNION ALL SELECT * FROM [CORBEIL]
UNION ALL SELECT * FROM [BRICK]
UNION ALL SELECT * FROM [TSC]
UNION ALL SELECT * FROM [TEMP POS]
UNION ALL SELECT * FROM [LEONS];

[pbaldy]

You would either need to add a WHERE clause to each SELECT statement in the UNION, or base a second query on the UNION and put your criteria there. I assume you know it is a normalization mistake to have all those tables.

[gkaro]

Yes, I do now.
Created this as my first project. I realized that this sloooooows things down tremendously. Is this the reason its "incorrect" or is there more?

thanks for the help.
G

[pbaldy]

No, that's just a side effect. Let's say you start dealing with a new store called "Baldys". In a normalized db, your users just add a record to your "stores" table via the form you would have built for that purpose, and it flows through the application without any design changes by you. In your design, you have to build a new table, presumably a new form, your UNION query has to be modified, and who knows what other forms, queries and reports have to be modified to account for the new store.

[June7]

Yes, is bad design. If you use the UNION as source data for multitude of queries, forms, reports, maybe the required changes will be minimal but still a design change. Also, I have encountered a limit of 50 lines in a UNION and VBA would not play nice with UNION query. Had to modify a process to not rely on UNION so VBA code would work.

[boblarson]

And Union Queries are not updatable as well.