?
Solved

Query does not returning the whole data - MS Access

Posted on 2013-01-14
11
Medium Priority
?
327 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 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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 2000 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 49

Expert Comment

by:Dale Fye
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

588 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