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!
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
CODE
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
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: MS ACCESS UNION ALL with Attachment data type
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
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
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
oldmature 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
qselT1_Softcopy
CODE --> SQL
qselGII_Softcopy
CODE --> SQL
qselUniWithSoftCopy
CODE --> SQL
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: MS ACCESS UNION ALL with Attachment data type
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
RE: MS ACCESS UNION ALL with Attachment data type
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
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
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
CODE --> SQL
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: MS ACCESS UNION ALL with Attachment data type
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
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: MS ACCESS UNION ALL with Attachment data type
RE: MS ACCESS UNION ALL with Attachment data type
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: MS ACCESS UNION ALL with Attachment data type
RE: MS ACCESS UNION ALL with Attachment data type
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: MS ACCESS UNION ALL with Attachment data type
CODE --> U.IDKey;
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
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: MS ACCESS UNION ALL with Attachment data type
RE: MS ACCESS UNION ALL with Attachment data type
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
Thanks again
RE: MS ACCESS UNION ALL with Attachment data type
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: MS ACCESS UNION ALL with Attachment data type
RE: MS ACCESS UNION ALL with Attachment data type
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
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
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: MS ACCESS UNION ALL with Attachment data type