union queries - with tables containing fields with attachments

[Dawie Theron]

I need to build a query which combine records from a number of similar tables.

Building a union query works great with "normal" records.

Problem is, these tables contain attachments in some fields and MS ACCESS
2007 treat these fields as multi-value fields and does not allow building
APPEND or UNION queries with multi-value fields.

I am so frustrated with this, because I would like to base my searches and
some other queries on this.

PLEEEESe help

Cheers

Dawie Theron

 

[John W. Vinson]

I need to build a query which combine records from a number of similar tables.

The first question to ask would be... why are you storing "similar" data in
different tables? That's generally bad design; would it be possible to have
one big table, with an additional field to indicate which subset of the data
is involved? That would avoid any need for (inefficient, non-updateable, hard
to maintain) UNION queries.

Building a union query works great with "normal" records.

Problem is, these tables contain attachments in some fields and MS ACCESS
2007 treat these fields as multi-value fields and does not allow building
APPEND or UNION queries with multi-value fields.

Does UNION ALL get around the problem? UNION will try to remove duplicates,
and it's unable to do so with attachment, memo or multivalue fields; UNION ALL
will show you the duplicates. I know it works with MEMO fields, haven't tried
it with multivalue (because I HATE multivalue fields and never use them).

I am so frustrated with this, because I would like to base my searches and
some other queries on this.

If you can explain a bit more about your database structure and environment
someone might be able to suggest a different structure which would resolve the
problem.

 

[a a r o n . k e m p f]

John;

Uh.. last I checked, unions can and should be used _ALL_ the time.

It's quite possible that this
Select * From table Where Cond1 = 'A'
UNION
Select * From table Where Cond2 = 'B'

is considerably faster than this:
Select * From table Where Cond1 = 'A' OR Cond2 = 'B'

If you don't know why, then you're not qualified to charge people for
your db development services. Dead serious.