Solved

Query does not returning the whole data - MS Access

Posted on 2013-01-14
11
320 Views
Last Modified: 2013-01-14
Dear Experts,
I am running a query but that does not return the whole data from a particular field.Actually the data is in the table, its a big one but some how its limiting in the query..can you please help me?
0
Comment
Question by:gtmathewDallas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38775257
see this link for possible causes and workarounds

http://allenbrowne.com/ser-63.html
0
 

Author Comment

by:gtmathewDallas
ID: 38775262
I am running this query for the report, I thought it not showing because of the size of the text box in the report, but its not..
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38775310
did you look at the link i posted above?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:gtmathewDallas
ID: 38775315
Yes I am looking on it Capricorn..
0
 

Author Comment

by:gtmathewDallas
ID: 38775333
Its the query, now my issue is with the field "REVIEW_MAIN.Presentation_Comments"
Please just look on the query too, that may be helpful for me..The other comment fields may get the same issue..I want to test it after fixing it.
Thanks

SELECT DISTINCT REVIEW_MAIN.Review_Id, REVIEW_MAIN.Profile_ID, REVIEW_MAIN.Preparer_Id, REVIEW_MAIN.Reviewer_Id, REVIEW_MAIN.Review_Number, REVIEW_MAIN.Review_Date, REVIEW_MAIN.Additional_Review, REVIEW_MAIN.Final_Review, REVIEW_MAIN.APPI_Review, REVIEW_MAIN.FPPI_Review, REVIEW_MAIN.Punctual_Submission, REVIEW_MAIN.Presentation, REVIEW_MAIN.Presentation_Comments, REVIEW_MAIN.Sub_ledger_Listing, REVIEW_MAIN.Sl_Comments, REVIEW_MAIN.Acknowledge_page, REVIEW_MAIN.Acknowledge_Comments, REVIEW_MAIN.Sl_Reconciliation, REVIEW_MAIN.Sl_Reconciliation_Comments, REVIEW_MAIN.Activity_Confirmation, REVIEW_MAIN.Ac_Comments, REVIEW_MAIN.General_Notes, REVIEW_MAIN.GN_Comments, REVIEW_MAIN.Demographics, REVIEW_MAIN.Current_Year_Projections, REVIEW_MAIN.Future_Year_Projections, REVIEW_MAIN.Current_Year_Prorates, REVIEW_MAIN.Future_Year_Prorates, REVIEW_MAIN.Footnotes, REVIEW_MAIN.Balance_Varience, REVIEW_MAIN.Employee_Listing, REVIEW_MAIN.El_Comments, REVIEW_MAIN.Annual_Pay, REVIEW_MAIN.Annual_Pay_Comments, REVIEW_MAIN.Cost_Center, REVIEW_MAIN.Cost_Center_Comments, REVIEW_MAIN.Subledger_Classification, REVIEW_MAIN.Sub_Classification_Comments, REVIEW_MAIN.El_Balance, REVIEW_MAIN.El_Balance_Comments, REVIEW_MAIN.Bonuses, REVIEW_MAIN.Bonuses_Comments, REVIEW_MAIN.Gp_Comments, REVIEW_MAIN.Gp_Questions, REVIEW_MAIN.FY, REVIEW_MAIN.PQuarter, REVIEW_MAIN.PName_ID, REVIEW_MAIN.Profile_Return, SUB_LEDGER_ATTENTION.Sl_Comments, SUB_LEDGER_ATTENTION.Ac_Num, SUB_LEDGER_ATTENTION.Sl_Id
FROM REVIEW_MAIN LEFT JOIN SUB_LEDGER_ATTENTION ON REVIEW_MAIN.Review_Id = SUB_LEDGER_ATTENTION.Review_ID;
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38775369
you are using DISTINCT in your query, from the link ( see Issue column Uniqueness)


Uniqueness       Since you asked the query to return only distinct values, Access must compare the memo field against all other records. The comparison causes truncation.       

Open the query's Properties Sheet and set Unique Values to No. (Alternatively, remove the DISTINCT key word in SQL View.)

You may need to create another query that selects the distinct values without the memo, and then use it as the source for another query that retrieves the memo without de-duplicating.
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 38775375
Does the field in question cut-off when you simply run the query?  The link that Capricorn1 posted is focused on queries that aggregate data using a Group By clause, which your query does not seem to do.

I have not tested this, but if your Report has a Group, that functionality may cause the same issue.

So, run the query and see if it seems to cut the field off, or whether it is complete.  If it is complete, check the Report to see whether one or more Groups have been created for the report.
0
 

Author Comment

by:gtmathewDallas
ID: 38775430
I think its because of the "Distinct" function in the query, when i am simply running that one table getting the whole data from that comment field, but running the table after joining with the other table not getting the whole memo field..
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38775450
seem my post at http:#a38775369 ?
0
 

Author Comment

by:gtmathewDallas
ID: 38775481
Yes, Thanks a lot Capricon.. I was trying to solve it based on your comment
0
 

Author Comment

by:gtmathewDallas
ID: 38776128
Yes It worked Capricon..Thanks and Thank you Fyed
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

710 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