"Hide Duplicates" producing undesireable results i

[sarcasmic]

Hello.

I have a database full of records from different audits that have been conducted. There may be, and often are, multiple records for each place where an audit has been conducted. I am trying to generete a report based on the place audited, and the date that it was audited, however I am having some issues with duplication.

I have tried using the "Hide Duplicates" feature in both the date, and station (place where audits happen) fields, however it's not really filtering them out the way that I would like. The problem seems to be when an audits happen in multiple places on the same day.

Examples-

What I get:

October 2004
AAA 10/4/2004
BBB 10/5/2004
CCC 10/11/2004
DDD 10/25/2004
EEE
DDD
EEE
DDD
EEE
FFF 10/26/2004

Now DDD and EEE are both completed on the same day, which seems to create some undesireable results when I try to "hide duplicates." This is happening throughout the report...if the eval date matches another eval date, then it adds in 2 additional instances (minus date) of the station.

This is what I want my report to produce:

October 2004
AAA 10/4/2004
BBB 10/5/2004
CCC 10/11/2004
DDD 10/25/2004
EEE 10/25/2004
FFF 10/26/2004

Now I've tried taking the "hide duplicates" off of the date field, but then I end up with something like the following for every station:

AAA___10/4/2004
______10/4/2004
______10/4/2004
______10/4/2004


Thanks for your help! Also, let me know if this isn't terribly clear.

 

[VicRauch]

The problem is in your query, not the report. Please copy the SQL from the query that the report is built on, and post it so we can help.

Thanks,

 

[sarcasmic]

The query just pulls those 2 fields (Station and Evaluation Date) from the records. So the query is just two 'columns', with the station in the left and the date in the right.

I'm sorry, but I don't know how to go about copying the SQL from the query.

I built a new report, but this time sorted the results according to the station field, this solved the wacky problem that I was having earlier, however now if two consecutive audits (alphabetically) were completed on the same day, it will only show the date next to the first one...while I'd like to be able to show the date next to both, this isn't a major problem.

 

[VicRauch]

Remove the "Hide Duplicates" from the date field, it will then show on all lines.

To get the SQL code, open the query, then select SQL from the View menu. Highlight everything, and copy it to the clip board (Ctrl C). Then send us a message and to paste it in the message, use Ctrl V.

 

[sarcasmic]

Unfortunately, when I remove the "hide duplicates" from the date field, then it adds a date for every record for each station, which is a mess.
It turns out like:
<pre>
AAA 4/26/2005
4/26/2005
4/26/2005
4/26/2005
4/26/2005
BBB 4/26/2005
4/26/2005
4/26/2005
CCC 4/27/2005
4/27/2005
4/27/2005
4/27/2005
4/27/2005</pre>

If I select "hide duplicates for the date field, then it ends up looking like:
<pre>
AAA 4/26/2005
BBB
CCC 4/27/2005
</pre>

which is slightly annoying...but not the end of the world.

Here is the SQL view of my Query:

SELECT tblFindings.Station, tblFindings.[Evaluation Date]
FROM tblFindings;

 

[VicRauch]

Looks to me that you have multiple records in the table for a station and date. That is why you will get multiple records within one station, with the same date. In order to get just one record per station, per date, try this SQL:

SELECT tblFindings.Station, tblFindings.[Evaluation Date] 
FROM tblFindings Group By tblFindings.Station, tblFindings.[Evaluation Date];

I think you will like getting just one record going into your report for each station/date pair.
HTH,