Contact US

Question

MS ACCESS UNION ALL with Attachment data type

Anthony343242 (Programmer)

(OP)

4 Feb 21 04:06

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!

Andrzejek (Programmer) · Answer 1 · 2021-02-04 13:22:04

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

dhookom (Programmer) · Answer 2 · 2021-02-04 21:01:06

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

Anthony343242 (Programmer) · Answer 3 · 2021-02-04 22:01:51

@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]

Anthony343242 (Programmer) · Answer 4 · 2021-02-04 22:07:51

@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 :)

dhookom (Programmer) · Answer 5 · 2021-02-05 00:06:17

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

Anthony343242 (Programmer) · Answer 6 · 2021-02-05 00:47:20

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 :)

Anthony343242 (Programmer) · Answer 7 · 2021-02-05 00:55:42

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 :)

dhookom (Programmer) · Answer 8 · 2021-02-05 03:28:29

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

Anthony343242 (Programmer) · Answer 9 · 2021-02-05 03:36:06

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 :)

combo (TechnicalUser) · Answer 10 · 2021-02-05 10:04:35

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

dhookom (Programmer) · Answer 11 · 2021-02-05 11:06:43

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

Anthony343242 (Programmer) · Answer 12 · 2021-02-07 21:50:47

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

dhookom (Programmer) · Answer 13 · 2021-02-07 23:04:52

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

Anthony343242 (Programmer) · Answer 14 · 2021-02-07 23:08:57

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.

dhookom (Programmer) · Answer 15 · 2021-02-08 00:25:21

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

Anthony343242 (Programmer) · Answer 16 · 2021-02-08 00:31:32

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

dhookom (Programmer) · Answer 17 · 2021-02-08 00:34:41

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

Anthony343242 (Programmer) · Answer 18 · 2021-02-08 00:42:03

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;

dhookom (Programmer) · Answer 19 · 2021-02-08 01:12:29

Yes, what are the results? Does it work?

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

Anthony343242 (Programmer) · Answer 20 · 2021-02-08 01:15:25

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

dhookom (Programmer) · Answer 21 · 2021-02-08 04:47:21

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

Anthony343242 (Programmer) · Answer 22 · 2021-02-08 05:02:03

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

dhookom (Programmer) · Answer 23 · 2021-02-08 16:09:17

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

Anthony343242 (Programmer) · Answer 24 · 2021-02-08 22:20:17

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!

dhookom (Programmer) · Answer 25 · 2021-02-08 22:24:50

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

Anthony343242 (Programmer) · Answer 26 · 2021-02-08 22:28:28

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!

Anthony343242 (Programmer) · Answer 27 · 2021-02-09 00:29:13

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

dhookom (Programmer) · Answer 28 · 2021-02-09 01:18:54

I never use hyperlink fields.

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

Anthony343242 (Programmer) · Answer 29 · 2021-02-09 01:28:49

RE: MS ACCESS UNION ALL with Attachment data type

Anthony343242 (Programmer)

(OP)

9 Feb 21 01:28

No worries, thanks for your time :)

Contact US

Come Join Us!

Posting Guidelines

MS ACCESS UNION ALL with Attachment data type

MS ACCESS UNION ALL with Attachment data type

MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

CODE

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

CODE --> SQL

CODE --> SQL

CODE --> SQL

CODE --> SQL

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

CODE --> SQL

RE: MS ACCESS UNION ALL with Attachment data type

CODE --> U.IDKey;

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

CODE --> U.IDKey;

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

CODE --> SQL

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

RE: MS ACCESS UNION ALL with Attachment data type

Red Flag This Post

Red Flag Submitted

Reply To This Thread

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

Join Tek-Tips® Today!

Join Tek-Tips^® Today!