Solved

MS Access How can I configure my report to stop displaying date dupes?

Posted on 2013-01-04
16
366 Views
Last Modified: 2013-01-08
I have a schedule input with a query that supply's the data for the calendar report.

The query stores the data as follow's in the qryEmpTimeOff query:
etodate      empName
1/30/2013  Patrick
1/30/2013  SpongeBob

Problem:
The report (frmReportByDate) will display the data twice. How can I configure my report or query to display data once under one date?

Please see sample database file.
ExpertsScheduleInputExample.accdb
0
Comment
Question by:DJPr0
[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
  • 5
  • 3
  • +1
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38744896
have you tried setting the Reports textbox property Hide duplicates to "YES"
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38744913
open report in design view
select the textbox and hit F4
look for the property "Hide Duplicate" and set to Yes
0
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 38744970
I am kind of confused..  spongebob and patrick are two distinct employees that would be requesting time off even though they requested the same day off...


HTH,

Kent
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38745047
So then with the suggestions by the previous experts it would look like this:
1/30/2013  Patrick
                   SpongeBob
2/18/2013  Lavern
                   Shirley
                   Lenny
                   Suiggy

...isn't this what you wanted?

If not, then please post a clear graphical example of what you do want.

JeffCoachman
0
 

Author Comment

by:DJPr0
ID: 38745064
Yes, that looks good Jeff.


Applied the "Hide Duplicate" to yes and worked for the date.

Applied the "Hide Duplicate" to yes for the name and reason, and still displays dupes on the report.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38745202
<Applied the "Hide Duplicate" to yes for the name and reason, and still displays dupes on the report. >
This is why we always ask for sample files...

A *Duplicate* is defined as a entry (in one field) that is exactly the same as the row above it.
Not sure how you are sorting or grouping in your report, so it is difficult to say what the problem is, (or if it can be easily worked around)

Can you post a sample, following these steps:
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

...Then I am sure any expert here can help you with this...


;-)

JeffCoachman
0
 

Author Comment

by:DJPr0
ID: 38745511
Jeff,

I already posted the sample database with my initial post.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38745583
LOL!

Yes, you are correct.

;-)


I'm looking at it now...

;-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38745670
OK, so then the type of report you have there now is not the best option.
(I know I made that report, but this contingency was not anticipated...)

You still need to post a clear example of the *exact* output you need, but here is a sample that does what you asked for...

Note, see step 11 in my sample db notes:
Compact the db
... your sample was 7MB.  When compacted, it was 700k (90% smaller after compaction)

Jeff
ExpertsScheduleInputExample.accdb
0
 

Author Comment

by:DJPr0
ID: 38745849
Thanks Jeff.

Seems to be partially working.

When I run qryEmpTimeOff2 I receive some blanks I.E.

Jan 22 2013  (blank) (Spongebob is missing)
Jan 29 2013  (blank) (Spongebob is missing)
                     Patrick

Jan 30 2013  (blank) (Patrick is missing)
                    Spongebob

(qryEmpTimeOff does have the missing records)

Found solution:
Set Hide Dupes to No for EmpName & ReasonText in the Report
0
 

Author Comment

by:DJPr0
ID: 38750950
Thanks Jeff!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38751163
looks like you did not even consider what was posted earlier as the solution..
0
 

Author Comment

by:DJPr0
ID: 38751300
Yes, I did try your solution with my existing report, but it did not work due to the format of my report.

It did work with the report that boag2000 supplied.

Thanks for your input capricorn1.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38751677
I have no issue if you give capricorn1 some points on this.
(Please click the "Request Attention" link and ask that the points be split)
Hide Dupes was indeed first mentioned by him...

All I did was refine your requirements.
I was still not sure if what I posted is what you really wanted...

;-)

Jeff
0
 

Author Comment

by:DJPr0
ID: 38754972
I don't see any Request Attention link.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

688 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