MS ACCESS UNION ALL with Attachment data type 1

[Anthony343242]

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:

https://www.tek-tips.com/viewthread.cfm?qid=1724615#post

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!

 

[Anthony343242]

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

 

[dhookom]

The error message suggests you removed too many commas. What do you get with this 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

 

[Anthony343242]

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]

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]

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]

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]

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]

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]

I never use hyperlink fields.

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

 

[Anthony343242]

No worries, thanks for your time