UNION ALL with Attachment data type 1

[jjMedrano]

thread700-1724615

Good afternoon guys, I'm very new to this.

I was just wondering if anyone can elaborate on what the code would look like for the answer by Programmer dhookom on thread700-1724615.

Any help would be really helpful. Thank you all!

 

[Andrzejek]

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.

 

[jjMedrano]

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

 

[dhookom]

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?

SELECT 
U.[Last Name], 
U.[First Name],
S.[Attachments]
FROM [Students] S Left Join [Union Query] U ON S.SomeUniqueField = U.SomeUniqueField

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

 

[Andrzejek]

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.

 

[jjMedrano]

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.

 

[dhookom]

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:

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

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

 

[jjMedrano]

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.

 

[Andrzejek]

having three tables rather than one

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.