union queries - with tables containing fields with attachments

  • Thread starter Thread starter Dawie Theron
  • Start date Start date
D

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
 
I need to build a query which combine records from a number of similar tables.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
 
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.
 
Back
Top