Counting only distinct records 2

[zevw]

I have a report which displays all the payroll checks that the employees received this week. I want to know the count of employess I have on this report, and not the number of checks received

when I write

=Count([EmpId])

I get the number of checks, I would like to get the number of (like DISTINCT in SQL) employees, even though one of them got 3 checks.

How can this be done?

 

[PaulBricker]

Well, one way to do this is to group by the employee with a group header. Then in the header you can add an addtional textbox with a control source set to =1, the running sum set to Over All and the visible property set to No. Then in the Report Footer, put another textbox and set the control source to =HiddenTextboxName, where HiddenTextboxName is the name of the textbox in your Group header. This will give you a distinct count of employees.

Paul

 

[zevw]

Wow!

I like this idea and it works fantastic. I just have one problem that they want to sort it by check number and not by ID. In that case it gives me the employee count the same count as check numbers. What can I do, to still get the number of employees.

Thanks again

 

[PaulBricker]

You can try this.
Query1

SELECT [tblName].[EmpID]
FROM [tblName]
WHERE ((([tblName].[DateCheckIssued]) Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+8))
GROUP BY [tblName].[EmpID];

You may have to adjust the Where clause to get your check week set up exactly. Save this as whatever name you want. I'll call it Qry1. Then create another query like this

SELECT Count(Qry1.[EmpID]) AS [CountOfEmpID]
FROM Qry1;

This should give you the value you are looking for. Then in the report you will use a DLookup function to put this number into a textbox on the report.
The control source would be
=DLookup("CountOfEmpID", "Qry1")

Paul

 

[monkeysee]

Paul,

I have a similar problem. My db tracks inspections on food facilities. I want to build a report that gives the total of facilities that have scored 100, 99, 98, etc

I have a query which is:

SELECT Food.FacilityID, QryLastScores.LastOfInspectionDate1, QryLastScores.Score, Food.BusinessName, Food.Status
FROM (Food INNER JOIN QryLastScores ON Food.FacilityID = QryLastScores.FacilityID) INNER JOIN Address ON Food.AddressLocationID = Address.AddressLocationID
WHERE (((QryLastScores.LastOfInspectionDate1) Between [From mm/dd/yyyy] And [Thru mm/dd/yyyy]) AND ((Food.Status)="Active"))
ORDER BY QryLastScores.Score DESC , Food.BusinessName;

In my text box on the report I have: =Count(Abs([score]="100")) in the control source which is not giving me the correct count who have scored 100. What am I missing?

 

[PaulBricker]

I think the expression you are looking for is

=Sum(Abs([Score]="100"))
Summing 0's and 1's is the same thing as counting.

That should do it for you.

Paul

 

[monkeysee]

Paul
Thanks! That did the Trick and here's a star!

But I do have a problem now when running the report. I get
numerous pages of the same info. I have an idea why... For example, if I have 200 facilities that had a score in the time frame I choose, then I get 200 pages.

I need to keep the FacilityID in the query's grouping, as any facility may have more than one score during the year.

I have looked in the report settings to see if there is a way I can just have one page print out, but cannot locate any "tweak" I can set. I know I can set that in the print dialogue box to print just the one page, but if another user prints this report, they'll get 200 pages. Is there a way we can write some code to tell the printer to only print the one page? Or is there something I can do with the query?

Here is my query:
SELECT Inspections.FacilityID, Max(Inspections.InspectionID) AS MaxOfInspectionID, Max(Inspections.InspectionDate) AS MaxOfInspectionDate, Last(InspectionScores.Score) AS LastOfScore
FROM Inspections INNER JOIN InspectionScores ON Inspections.InspectionID = InspectionScores.InspectionID
GROUP BY Inspections.FacilityID
HAVING (((Max(Inspections.InspectionDate)) Between [From mm/dd/yyyy] And [Thru mm/dd/yyyy]) AND ((Last(InspectionScores.Score)) Is Not Null));

thanks again for your help

 

[PaulBricker]

There is a PrintOut Method that allows you to print just certain pages or just one page in your case.

DoCmd.PrintOut

is the command. You can use it in the same code that opens your report.

DoCmd.OpenReport.....arguments
DoCmd.PrintOut.....arguments

That may be all you need. If not, we can look closer at your query.

Paul

 

[monkeysee]

Paul,

PrintOut seems to be what I am looking for. However, I'm not very good at VB, this is what I have and it isn't working. Where am I wrong?

DoCmd.PrintOut(PrintRange, acPages, [PageFrom]=1,[PageTo]=1)

Thanks again for the help! It is really appreciated.
Deb

 

[PaulBricker]

Try this

DoCmd.PrintOut acPages, 1,1

That should do it.


Paul

 

[monkeysee]

Paul,
Thanks again for your help.
I tried what you suggested:

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim stDocName As String

stDocName = "Rpt_ScoreCount"
DoCmd.PrintOut acPages, 1, 1

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub

What I am now, is a printout of the switchboard where the button resides. Wierd! That also explains why I am not getting the parameter box (asking for date range)since it is not printing the report.

????
Hope you can help!, thanks Deb

 

[PaulBricker]

You don't have the report open yet.

Dim stDocName As String

stDocName = "Rpt_ScoreCount"
DoCmd.OpenReport strDocName, acViewPreview
DoCmd.PrintOut acPages, 1, 1
DoCmd.Close acReport, strDocName, acSaveYes

Try that and let me know.


Paul

 

[monkeysee]

Ok, this is what I have now:

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim stDocName As String

stDocName = "Rpt_ScoreCount"
DoCmd.OpenReport strDocName, acViewPreview
DoCmd.PrintOut acPages, 1, 1
DoCmd.Close acReport, strDocName, acSaveYes


Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub

Error message comes back:
The action or method requires a report name argument

Deb

 

[PaulBricker]

I'm not sure. That code runs fine for me. Which line does it stop on? Also, I do not have parameters in the underlying query for my report. Try taking the parameters out, and see if it runs. If that's it, then we need to look at how the values are getting input into the query.

Paul

 

[monkeysee]

Paul!

I got it to work! In checking the spelling of commands to other codes I have, I found that we mispelled the stDocName!
we had it as strDocName - so I changed that and it works great!

You are the BEST! Thanks again and have another star!

Deb

 

[PaulBricker]

I see that. Sorry for the oversight. Glad you got it working.

Paul