Count(not duplicates)... HELP!?

[morfusaf]

Ok, I have searched this, and I have tried some of the things that where shown(most for 2003) and it hasn't worked yet or I didn't understand what they ment.


I am using MS Access 2007. I have a report that prints out peoples names, which events they went to and the type of events... on the bottom I have totals, which works great if I am just pulling the report on 1 person. But if i need to pull a report on say an office... then it counts the duplicate events, and types...


Currently, I have a text box for

= IF([EventTitle] <> null, 1,0)      **NOT EQUAL

This way it makes a 1, for each event... then in the footer I have ...

=Count([EventTitle])

in order to count

I need to be able to count each individual event... and not duplicates... how to do this? Please give as detailed as you can as I haven't been able to figure it out yet.

 

[AccessMSSQL]

You need to create a global variable at the top of your report and then on the OnPrint event (or OnFormat) event of your detail section (or group section) add 1 to your global variable. On the report or group footer OnFormat event - set the txtCount total = the global variable. Make sure you reset the variable to zero on report open.

Something like this:

Option Compare Database
Dim gCount As Integer
 
Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Me.txtCount = gCount
End Sub
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
gCount = gCount + 1
End Sub
Private Sub Report_Open(Cancel As Integer)
gCount = 0
End Sub

http://www.accessmssql.com/

 

[morfusaf]

I am sorry but it isn't working at all...

I don't understand what your telling me to do.


I do not wish to create a new group(if its possible not to)... as I have 3 differnt things in the Detail section, that I need to count like this....

Also on the report there is no onformat event. ...


Here is the code I am trying to use...

Option Compare Database
Dim gCount As Integer
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
gCount = gCount + 1
End Sub
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.txtCount = gCount
End Sub
Private Sub Report_Open(Cancel As Integer)
gCount = 0
End Sub

Then I have a box and the data source is = txtCount or gCount niether works.

 

[AccessMSSQL]

It sounds like you need to create a group header for Office and/or Event. But not sure without seeing more. Is it possible to post the project - or at least your table structures? Also, what three things are you trying to count in the detail section (EventTitle, EventType, ??).


http://www.accessmssql.com/

 

[morfusaf]

Attached is 2 databases ... a 2003 version and a 2007 version, incase you don't have 07. I am currently using 2007 and would hopefully like an answer in the 2007 version.

The databases don't work properly because of some missing stuff but I figured this might help a little.


On the report, I select which "Flight" to run the report on then date ranges...

it runs the report and sorts by LastName, then FirstName, then DetailDate.

The things I wish to count are.

-The total events(not counting duplicates). The easiest way would be by eventID maybe.. although I don't display this anywhere .

- then there are 2 types of events( funerals and Color Guard). I need an if funeral then +1 to funerals. and same for colorguard (EventType)

- I also want to get the Detail Hours to add up, and not use duplicates(might have to reference the EventID or something to ensure this, as some of the numbers might be the same.

- Also the RoundTripMiles... same as detail hours.


Currently If you look at the report in design view you will notice 3 blocks on the left side of the detail section.... this is currently how I am counting them... for an individual it works great... but when I pull down a group, it counts duplicates.

I sure hope this helps, I have been stuck on this for almost a week now.

Let me know if you have anymore questions.

 

[AccessMSSQL]

Okay. let me take a look and I'll get back to you.

 

[AccessMSSQL]

Okay I took a look at your report. Are you trying to count event types or actual events? For example, I removed all the filters from the report so it returns all the records. You have some Funeral Types but then you have individual Funerals. For your event total do you want just the number of event types - in the case of returning all records you would only have 1 event type - funeral. But if you were counting individual events it looks like you would have 5 events.
The way you have it set up with the IIf statements isn't correct. It's still going to count 1 for each row returned in your report.

If you want to return a count of all the unique events in your report you should create another query as follows:

SELECT tblEvents.DetailEventTitle
FROM tblPeople INNER JOIN (tblEvents INNER JOIN tblPeopleAtEvents ON tblEvents.EventID = tblPeopleAtEvents.EventID) ON tblPeople.PersonID = tblPeopleAtEvents.PersonID 
WHERE (((tblEvents.DetailDate) Between Forms!formMemberReports!FlightFDate And Forms!formMemberReports!FlightEDate) And ((tblPeople.HG_Flight)=Forms!formMemberReports!Combo68))
GROUP BY tblEvents.DetailEventTitle;

Save it as qryEventGroupTotal

Then in your footer section of your report in your control do a DCount:
=DCount("DetailEventTitle","qryEventGroup")



http://www.accessmssql.com/

 

[morfusaf]

I am trying to count ... well both.

First, i need to count the total events so I need to count the total actual events(EventID would probably be best for this... I guess, make an invisible eventID section???)

Then I also want to count the 2 different types... I have 3 differnt types of funerals, but I want to bunch them all up in to funerals.. So it should just be EventType - ColorGuard or Funeral... then I want the total ColorGuard Events, and the total Funeral Events.

Then also I want the total Hours, and miles.... not to duplicate these from the same events.

Ill try what you posted and see if that helps me any. Thanks.

 

[morfusaf]

Well, I beleive I did what you said... and no luck... just get "#ERROR"

I created the qry... copied exactly what you had... then created a Dcount in the footer... but just get an error.


DO YOU HAVE AN EMAIL ADDRESS SO I CAN EMAIL THIS AND YOU CAN CHECK IT OUT... file is to large for uploading here.

 

[AccessMSSQL]

You can email it to accessmssql@gmail.com - how big is the file and can you zip it? And compact and repair before zipping. I'll take a look when I get it.

 

[AccessMSSQL]

Hi morfusaf: I looked at your project - you were close but I didn't see where you put the Dcount function in your report. It wasn't there. A screenshot of what I did is attached. I'll also return the project to you via email.