UNION ALL with Attachment data type 1

The way I would take it is to create a query without the Attachment field:

[pre]
qryMyQuery
Select ….
From
UNION ALL
Select ….
From
UNION ALL
Select ….
From
[/pre]
And then use this query in your ‘regular’ SQL like any other table:

[pre]
Select …
From tableA Join qryMyQuery …
[/pre]



---- Andy

There is a great need for a sarcasm font.

This is what I'm trying. I've simplified it so it's easy to follow.

(Union Query)
SELECT
[Last Name],
[First Name]
FROM [Students]

UNION ALL

SELECT
[Last Name],
[First Name]
FROM [Students Archive 2-4 years old]

UNION ALL

SELECT
[Last Name],
[First Name]
FROM [Students Archive 4-6 years old];


(Add Attachments Query)
SELECT
[Last Name],
[First Name],
[Attachments]
FROM [Students] Left Join [Union Query]

Result: Syntax error in FROM Clause

Your FROM clause would require an "ON".

Do you have a primary key field? Last and first names are quite often not unique. What table(s) have the Attachments field?



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

jjMedrano, don't you have a query builder in Access to help you with the correct syntax? It looks something like this where you can drag-and-drop your tables and queries and see the SQL:





---- Andy

There is a great need for a sarcasm font.

Thank you dhookom and Andrzejek

Sorry I'm going back and forth on this...

But this worked out great but unfortunately All Tables have attachments.
Is there a way to combine all attachments into one column? I have a form with a button that opens attachment management.

I'm not sure about combining the attachments into a single column. It might be the price you pay for having three tables rather than one.

I would try create a single column with an expression like:

I'm not optimistic that Nz() works with an attachment field. You may need to run append queries to add all of the records into a temporary single table.

BTW, please copy and paste the SQL statement rather than giving us an image.

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

Nz(A.Attachments,Nz(B.Attachments,C.Attachments))

Thank for the advice but like you predicted, it did not work.

Unfortunately since SharePoint has a limit of 5000 items per list, so we are forced to move (archive) student files automatically using Microsoft Flow.
It just would be nice if all lists
A [students]
B [students archive 2-4 years old]
C [students archive 4-6 years old]
would all be searchable from one Form in Access. Which is, thanks to UNION ALL and LEFT JOIN.
But if a particular student is in B but the Access Form's control source is A.Attachments. It'll come back with an error, obviously because the student's attachments are in the B.Attachmetns column not int the A.Attachments column

I tried "=Nz(A.Attachments,Nz(B.Attachments,C.Attachments)) " in the Access Form's control source as well, it did not work.

I'll look into how running append queries to a single table works. Thanks for the suggestion.

Duane said:

having three tables rather than one

Click to expand...

Consider this:

One more table:
[pre]
tbl_X
ID Description[blue]
0 [/blue] Regular Student[blue]
1 [/blue] Archive 2-4 years old[blue]
2 [/blue] Archive 4-6 years old[blue]
3 [/blue] ....[/pre]

And instead of 3, you have just one table (with Attachments)
[pre]

Students
[Last Name] [First Name] Attachments ... FieldX
Brown Jack [blue]0[/blue]
White Susie [blue]1[/blue]
Presley Elvis [blue]2[/blue]
...
[/pre]


---- Andy

There is a great need for a sarcasm font.