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 Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
UNION ALL with Attachment data type 1
- Thread starter jjMedrano
- Start date
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.
[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.
- Thread starter
- #3
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
(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
-
1
- #4
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
Do you have a primary key field? Last and first names are quite often not unique. What table(s) have the Attachments field?
SQL:
SELECT
U.[Last Name],
U.[First Name],
S.[Attachments]
FROM [Students] S Left Join [Union Query] U ON S.SomeUniqueField = U.SomeUniqueFieldDuane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
- Thread starter
- #6
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.
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
I would try create a single column with an expression like:
SQL:
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
- Thread starter
- #8
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.
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:
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.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question