Count Distinct

[Newbie11]

I have a reprot in which I am trying to count distinct customers.

I have form filter that limits the recrodset of a reprot by date and ordering facility. On the reprot I have a field Total Customers that I would like to count specific customers. For instance if in the form filter I select 04/01/2011 and 04/30/2011 as start and ending dates and select 2 as the ordeirng facility, the field should be populated with the number 2 although there were 4 orders because there are two unique customers.

I am struggling to get this to work and continously recieve a 3061 error using allen browns ECount module. I have uploaded the database to this post. Any help would be greatly appreciated.

[June7]

I am surprised that Allen Browne code errors.

The error is triggered by:

Set rs = db.OpenRecordset(strSql)

I have no idea why this line is demanding three arguments.


I tried very simple procedures and they fail.

Public Function GetCount()
Dim db As dao.Database
Dim rs As dao.Recordset
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT DISTINCT MRN FROM qsum_ReferralStatusFacility;")
GetCount = rs.RecordCount
End Function

Procedure based on working syntax I routinely use.
Public Function GetCount()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT DISTINCT MRN FROM qsum_ReferralStatusFacility;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
GetCount = rs.RecordCount
End Function

I copy/pasted Allen Browne's code into my existing Access2007 accdb and it runs without error. I should think your mdb would work running under 2007 but doesn't. I imported all your objects into an Access2007 accdb but get same errors - very odd. Sorry, don't know what needs to be done to make your db work with this code.

Only alternative I can see is to build a query that selects DISTINCT records in qSumReferralStatusFacility and then do a DCount domain aggregate calc on that query in textbox.
SELECT DISTINCT MRN FROM qsum_ReferralStatusFacility;
=DCount("MRN","Query1")

You have #Num! error in other textboxes because calculation is division by zero. One way to deal with:
=[txtDeclines]/IIf([txtAcceptances]=0,1,txtAcceptances)

[Newbie11]

Thanks for your reply. I tried your suggestion and it worked great.

I'm looking at making one more addition to this report that I can't figure out. I'll try to explain the best that I can I would like to also include on the report not only including orders for the specified date range but placements as well. The main report will stand as is but I would also like to include in each date footer the same information as in the page header and detail but list the info for those orders that have a placement in the specified date range .

For instance....if I ran a report 01/01/2011-06/01/2011 for Order Facility 10 there will be four orders that will appear... 1 for Kindle and 3 for Cotton. Kindle's order was placed/completed in Feb and one of Cotton's was placed/completed in April. What I would like to appear in the respective month's info is any placement information that occured during the month as well. So in April Cotton will have ordering info for their 4/14 order but somewhere in a footer will record that their 3/30 order was placed on 4/5.

I hope this makes sense. I've included an updated version of my db to make clear out the previous error.

[June7]

Sounds like a subreport with the form parameters applied to the PlacementDate field should do the trick.


BTW, Date is a reserved word and should not use reserved words as names. It is not very descriptive either. Date of what? Better would be DateOrder.

[Newbie11]

I made the date change to Order Date...good call.

I'm not sure I know where to put the subreport so that it will line up as such:
Jan Order
Jan Placement
Feb Order
Feb Placement....

Currently I have a included a subreport using the same record souce (qsum_ReferralStatusFacility) in the OrderDate Footer under the various textboxes. I don't comprehend "with the form parameters applied to the PlacementDate". I am looking the property box of the placement filed and cannot set parameters on it.

[June7]

Use the date parameters input on form as filter criteria for the subreport RecordSource same way as used as filter criteria for the main report RecordSource.

Might need to create some Grouping on the report. Group by Customer and Month then put the subreport in the Month group footer. Subform container Master/Child links would be on the customer ID.

[Newbie11]

I implented the sub form and am getting a report as such:

Jan Orders
-Jan Placements
-Feb Placements
-March Placements
Feb Orders
-Jan Placements
-Feb Placements
-March Placements...
....

The results of the subform in each month include every placement that was made during the specified time range and does not limit it by month.
I'm not really concerned with grouping by customer just month only.

[June7]

Create fields in queries that extract Month and Year from ReferralDate and PlacementDate. Use the RefMoYr field for grouping on the report. Link the subreport to the main report by setting the subreport control Master/Child links to these MonthYear fields. Put subreport in RefMoYr group footer.

In Referral query:
RefMoYr: Format([OrderDate],"mmyyyy")

In Placement query:
PlaceMoYear: Format([PlacementDate],"mmyyyy")

[Newbie11]

This worked wonderfully! Thanks for your help. You Rock!