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!

 

[Andrzejek]

If this SQL works OK for you:

SELECT [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #]
FROM Table1
UNION ALL
SELECT [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #]
FROM [GII Register Data2]

could you show your SQL "with attachments" that causes the error?
And, what error are you getting?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

What are your primary key and attachment fields in Table1 and [GII Register Data2]? Also, you need to save the working UNION query and provide us with its name.

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

 

[Anthony343242]

@Andrzejek - Thanks for your reply, I appreciate it.

Correct that SQL works perfectly and joins the two tables together. However, I did not include my attachments field ([Softcopy]) within that, otherwise I would receive the error "the multivalued field '[Softcopy]' cannot be used in a UNION query. I understand why it doesn't work, but I have seen other posts where people were able to successfully bypass this error and add their attachments using another query I think? I'm just not sure how to execute it.

This is the code that would give me the error (with [softcopy] being my attachments column:

SELECT [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #], [Softcopy]
FROM Table1
UNION ALL
SELECT [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #], [Softcopy]
FROM [GII Register Data2]

 

[Anthony343242]

@dhookom - Thanks for the reply, I appreciate it.

I have left the primary key as "ID" for both tables, where each row is given a number (i.e. 1,2,3,4...). These numbers between each of my tables have no correlation though, I'm just wanting to paste one table on the bottom of another in order to create a continuous form - rather than having multiple forms. The reason I am doing this also is that I have exceeded the 2gb limit on another access file - so I have tried to bypass this by linking the table to a new file, and joining it with my newly created one. Hope this makes sense.

The working union query is saved - though Im not sure what you mean by name? The name of my query is "query3" and I have two other queries "query1" and "query2" that contain each of the tables respectively so I could grab the SQL code from both to paste into query 3.

Hope that isn't too confusing. Thanks

 

[dhookom]

Anthony, Welcome to tek-tips.

Consider using TGML tags to format your posts to make them easier to read. Notice how Andy's post is more readable. Another suggestion is to find and use a good naming convention for tables, fields, queries, and everything else you name in Access. Most of us old mature developers won't use spaces or special characters in object names. A field named ID is rarely a good name since it doesn't suggest the table where it is used. Do yourself a favor and do some reading on this. I think you will love implementing a consistent system.

Create these four queries and see what you get in the final. You may be able to remove the last two columns from the final query.

quniTable1GIIRegister

SELECT "T1" & ID IDKey, [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #]
FROM Table1
UNION ALL
SELECT "GII" & ID, [Folder #], [Part #], [Lot#/ SN], [Inspection Date], [Expiry Date], [SHIP #]
FROM [GII Register Data2];

qselT1_Softcopy

SELECT "T1" & ID AS IDKey, Softcopy
FROM Table1;

qselGII_Softcopy

SELECT "GII" & ID AS IDKey, Softcopy
FROM [GII Register Data2];

qselUniWithSoftCopy

SELECT U.*, NZ(T1.SoftCopy,GII.SoftCopy) AS CSoftCopy, T1.SoftCopy AS T1SoftCopy, GII.SoftCopy AS GIISoftCopy
FROM quniTable1GIIRegister U 
LEFT JOIN qselT1_Softcopy T1 ON U.IDKey = T1.IDKey
LEFT JOIN qselGII_Softcopy GII ON U.IDKey = GII.IDKey

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

 

[Anthony343242]

Thanks for taking your time to reply dhookom! I truly appreciate your help!

I have followed all of your code, but have unfortunately received a couple errors.

For the first query you provided, I received the following error:

I added the "IDKey" onto the second SELECT line code following the "ID" (as you wrote only "SELECT "GII" & ID"). But still returned the same error as attached above.

I then deleted "IDKey" from both select lines and it ran fine for some reason?

Anyway continuing on, I got to your final provided query (this is after deleting the "IDKey" from the initial query as stated above) and I received the following error:

Since I had previously removed the "IDKey" since the initial query ran fine without it, I thought I'd do the same for the final query and remove it- so the bottom 2 lines of code would read U.ID = GII.ID
However, I still received the same error as attached above.


Apologies for the mess, I hope it makes some sense to you. Also, how can I use the TGML? Sorry, I am new to access and the world of coding. Thanks again for your help

 

[Anthony343242]

And just to let you know- FYI this is my first week of using access. So please excuse my horrible explanations and me being so clueless. I appreciate your patience

 

[dhookom]

I forgot the word “as” which is optional in SQL Server but required in Access:


SELECT "T1" & ID AS IDKey

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

 

[Anthony343242]

Thanks dhookom, it now works for the first query.

However, I receive the following error on the last query:

Is there something that I've overlooked?

Thanks again for your patience

 

[combo]

Probably it should be possible to build the last query with visual designer and inspect sql. For multiple joins you need them ordered in hierarchy, in parenthesis, otherwise there are too many JOINS causing error.
Please apply Duane's tip about TGML, images are hard to read and work with. Better to paste and format sql string, the only error message box can be copied with ALT+PrtSc keys.

combo

 

[dhookom]

Combo is correct that posting pictures is both more difficult for you and for me. Please copy and paste the SQL view of the query and use the Code tag [<>]. My syntax was attempted without actually being Access and should work in SQL Server. Try the following for MS Access:

SELECT U.*, NZ(T1.SoftCopy,GII.SoftCopy) AS CSoftCopy, 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]

Hi guys, thanks so much for responding. Apologies for the delayed reply, I have not been in the office over the weekend.

I used your exact code (as below) for the last query Dhookoom, and I received an error as follows:

"The multivalued field 'T1. Softcopy is not valid in the expression 'NZ(T1.Softcopy,GII.Softcopy)'.

I believe this is a similar error to what I had when first attempting to union the tables with attachments.

Any further help would be greatly appreciated, thanks so much for your patience

EDIT: I'm not too sure why my code isnt showing? But it is the same as you posted in the previous post dhookom. Cheers

 

[dhookom]

Try to remove the column with the Nz(). I never use attachment and multi value fields. This enforces my decision.

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

 

[Anthony343242]

By that you mean to not include the attachment column? That's a shame, I thought it could be done. Thanks for your assistance anyway, I appreciate it.

 

[dhookom]

No, just the Nz(). Didn’t I have the attachment fields in other columns?

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

 

[Anthony343242]

Sorry, I don't understand what you mean? The Nz() code was not used in any other queries? Thanks

 

[dhookom]

My post with the Nz() column, simply remove the one NZ() column and leave all the others.

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

 

[Anthony343242]

Sorry, so the code would look like this?

SELECT U.* 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;

 

[dhookom]

Yes, what are the results? Does it work?

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