×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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.

Students Click Here

MS ACCESS UNION ALL with Attachment data type

MS ACCESS UNION ALL with Attachment data type

MS ACCESS UNION ALL with Attachment data type

(OP)
Hi All,

I was just wondering if anyone can elaborate on what the code would look like for the answer by Programmer dhookom on thread700-1724615: Access 2010 UNION ALL with Attachment data type: https://www.tek-tips.com/viewthread.cfm?qid=172461...

I know this has been answered before but I am having some struggles.

This is my SQL code as follows:
SELECT Table1.[Folder #], Table1.[Part #], Table1.[Lot#/ SN], Table1.[Inspection Date], Table1.[Expiry Date], Table1.[SHIP #]
FROM Table1;
UNION ALL
SELECT [GII Register Data2].[Folder #], [GII Register Data2].[Part #], [GII Register Data2].[Lot#/ SN], [GII Register Data2].[Inspection Date], [GII Register Data2].[Expiry Date], [GII Register Data2].[SHIP #]
FROM [GII Register Data2];

(In the above code the attachment column was not included as I would receive an error) This SQL code works great though - without the attachments of course, and want to now add the attachments to my newly merged table.

Now I am wondering how I can use the LEFT JOIN function or anything else to add the attachment column for both Table1 and GII Register2

Any help would be really helpful. Thank you all!

RE: MS ACCESS UNION ALL with Attachment data type

If this SQL works OK for you:

CODE

SELECT [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #]
FROM Table1
UNION ALL
SELECT [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #]
FROM [GII Register Data2] 

could you show your SQL "with attachments" that causes the error?
And, what error are you getting?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: MS ACCESS UNION ALL with Attachment data type

What are your primary key and attachment fields in Table1 and [GII Register Data2]? Also, you need to save the working UNION query and provide us with its name.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
@Andrzejek - Thanks for your reply, I appreciate it.

Correct that SQL works perfectly and joins the two tables together. However, I did not include my attachments field ([Softcopy]) within that, otherwise I would receive the error "the multivalued field '[Softcopy]' cannot be used in a UNION query. I understand why it doesn't work, but I have seen other posts where people were able to successfully bypass this error and add their attachments using another query I think? I'm just not sure how to execute it.

This is the code that would give me the error (with [softcopy] being my attachments column:

SELECT [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #], [Softcopy]
FROM Table1
UNION ALL
SELECT [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #], [Softcopy]
FROM [GII Register Data2]

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
@dhookom - Thanks for the reply, I appreciate it.

I have left the primary key as "ID" for both tables, where each row is given a number (i.e. 1,2,3,4...). These numbers between each of my tables have no correlation though, I'm just wanting to paste one table on the bottom of another in order to create a continuous form - rather than having multiple forms. The reason I am doing this also is that I have exceeded the 2gb limit on another access file - so I have tried to bypass this by linking the table to a new file, and joining it with my newly created one. Hope this makes sense.

The working union query is saved - though Im not sure what you mean by name? The name of my query is "query3" and I have two other queries "query1" and "query2" that contain each of the tables respectively so I could grab the SQL code from both to paste into query 3.

Hope that isn't too confusing. Thanks :)

RE: MS ACCESS UNION ALL with Attachment data type

Anthony, Welcome to tek-tips.

Consider using TGML tags to format your posts to make them easier to read. Notice how Andy's post is more readable. Another suggestion is to find and use a good naming convention for tables, fields, queries, and everything else you name in Access. Most of us old mature developers won't use spaces or special characters in object names. A field named ID is rarely a good name since it doesn't suggest the table where it is used. Do yourself a favor and do some reading on this. I think you will love implementing a consistent system.

Create these four queries and see what you get in the final. You may be able to remove the last two columns from the final query.

quniTable1GIIRegister

CODE --> SQL

SELECT "T1" & ID IDKey, [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #]
FROM Table1
UNION ALL
SELECT "GII" & ID, [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #]
FROM [GII Register Data2]; 

qselT1_Softcopy

CODE --> SQL

SELECT "T1" & ID AS IDKey, Softcopy
FROM Table1; 

qselGII_Softcopy

CODE --> SQL

SELECT "GII" & ID AS IDKey, Softcopy
FROM [GII Register Data2]; 

qselUniWithSoftCopy

CODE --> SQL

SELECT U.*, NZ(T1.SoftCopy,GII.SoftCopy) AS CSoftCopy, T1.SoftCopy AS T1SoftCopy, GII.SoftCopy AS GIISoftCopy
FROM quniTable1GIIRegister U 
LEFT JOIN qselT1_Softcopy T1 ON U.IDKey = T1.IDKey
LEFT JOIN qselGII_Softcopy GII ON U.IDKey = GII.IDKey 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
Thanks for taking your time to reply dhookom! I truly appreciate your help!

I have followed all of your code, but have unfortunately received a couple errors.

For the first query you provided, I received the following error:


I added the "IDKey" onto the second SELECT line code following the "ID" (as you wrote only "SELECT "GII" & ID"). But still returned the same error as attached above.

I then deleted "IDKey" from both select lines and it ran fine for some reason?

Anyway continuing on, I got to your final provided query (this is after deleting the "IDKey" from the initial query as stated above) and I received the following error:


Since I had previously removed the "IDKey" since the initial query ran fine without it, I thought I'd do the same for the final query and remove it- so the bottom 2 lines of code would read U.ID = GII.ID
However, I still received the same error as attached above.


Apologies for the mess, I hope it makes some sense to you. Also, how can I use the TGML? Sorry, I am new to access and the world of coding. Thanks again for your help :)

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
And just to let you know- FYI this is my first week of using access. So please excuse my horrible explanations and me being so clueless. I appreciate your patience :)

RE: MS ACCESS UNION ALL with Attachment data type

I forgot the word “as” which is optional in SQL Server but required in Access:


SELECT "T1" & ID AS IDKey

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
Thanks dhookom, it now works for the first query.

However, I receive the following error on the last query:


Is there something that I've overlooked?

Thanks again for your patience :)

RE: MS ACCESS UNION ALL with Attachment data type

Probably it should be possible to build the last query with visual designer and inspect sql. For multiple joins you need them ordered in hierarchy, in parenthesis, otherwise there are too many JOINS causing error.
Please apply Duane's tip about TGML, images are hard to read and work with. Better to paste and format sql string, the only error message box can be copied with ALT+PrtSc keys.

combo

RE: MS ACCESS UNION ALL with Attachment data type

Combo is correct that posting pictures is both more difficult for you and for me. Please copy and paste the SQL view of the query and use the Code tag [<>]. My syntax was attempted without actually being Access and should work in SQL Server. Try the following for MS Access:

CODE --> SQL

SELECT U.*, NZ(T1.SoftCopy,GII.SoftCopy) AS CSoftCopy, T1.SoftCopy AS T1SoftCopy, GII.SoftCopy AS GIISoftCopy
FROM qselGII_Softcopy GII RIGHT JOIN (qselT1_Softcopy T1 RIGHT JOIN quniTable1GIIRegister U ON T1.IDKey = U.IDKey) ON GII.IDKey = U.IDKey; 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
Hi guys, thanks so much for responding. Apologies for the delayed reply, I have not been in the office over the weekend.

I used your exact code (as below) for the last query Dhookoom, and I received an error as follows:

CODE --> U.IDKey;

 

"The multivalued field 'T1. Softcopy is not valid in the expression 'NZ(T1.Softcopy,GII.Softcopy)'.

I believe this is a similar error to what I had when first attempting to union the tables with attachments.

Any further help would be greatly appreciated, thanks so much for your patience :)

EDIT: I'm not too sure why my code isnt showing? But it is the same as you posted in the previous post dhookom. Cheers

RE: MS ACCESS UNION ALL with Attachment data type

Try to remove the column with the Nz(). I never use attachment and multi value fields. This enforces my decision.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
By that you mean to not include the attachment column? That's a shame, I thought it could be done. Thanks for your assistance anyway, I appreciate it.

RE: MS ACCESS UNION ALL with Attachment data type

No, just the Nz(). Didn’t I have the attachment fields in other columns?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
Sorry, I don't understand what you mean? The Nz() code was not used in any other queries? Thanks

RE: MS ACCESS UNION ALL with Attachment data type

My post with the Nz() column, simply remove the one NZ() column and leave all the others.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
Sorry, so the code would look like this?

CODE --> U.IDKey;

 
SELECT U.* T1.SoftCopy AS T1SoftCopy, GII.SoftCopy AS GIISoftCopy
FROM qselGII_Softcopy GII RIGHT JOIN (qselT1_Softcopy T1 RIGHT JOIN quniTable1GIIRegister U ON T1.IDKey = U.IDKey) ON GII.IDKey = U.IDKey;

RE: MS ACCESS UNION ALL with Attachment data type

Yes, what are the results? Does it work?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
Unfortunately not, I receive the error "syntax error in query expression 'U.*T1.Softcopy'. :(

RE: MS ACCESS UNION ALL with Attachment data type

The error message suggests you removed too many commas. What do you get with this SQL:

CODE --> SQL

SELECT U.*,  T1.SoftCopy AS T1SoftCopy, GII.SoftCopy AS GIISoftCopy
FROM qselGII_Softcopy GII RIGHT JOIN 
   (qselT1_Softcopy T1 RIGHT JOIN 
    quniTable1GIIRegister U ON T1.IDKey = U.IDKey) ON GII.IDKey = U.IDKey; 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
I get the merged table with both attachment fields! However, the pdf files I previously attached are not present. Please see the photo to see what I am talking about.


Thanks again

RE: MS ACCESS UNION ALL with Attachment data type

Sorry but again I don't use attachment or multi-value fields so it's impossible for me to visualize what you expect. I typically store the path to the PDF file in a separate child table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
Oh, that is exactly what I'm looking for - but just my attachments aren't actually showing (as you can see they all say "0" when in reality there's 1-3 attachments per row. I will probably opt to storing hyperlinks for each row since it seems a whole lot easier. Thanks again for your assistance Duane!

RE: MS ACCESS UNION ALL with Attachment data type

If you get rid of the attachment fields you might not exceed the file size issue.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
Exactly right, that was the big issue. Oh well, time to add hyperlinks for 1000+ rows haha. Thank you for your patience and assistance throughout the past few days!

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
Also, quick question for you, would adding a hyperlink using a hyperlink field with ctrl + k be the most effective/ safest way to add a link to a specific location? Thanks

RE: MS ACCESS UNION ALL with Attachment data type

I never use hyperlink fields.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS ACCESS UNION ALL with Attachment data type

(OP)
No worries, thanks for your time :)

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! Already a Member? Login

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