Count Distinct in Reports 1

[khwaja]

I will try to explain this to the best of abilities. But if it is not clear, please let me know.

I have a report designed to show details of number of hours put in by each worker in each week. So there are two table with one2many relationship with the first table showing workers' names and the second capturing number of hours each week. I use a week ending date to create new records in the sub form for each employee.

Creating a report grouped on workers and showing total number of accumulated hours is not a problem. However, I need to be able to show that these number of hours are for X number of weeks data has been entered somewhere in the report. Using count function in the detail or groups does not help as it counts records but not unique occurance of dates. So far I have captured weeks' data and as such I should be able to show this somewhere in the report.

Is there any way I can show a distinct Count of number of weeks data entered so far (I am aware that DistinctCount as a function cannot be used being DAO specific). Will appreciate if someone could help.

Cheers

AK

 

[randysmid]

Hi,
I wrote an FAQ on how to use the DSum function to provide totals based on criteria. You can modify this for the DCount function by simply replacing DSum with DCount. Here is the FAQ: faq703-3066 HTH,
Randy Smith
California Teachers Association

 

[khwaja]

Thank you Randy for you advice. I am not an expert but I am fairly comfortable using domain functions. Yor FAQ is very good. I cannot use dcount function as the last part where you enter criteria could become quite messy. I am reproducing an excerpt from my underrlying query for the report for your info.

Designe Weekending TTotal
Chris 22-Mar-03 0.5
Chris 28-Mar-03 1
Emma 22-Mar-03 3
Emma 28-Mar-03 0.5
Grace 22-Mar-03 8
Grace 28-Mar-03 2

I need to be able to show total number of hours by each designer (which is easy enough) but since I started capturing hours from 22 March on weekly basis, I need to show somewhere how many weeks worth of data does this represent. As you can see there are at the moment two weeks data entered. Even if I use a Dcount function, how would I extract count of weeks and what would be the syntax in the criteria part of the formula? Any ideas?

Cheers

AK

 

[randysmid]

Hi,
I have an FAQ that should take you to the next level. It sounds like you can use "Sorting and Grouping" based on separate weeks. Here is the link to my FAQ on that: faq703-2980
This FAQ will show you how to group based on week. I guess the next question is whether you want the entire report be sorted by person first, then week next, or vice versa, where the week is the top layer sorted, then the person. Of course, your report can have a number of Group levels. HTH,
Randy Smith
California Teachers Association

 

[khwaja]

Randy

As usual you have been a great help. I read your all FAQs before replying and including the one you just indicated. Meanwhile, trying to find a solution myself, I came up with something similar to the following which gives me the answer I need without having to create an additional grouping level.

=DateDiff("ww",#15/03/03#,DMax("weekending","qryTimesheetCalc&quot)

Use of DMAx in conjunction with DateDiff has done the trick.

Thanks a lot for your kind help.

Cheers

AK

 

[randysmid]

AK,
That is a very interesting bit of code you posted. When I get a chance, I will load it into one of my databases and experiment with it. Thanks for sharing the results of your own experiment. HTH,
Randy Smith
California Teachers Association