Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MS ACCESS UNION ALL with Attachment data type 1

Status
Not open for further replies.

Anthony343242

Programmer
Feb 4, 2021
16
0
0
AU
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:
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!
 
Unfortunately not, I receive the error "syntax error in query expression 'U.*T1.Softcopy'. :(
 
The error message suggests you removed too many commas. What do you get with this SQL:

SQL:
SELECT U.*[highlight #FCE94F],[/highlight]  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
 
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.
Screenshot_2021-02-08_160049_hat3fe.png


Thanks again
 
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
 
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!
 
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
 
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!
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top