Track Unique IDs

[Newbie11]

Is there a way to to count unique ID occurences in a report? I.E. recording how many employees are on a report even though the same employee may be listed more than once. I've seen some suggestions about including a header or footer to do a count but I would much rather like to have a text box field in the header of the report.

[June7]

I tried the approach of group section on ID and then a Sum in report footer. Doesn't work.

Try a subquery in the report RecordSource which did work for me:

DistinctIDcount: (SELECT COUNT(*) AS TotIDs FROM (SELECT DISTINCT ID FROM queryname))

My test was with an unfiltered query as RecordSource.

Any other solution will involve VBA code.

Review

http://allenbrowne.com/ser-66.html

http://www.dbforums.com/microsoft-ac...-database.html

[Newbie11]

I spent sometime working with the Allen Browne code you suggested and just can't seem to get it right. I'm working with the field EmployeeID from the table tblEmployeeInfo. The module I created based on the distinct count code is called the Employee Count. Essentially what I would like to have happen is have the txtEmployee field in the qryOrderShippment report count how many employees there are. Either I'm not inserting the code correctly or comprehending what the code is asking for.

[June7]

First, the data sources in qryOrderShippment appear to be joined on wrong fields. Shouldn't it be EOID field from both tables?

Second, I think this can be done without the VBA function. I tried the nested sql with the filtered query and it seems to work. Make the report RecordSource this sql statement and bind textbox to DistinctIDcount:
SELECT qryOrderShippment.*, (SELECT COUNT(*) AS TotIDs FROM (SELECT DISTINCT EmployeeID FROM [qryOrderShippment])) AS DistinctIDcount
FROM qryOrderShippment;

Ooops, spoke too soon. The query works but report doesn't like it. The report accepts the following:

1. qryOrderShippmentEmpDistinct
SELECT DISTINCT qryOrderShippment.EmployeeID FROM qryOrderShippment;

2. qryOrderShippmenEmpCount
SELECT Count(qryOrderShippmentEmpDistinct.EmployeeID) AS CountOfEmployeeID FROM qryOrderShippmentEmpDistinct;

3. sql statement for report RecordSource
SELECT qryOrderShippment.*, qryOrderShippmentEmpCount.CountOfEmployeeID FROM qryOrderShippmentEmpCount, qryOrderShippment;

You are not using the VBA function correctly. I can get it to work if I remove the parameter prompt from the query. I never use query parameter prompts because they annoy me when I am debugging and can't validate user input. I always have input on form and query refers to controls on form for parameter or use VBA to construct WHERE argument of DoCmd.OpenReport (or OpenForm). If you want to try the VBA some more, need to go back to Allen's original code exactly as it is in the article, use a form for date parameter input and then call the function from textbox ControlSource with:
=ECount("EmployeeID","qryOrderShippment","",True)
Criteria argument referencing form input:
=ECount("EmployeeID","qryOrderShippment","[Date]=#" & Forms!formname.datetextboxname & "#",True)

[Newbie11]

Thank you so much. I tried your solution and it works great. I'm really not a fan of the paramaters either since if you enter the date correctly there's no way to tell that you did except getting an error message and the report doesn't detail what the paprameters were that generated the report.

The problem is-is that for that one report I have four different versions : 1) one that groups by employee 2) one that groups by payment 3) one that groups by item and 4) one that groups by status (i.e. the report shows just the orders for the employee entered in the parameter, or payment entered in the parameter, etc..)

As I get further into building the reports I need I will probably always have four different "views" of the same report. I just don't want to creater that many more forms to filter the info

[June7]

I doubt you need a specific form for each report. One form should serve as parameter input for all 4 versions of report.