Solved

Query does not returning the whole data - MS Access

Posted on 2013-01-14
11
317 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
  • 6
  • 4
11 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 38775310
did you look at the link i posted above?
0
 

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
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.

 
LVL 119

Accepted Solution

by:
Rey Obrero 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 47

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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

895 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

17 Experts available now in Live!

Get 1:1 Help Now