Query truncates data

  • Thread starter Thread starter Jake
  • Start date Start date
J

Jake

Hello,
I've created a query that contains two queries, with a left outer join. The
query duplicates values if I don't set the query properties to Unique
values. However, when I do this the query truncates the data in two memo
fields. I've never seen this before. Here is the SQL statement:

SELECT DISTINCT qryAWM525CC130Spec.strSerialNumber,
qryAWM525CC130Spec.strAWM525para, qryAWM525CC130Spec.memAWM525Requirement,
qryAWM525CC130Spec.memRecommendation, qryAWM525CC130Spec.memRemarks,
qryDisplayCC130SpecInfo1.strCC130SpecPara,
qryDisplayCC130SpecInfo1.memCC130SpecStatement,
qryAWM525CC130Spec.lngTenCount
FROM qryAWM525CC130Spec LEFT JOIN qryDisplayCC130SpecInfo1 ON
qryAWM525CC130Spec.strCC130SpecPara =
qryDisplayCC130SpecInfo1.strCC130SpecPara
ORDER BY qryAWM525CC130Spec.lngTenCount;

Thanks in advance for any help you can provide.
Jake
 
Hi,


DISTINCT truncates memo to 255 characters since you ask to compare memo
between themselves. By design, as soon as you add DISTINCT, your memo are
truncated.

If all your memos are from qry...130Spec, change the qry...130SpecInfo1 so
that this, and only this query got the DISTINCT, while you remove the
DISTINCT from the actual query. Do the reverse if the memo come from
130SpecInfo1, but to qry...130Spec. As long as one of the two implied
queries do NOT have duplicated strCC130SpecPara value, you should not have
"extra" records in the result, so, no need to apply DISTINCT at all.



Hoping it may help,
Vanderghast, Access MVP
 
Hi,
Thank you for your advice. But the query is still unsuccessful. I believe
it is because the implied queries do have duplicated CC130SpecPara values.
Would you have any other suggestions?
Thanks again,
Jake
 
Hi,

The solution would be to remove the duplicated values from ONE of the
two queries. If you are lucky, that is a matter to open one, and add a
DISTINCT right after its SELECT. If you are not lucky (because that will
truncate your memo), and you can't "dig further down" with the same idea,
you can try ... digging "up". That would be possible if your tables (with
the memo) do have primary key. IF this is the case (and it should, if the
tables are well designed), start by bringing those pk (primary keys), with
DISTINCT:



SELECT DISTINCT qryAWM525CC130Spec.pk As pk1,
qryDisplayCC130SpecInfo1.pk As pk2

FROM qryAWM525CC130Spec LEFT JOIN qryDisplayCC130SpecInfo1
ON qryAWM525CC130Spec.strCC130SpecPara =
qryDisplayCC130SpecInfo1.strCC130SpecPara


(that is almost the query you presented, but I just SELECT the primary keys
of the involved tables/queries). You claim that this query do the job right
(your problem was just about memo being truncated, so I assume that there is
no "duplicated" problem, once the DISTINCT is applied... and here it is
applied just to the primary key). I assume that this observation still hold,
here too.
Save that query, qu1. Next, you should probably see what I am leading you
right now. Yes, having the primary key, it is a child play to get back the
whole record... by definition of what a "primary key" is:


-----------------------------------
SELECT a.*, b.*

FROM (qu1 INNER JOIN qryAWM525CC130Spec As a
ON qu1.pk1=a.pk)
INNER JOIN qryDisplayCC130SpecInfo1 As b
ON qu1.pk2=b.pk

ORDER BY a.lngTenCount
------------------------------------

That query does not use DISTINCT, so, no problem with truncated memo. ( I
use alias for your queries, a and b, in order to make the SQL less
"indigestable"..., no other reason).




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top