INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access 2010 UNION ALL with Attachment data type

Access 2010 UNION ALL with Attachment data type

(OP)
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 2thumbsup

RE: Access 2010 UNION ALL with Attachment data type

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

RE: Access 2010 UNION ALL with Attachment data type

(OP)
You Rock!! Worked like a charm..... Thanks so much!

Best,
Blue Horizon 2thumbsup

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close