Different results from similar queries

S

ssutton503

I have a table in which there was a Text field called 'txtPressNote'.
Originally, I set it to 250 characters but recently changed it to a Memo
field because 250 characters wasn't enough. I added more characters into the
memo field of a particular record but my existing query still only pulled
some of the info from the memo field. Here's the query:

SELECT DISTINCT tblJobTickets.JobNumber, tblJobs.intJobID,
tblJobs.txtJobName, tblJobs.txtJobDesc, tblJobs.intQuantity, tblJobs.intFold,
tblJobs.booLip, tblJobs.txtColor, tblJobs.txtPressFinishing,
tblJobs.txtPressNote, tblJobs.booMail, tblJobs.booInsert,
tblJobs.txtInsertDesc, tblJobs.txtBindNote, tblJobs.txtDestination,
tblJobs.dtBindery, tblJobs.dtCustomer, tblJobs.dtEntered, tblJobs.dtModified,
tblJobs.Status, tblJobs.txtUsername, tblJobs.intCustomerID,
tblJobs.intStatusSort, tblJobs.dtFilesDueIn, tblJobs.booPostOvers,
tblCustomers.txtLastName, tblCustomers.txtFirstName, tblCustomers.txtCompany,
tblCustomers.txtWorkPhone
FROM (tblJobTickets RIGHT JOIN tblJobs ON tblJobTickets.intJobID =
tblJobs.intJobID) INNER JOIN tblCustomers ON tblJobs.intCustomerID =
tblCustomers.intCustomerID
WHERE (((tblJobs.intJobID)=4817));


As I metioned earlier, the field in question is 'tblJobs.txtPressNote'. The
query above retrieves this for that field:

HOLD THESE COPIES @ TWO FOR SHIPPING AT LATER DATE
LAS VEGAS HILTON dist. Copies (1/2 fold) 400 Leave on skid (DO NOT POLY BAG
OR BOX FOR NOW) - These copies insert into the middle of the Nov. Main issue,
polybags, boxes & ships to Mailings Unlimited. Ad

This query:

SELECT tblJobs.intJobID, tblJobs.txtJobName, tblJobs.txtPressNote
FROM tblJobs
WHERE (((tblJobs.intJobID)=4817));

retrieves the entire information:

HOLD THESE COPIES @ TWO FOR SHIPPING AT LATER DATE
LAS VEGAS HILTON dist. Copies (1/2 fold) 400 Leave on skid (DO NOT POLY BAG
OR BOX FOR NOW) - These copies insert into the middle of the Nov. Main issue,
polybags, boxes & ships to Mailings Unlimited. Adding more and more text.
When will it stop?
I even put in a carriage return. WOW!!
Can I still keep typing? I guess so.

Does anyone have an explanation? As always, thanks for your time.
 
J

Jeff Boyce

I seem to recall seeing something about how applying any format to the memo
field (at table or query level) caused it to only display the first 250
characters.

Does your field have any formatting defined?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
D

Duane Hookom

I would also try removing the word DISTINCT.

SELECT tblJobTickets.JobNumber, tblJobs.intJobID,
tblJobs.txtJobName, tblJobs.txtJobDesc, tblJobs.intQuantity, tblJobs.intFold,
tblJobs.booLip, tblJobs.txtColor, tblJobs.txtPressFinishing,
tblJobs.txtPressNote, tblJobs.booMail, tblJobs.booInsert,
tblJobs.txtInsertDesc, tblJobs.txtBindNote, tblJobs.txtDestination,
tblJobs.dtBindery, tblJobs.dtCustomer, tblJobs.dtEntered, tblJobs.dtModified,
tblJobs.Status, tblJobs.txtUsername, tblJobs.intCustomerID,
tblJobs.intStatusSort, tblJobs.dtFilesDueIn, tblJobs.booPostOvers,
tblCustomers.txtLastName, tblCustomers.txtFirstName, tblCustomers.txtCompany,
tblCustomers.txtWorkPhone
FROM (tblJobTickets RIGHT JOIN tblJobs ON tblJobTickets.intJobID =
tblJobs.intJobID) INNER JOIN tblCustomers ON tblJobs.intCustomerID =
tblCustomers.intCustomerID
WHERE (((tblJobs.intJobID)=4817));

If this doesn't fix the issue, it's important to identify if the text is
truncated in the query or in the report.
 
S

ssutton503

It was indeed the use of the DISTINCT clause that was the problem.
Unfortunately, I kind of need that in there. If anyone wants to explain WHY I
would love to read it. At least I know the cause of the problem and I can
move on. Thanks guys.
 
D

Duane Hookom

Can we assume there might be multiple JobTickets to a single Job? I guess I
would question why JobNumber is in the table tblJobTickets? I would think
this would be stored in tblJobs.
 
J

John W. Vinson

It was indeed the use of the DISTINCT clause that was the problem.
Unfortunately, I kind of need that in there. If anyone wants to explain WHY I
would love to read it. At least I know the cause of the problem and I can
move on. Thanks guys.
Click to expand...

DISTINCT requires that Access compare the values of every field in every
record to remove any duplicate records. If you're comparing MEMO fields in two
records, Access trims them to 255 bytes; in principle a MEMO field could
contain billions of bytes, and comparing a billion byte field in one record to
another billion byte field in a different record... for potentially millions
of records... is simply too expensive an operation.

Do you in fact have records which are identical with the exception of a
difference somewhere in this memo field? If not, why do you need the DISTINCT?
 
S

ssutton503

To John and Duane,

Please don't ask me why I did what I did. I have no idea! :-O

Seriously, I have taught myself Access and have muddled through this
particular project without ever really thinking about why I build things the
way I do. I have reached a point with this project where it seems the company
likes what we can do with what I built so now I am in the progress of
rethinking the entire database design and information storage. Here's hoping
I do it better the second time.

To answer your questions, there are multiple Job Tickets for the same Job
(the individual pieces that make up the whole) and I really have no idea why
I put in the DISTINCT. The funny thing is, I like the way things work better
without the DISTINCT clause in there. Go figure.

I would like to thank you guys again. I appreciate all the advice I have
gotten from the people in these forums.

Steven
 
Top