Solved

Union & Union All Queries, & Truncated Memo Fields

Posted on 2009-07-15
26
923 Views
Last Modified: 2012-05-07
I am developing an Access 2007 database to manipulate products lists from a wholesaler into a CSV file to be imported into a Magento-based ecommerce website. This particular wholesaler provides three separate lists in Excel .xls format - one for each type of product, & one of the fields is a description field, which usually contains a great deal more than 255 characters, & is therefore a Memo rather than a Text field. Initially I just set up a UNION ALL query to join all the data together into one table that I could use as my product list, as UNION ALL rather than just UNION doesn't truncate the description Memo field.

Somewhat annoyingly, the description field contains not only some text which serves as a general description of each product, but also some other information which really should be in separate fields. And of course this information is different for the three types of product. I have therefore created a query for each of these tables (which are actually linked tables pointing at the downloaded Excel .xls files) which replace parts of the description fields with some HTML, so that the information is usefully displayed with headings etc. when it ends up on the website.

If my UNION ALL query works directly on the original linked tables, it works perfectly & doesn't truncate the Memo fields. However, if I try exactly the same query, but working on the queries which manipulate the original data, then the Memo fields get truncated. Why is this truncation happening?

I've tried various things to stop this happening - I've even tried having the UNION ALL query join these description-manipulating queries with the original tables, in the vague hope that it would not truncate the Memo fields, but for some reason it still truncates. So the only way I can have the UNION ALL query not truncating my Memo fields is by pointing to the original tables, but then I can't manipulate the information in those Memo fields as each of the three product types requires different manipulation of the description information.

I know there are other reasons why Access truncates Memo field data, but can't see why it's happening in this case. I can confirm that the three queries which manipulate the description information don't truncate the fields, so it is happening at the UNION ALL query stage, even though UNION ALL is not supposed to truncate.

Does anyone have any idea what's going on, or how I can fix it?
SELECT qryProdType1.[CAT NO], qryProdType1.TITLE, qryProdType1.DESCRIPTION, qryProdType1.CAT, qryProdType1.PRICE

FROM qryProdType1

WHERE qryProdType1.[CAT NO] IS NOT NULL
 

UNION ALL
 

SELECT qryProdType2.[CAT NO], qryProdType2.TITLE, qryProdType2.DESCRIPTION, qryProdType2.CAT, qryProdType2.PRICE

FROM qryProdType2

WHERE qryProdType2.[CAT NO] IS NOT NULL
 

UNION ALL 
 

SELECT qryProdType3.[CAT NO], qryProdType3.TITLE, qryProdType3.DESCRIPTION, qryProdType3.CAT, qryProdType3.PRICE

FROM qryProdType3

WHERE qryProdType3.[CAT NO] IS NOT NULL
 

ORDER BY CAT, [CAT NO];

Open in new window

0
Comment
Question by:PaulCutcliffe
  • 16
  • 8
  • 2
26 Comments
 
LVL 84
ID: 24857758
You could create your union queries without the memo fields, then try (in the final stage) to link back in those memo fields from the original table ... IOW, your qryProdType1, qryProdType2 and qryProdType3 do NOT include those memos. After you build your UNION query, then buld another query (based on that Union query) that links back to the original tables, and include the memo fields. I'm not sure this will work, but it may.
0
 

Author Comment

by:PaulCutcliffe
ID: 24857902
LSMConsulting, I can see your logic - I'll give it a go & report straight back.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24858206
That will work:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21055609.html

If few records only, you can also use DLookup to retrieve the memo field.

/gustav
0
 

Author Comment

by:PaulCutcliffe
ID: 24858638
Okay, now I'm starting to swear at MS Access, even more than I did before.

Firstly I've created a query that performs a UNION ALL on the three tables without the Memo Description fields, which of course presents no problems. Next I've tried to create a query that pulls together the results from this query with the Memo Description fields from the three queries that manipulate the data therein. This presented a problem as I have to select the Description field from three different queries, and I don't know if it combines them into the same column as they have the same name. I thought I'd try it though, but seem to get a Type Mismatch as soon as I try to join any of these tables on the [CAT NO] column, even though they all contain exactly the same kind of data.

So I thought I'd try a separate UNION ALL query to join the result sets from the three queries that manipulate the Description Memo fields, then I would just have to join two result sets together in a normal query, but in that result set, the Description Memo fields are truncated again! Back to square one!

Any idea how I can get around this?
SELECT ProdType1Descriptions.[CAT NO], ProdType1Descriptions.DESCRIPTION

FROM ProdType1Descriptions
 

UNION ALL 
 

SELECT ProdType2Descriptions.[CAT NO], ProdType2Descriptions.DESCRIPTION

FROM ProdType2Descriptions
 

UNION ALL 
 

SELECT ProdType3Descriptions.[CAT NO], ProdType3Descriptions.DESCRIPTION

FROM ProdType3Descriptions
 

