×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

UNION ALL with Attachment data type

thread700-1794175

UNION ALL with Attachment data type

RE: UNION ALL with Attachment data type

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

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

Select …
From tableA Join qryMyQuery …


---- Andy

There is a great need for a sarcasm font.

RE: UNION ALL with Attachment data type

(OP)
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

RE: UNION ALL with Attachment data type

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?

CODE --> sql

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

RE: UNION ALL with Attachment data type

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.

RE: UNION ALL with Attachment data type

(OP)
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.


RE: UNION ALL with Attachment data type

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:

CODE --> 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

RE: UNION ALL with Attachment data type

(OP)
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.

RE: UNION ALL with Attachment data type

Quote (Duane)

having three tables rather than one

Consider this:

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

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

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


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close