Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query does not returning the whole data - MS Access

Posted on 2013-01-14
11
Medium Priority
?
325 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

926 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