ORDER BY [CAT NO]

Open in new window

0
 

Author Comment

by:PaulCutcliffe
ID: 24858714
cactus_data, sadly I have around 1,000 records from one, & around 6,000 each from the other two, so I don't suppose that counts as 'few records', although of course everything is relative!

I'm looking into the other article to see if it suggests anything different from what I've tried - I'll let you know how I get on.
0
 

Author Comment

by:PaulCutcliffe
ID: 24859515
cactus_data I have tried to replicate the logic on the separate article, & but I got as far as the third query, which it called QueryU, & lo & behold the Memo fields are truncated again! So I didn't even get as far as the fourth query.

Why does Access insist on truncating these fields, however I try to do this?
0
 
LVL 84
ID: 24859546
Have you tried giving each CAT NO field a unique name:

SELECT [Cat No] AS CatNoA FROM OneTable
UNION
SELECT [Cat No] AS CatNoB FROM AnotherTable
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24860541
> I got as far as the third query, which it called QueryU, & lo & behold
> the Memo fields are truncated again! So I didn't even get as far as the fourth query.

Well, bad place to stop as it is the fourth query that pulls the untruncated memo fields.

> I have around 1,000 records from one, & around 6,000 each from the other two,
> so I don't suppose that counts as 'few records',

It does. The reason to use DLookup is the simplicity - one query and you are set. If speed is too slow, go for the previous model with discrete queries.

/gustav
0
 

Author Comment

by:PaulCutcliffe
ID: 24860718
No I haven't, but how would that help?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24861162
It retrieves the memo fields in full. I thought that was your goal.

/gustav
0
 

Author Comment

by:PaulCutcliffe
ID: 24862418
Sorry cactus_data, my 'how would that help' was directed towards LSMConsulting's suggestion, which was the latest update when I started typing my response! You are correct, that is my goal, & I will persevere with your suggestion tomorrow.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24864292
Oh - that explains.

/gustav
0
 

Author Comment

by:PaulCutcliffe
ID: 24896471
Hi gustav!

Sorry for the delay, but I've now replicated the query shown at http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21055609.html, but as it is, I get a 'Syntax error in FROM clause' error. I think this is because there is a closing parenthesis missing, as there are five opening ones & six closing ones. I suspect the missing one is just after the word 'FROM', but when I add one there, I get a 'Type mismatch in expression' error. All the fields I'm joining on are of the same type, so I don't understand what's going wrong!

I'll paste in my exact code, so you can see where I'm at, & would be most grateful if you can spot my error- thanks.

By the way, I actually have three sets of products to merge together as you know from my original post, but I've decided to stick to just two for now until I've got the concept right - firstly because it's quite complicated enough with just the two(!), and secondly because Access keeps returning the strangest error on my third query, even though it's identical to the other two - something about: '"Prod3 07.07.09$" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long', which as you've probably guessed means nothing to me, as I've not used anything like that name anywhere! Don't you just love computers?
SELECT 

qryUnionAll.[TableID], 

qryUnionAll.[CAT NO], 

qryUnionAll.[TITLE], 

qryProdType1.[DESCRIPTION] & qryProdType2.[DESCRIPTION] AS DESCRIPTION, 

qryUnionAll.[CAT], 

qryUnionAll.[PRICE] 

FROM 

((qryUnionAll 

LEFT JOIN 

qryProdType1 

ON 

(qryUnionAll.TableID=qryProdType1.TableID) 

AND 

(qryUnionAll.[CAT NO]=qryProdType1.[CAT NO])) 

LEFT JOIN 

qryProdType2 

ON 

(qryUnionAll.TableID=qryProdType2.TableID) 

AND 

(qryUnionAll.[CAT NO]=qryProdType2.[CAT NO]));

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24901929
You have to closely follow the setup from the link or you are in trouble.
If you still receive errors, study the output from the previous queries for records containing Null for CAT No.

/gustav
SELECT 

qryUnionAll.[TableID], 

qryUnionAll.[CAT NO], 

qryProdType1.[TITLE] & qryProdType2.[TITLE] AS TitleAll, 

qryProdType1.[CAT] & qryProdType2.[CAT] AS CatAll, 

qryProdType1.[PRICE] & qryProdType2.[PRICE] AS PriseAll, 

qryProdType1.[DESCRIPTION] & qryProdType2.[DESCRIPTION] AS DescriptionAll

FROM 

  (qryUnionAll 

    LEFT JOIN 

      qryProdType1 

        ON 

          (qryUnionAll.TableID = qryProdType1.TableID) 

        AND 

          (qryUnionAll.[CAT NO] = qryProdType1.[CAT NO])) 

    LEFT JOIN 

      qryProdType2 

        ON 

          (qryUnionAll.TableID = qryProdType2.TableID) 

        AND 

          (qryUnionAll.[CAT NO] = qryProdType2.[CAT NO]));

Open in new window

0
 

Author Comment

