Access 2010 UNION ALL with Attachment data type 1

[BlueHorizon]

Hello everyone,
I created a database to keep client data, including helping me keep track of various letters sent to clients. Each record has an Attachment field that can contain 1 to 3 attachments (Letters).

I quickly outgrew the 2gb limit, so I split my data into separate tables representing years, i.e., 2011, 2012, 2013, 2014. I made each year a new database and used linked tables to work with everything in a single database.

Now I want to use a UNION ALL query to display data from all tables. In the UNION Query, I have a parameter set so I can query by specific client number. So far so good.

Here's the code for the Query:
SELECT Client_Matter_Number, Date_Letter_Sent, Comments, Status, Letter_Created_By, Contact_Name, Flag, Attachments, Remaining_Files_offsite
From 2011
WHERE ((([2011].[Client_Matter_Number]) Like [Which C/M?] & "*"))
UNION ALL
SELECT Client_Matter_Number, Date_Letter_Sent, Comments, Status, Letter_Created_By, Contact_Name, Flag, Attachments, Remaining_Files_offsite
From 2012
WHERE ((([2012].[Client_Matter_Number]) Like [Which C/M?] & "*"))
UNION ALL
SELECT Client_Matter_Number, Date_Letter_Sent, Comments, Status, Letter_Created_By, Contact_Name, Flag, Attachments, Remaining_Files_offsite
From 2013
WHERE ((([2013].[Client_Matter_Number]) Like [Which C/M?] & "*"))
UNION ALL
SELECT Client_Matter_Number, Date_Letter_Sent, Comments, Status, Letter_Created_By, Contact_Name, Flag, Attachments, Remaining_Files_offsite
From 2014
WHERE ((([2014].[Client_Matter_Number]) Like [Which C/M?] & "*"));

When I run the query, the error message states that a multivalue field cannot be displayed - this field being the Attachment field. When I use a query without the Attachment field, it works fine.

What can I do to my query to make the attachment field results display in the query results?

I'm very new at this UNION ALL query writing so any very specific help would be greatly appreciated. A good weekend to all!

Thank you in advance, K

Best,
Blue Horizon

 

[dhookom]

I think you could remove the attachment field from the union query. Then create a new query that LEFT joins the union query with each year table. I'm thinking this might result in multiple attachment columns, one for each year.

Most of us old/mature developers store the file name of the attachments.

Duane
Hook'D on Access
MS Access MVP

 

[BlueHorizon]

You Rock!! Worked like a charm..... Thanks so much!

Best,
Blue Horizon