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

On Fri, 26 Feb 2010 05:54:01 -0800, Dawie Theron
<DawieTheron@discussions.microsoft.com> wrote:

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

             John W. Vinson [MVP]

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 =3D 'A'
UNION
Select * From table Where Cond2 =3D 'B'

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

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





On Feb 26, 10:07=A0am, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Fri, 26 Feb 2010 05:54:01 -0800, Dawie Theron
>
> <DawieThe...@discussions.microsoft.com> wrote:
> >I need to build a query which combine records from a number of similar t=
ables.
>
> The first question to ask would be... why are you storing "similar" data =
in
> different tables? =A0That's generally bad design; would it be possible to=
 have
> one big table, with an additional field to indicate which subset of the d=
ata
> 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 ACCES=
S
> >2007 treat these fields as multi-value fields and does not allow buildin=
g
> >APPEND or UNION queries with multi-value fields.
>
> Does UNION ALL get around the problem? UNION will try to remove duplicate=
s,
> and it's unable to do so with attachment, memo or multivalue fields; UNIO=
N ALL
> will show you the duplicates. I know it works with MEMO fields, haven't t=
ried
> 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 a=
nd
> >some other queries on this.
>
> If you can explain a bit more about your database structure and environme=
nt
> someone might be able to suggest a different structure which would resolv=
e the
> problem.
> --
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0John W. Vinson [MVP]