by:PaulCutcliffe
ID: 24906306
I've tried your updated suggestion, but still get 'Syntax error in FROM clause', but but still has non-matching pairs of parentheses, and having matched them up, I still get 'Type mismatch in expression'.

I will now go back & check for NULL Cat No values.
0
 

Author Comment

by:PaulCutcliffe
ID: 24906400
I've checked all three of the underlying queries - qryUnionAll, qryProdType1 & qtyProdType2, & none contain any rows with a Null value for the Cat No column. Then again, the first pulls its rows from the other two, & they actually have 'WHERE [CAT NO] Is Not Null', so any records with Null values for Cat No are already removed.

Can you get your query above to work? I can't see where the Type Mismatch is, as all columns are text other than the Memo one, & even mixing text & memo shouldn't cause a type mismatch, should it?
0
 

Author Comment

by:PaulCutcliffe
ID: 24906545
I've just had an idea, although I don't know whether it's at all relevant. In the past, I worked on something that imported a spreadhseet into Access from Excel. Unlike when you import from a text file, you have no control over the type of column that is created in Access, & for some stupid reason probably only known to Microsoft, Access just looks at the first few (I forget how may exactly) records & decides on the data type for all the columns. If the first few records contain only numbers in a particular column, then Access assumes the whole column will only ever contain numbers, as sets it up to take only numeric values. If that column actually contains text data further down the spreadsheet, then the import fails. The really stupid part is that you have no control over this at all, other than to re-order the rows so that at least one text value appears near the top, which of course may not be possible in a real world scenario! Luckily it was for me!

In this case, the values in Cat No for ProdType1 near the top of the data set are all numeric. When creating queries in Access, again you have no control over the data type for each column, so maybe Access uses the same stupid logic & makes the whole column numeric only?

The reason I think this is that when I open the qryProdType1 query in Access, it firstly seems to open correctly, at least the whole screen is filled with values, but within a second or two, I get this error message: 'You cannot record your changes because a value you entered violates the settings defined for this table or list (for example, a value is less than the minimum or greater than the maximum). Correct the error and try again.', after which all the values previously displayed suddenly change to '#Name?'. So it's as if the data shows correctly until it finds the first row which has a non-numeric Cat No value, at which point it raises this error.

I'll have a play & see if I can prove/disprove this theory.
0
 

Author Comment

by:PaulCutcliffe
ID: 24906785
Okay, now I've no idea what the hell it's doing! I added sorting on the CAT NO column, mainly so I could see quickly where, if anywhere, there were letters as well as numbers, but then the error stopped happening. Then I removed the sorting, & the error still didn't happen. Then I closed & re-opened the database, & it started raising the error again. So then I wrapped a Cstr() around the [CAT NO], thinking that this would force a string conversion on any number values, & leave text values unaffected. But now I get, after a second or two, 'Invalid use of Null'. What Null?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24909441
Sorry, corrected the parentheses.
Seems like a Null exists somewhere.
You may be able to get around it as shown.

/gustav
FROM 

  (qryUnionAll 

    LEFT JOIN 

      qryProdType1 

        ON 

          (qryUnionAll.TableID = qryProdType1.TableID) 

        AND 

          (Nz(qryUnionAll.[CAT NO]) = Nz(qryProdType1.[CAT NO]))) 

    LEFT JOIN 

      qryProdType2 

        ON 

          (qryUnionAll.TableID = qryProdType2.TableID) 

        AND 

          (qryUnionAll.[CAT NO] = qryProdType2.[CAT NO]);

 

Open in new window

0
 

Author Comment

by:PaulCutcliffe
ID: 24917304
Thanks.

I presume you meant to put in the Nz() around the last line too? But now I get no rows returned at all!

Sorry, this is starting to be a real pain, isn't it?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24920054
Yes, that can't be true.
You'll have to debug the queries and their output closely.

/gustav
0
 

Author Comment

by:PaulCutcliffe
ID: 24922860
Is there a query debugging facility, or do you mean that I'll just have to debug them by modifying them & testing them very carefully?
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 333 total points
ID: 24923232
Yes, go back to the first queries, (re)create them as simple as possible, study the output carefully, create the next and so on.
Eventually, get rid of Cat No as the primary key and add an AutoNumber to the table which is what is used in the model on the linked question/solution. Remember to correct for the parentheses.

/gustav
0
 

Author Comment

by:PaulCutcliffe
ID: 24923308
Okay, thanks. I'll give it a go.
0
 

Author Comment

by:PaulCutcliffe
ID: 25395462
The truth is, I couldn't get this to work, and don't want to delete the question, but am now being forced by Experts Exchange to Accept or Delete it, so I'm accepting it.
0
 

Author Closing Comment

by:PaulCutcliffe
ID: 31603633
The truth is, I couldn't get this to work, and don't want to delete the question, but am now being forced by Experts Exchange to Accept or Delete it, so I'm accepting it.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now