Count function which will ignore duplicates?

[Rik_StHelens]

Hi,

I have a query which outputs to a report.

It takes a full set of customer vehicles, groups them by fleet location and count the total costs incurred by that fleet for repairs/inspections etc. The report then works out the average cost per vehicle, by fleet.

In the report i do a count function which counts the number of reg's in a grouping to work out the number of vehicles per fleet. However, if a vehicle has incurred more than one cost in the specified time period, it counts it more than once because it pulls more than one record for that vehicle.

Is there any way i can stop it from doing this at report level, or is it at query level?

Here is the query spl:

SELECT tblVehicle.Customer, TRNLIST.TNETT, tblVehicle.FleetName, tblVehicle.RegistrationNumber
FROM tblVehicle INNER JOIN (tblInspection LEFT JOIN TRNLIST ON tblInspection.JobSheetNumber = TRNLIST.OLDADVNUM) ON tblVehicle.RegistrationNumber = tblInspection.Registration
WHERE (((tblVehicle.Customer) Like "*" & [Customer Name] & "*") AND ((tblInspection.InspectionDate)>=[Date From (dd/mm/yyyy)]));

Thanks for your time.

 

[DCrake]

Create and additional query that uses the same period but groups by fleet by reg no and count the regs' and summs the costs. Then include this into your other query joining on reg and fleet.

Can one reg move from one fleet to another in the same time period?

David

 

[Rik_StHelens]

Create and additional query that uses the same period but groups by fleet by reg no and count the regs' and summs the costs. Then include this into your other query joining on reg and fleet.

Can one reg move from one fleet to another in the same time period?

David

It would be highly likely, because we often send out workmen to look at a vehicle which is now based 200 miles away from the fleet location we have on record because our customers don't always pass on updates. nature of the industry i think.

we are aware that this would have a bearing on the final costs per vehicle, but i dont think there is a lot that can be done about it.

We currently copy and paste several information sources into a spreadsheet which is accurate, but very laborious and we have a few customers who want this information regularly.

 

[Rik_StHelens]

Right i have created the 2nd query, but it just says that either the expression is typed incorrectly or too complicated to be evaluated, i know i tried something similar orignally but got the same error message.

Here is the SQL:

SELECT tblVehicle.Customer, tblVehicle.FleetName, tblVehicle.RegistrationNumber, Sum(tblVehicle.RegistrationNumber) AS SumOfRegistrationNumber, Sum(TRNLIST.tnett) AS SumOftnett
FROM TRNLIST INNER JOIN tblVehicle ON TRNLIST.REGNUM = tblVehicle.RegistrationNumber
GROUP BY tblVehicle.Customer, tblVehicle.FleetName, tblVehicle.RegistrationNumber, TRNLIST.INVDATE
HAVING (((tblVehicle.Customer) Like "*" & [Customer Name] & "*") AND ((TRNLIST.INVDATE)>=[From Date dd/mm/yyyy]));

 

[Mike375]

I use this is on form. First go to the Allen Browne site for a function. As it says, simply paste into a Module. The function is called ECount.

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

In an unbound textbox I have

=ECount("[CLSurname]","HoldProspects","[CLSurname]",True)

In this case it counts the number of unique Surnames in the tabe HoldProspects. For what you want it would probably be ID numbers and you replace HoldProspects with the query name that supports your Report.

In a Report, you might need to put a pause in your code that opens and prints the Report because depending on table size it can take a second or two for the number to appear.

 

[Rik_StHelens]

my bad,

im trying to sum vehicle regs instead of count aren't i?

monday mornings...

 

[Rik_StHelens]

mike,

I have implemented the Ecount, however, it prompts me for an "Ecount" when i run the report, and therefore returns an error. BUT the "Cost per vehicle" still works out a value, so it must be finding a value somewhere.

David, I am also working on implementing your solution, and will let you know how i get on. Although as its a Monday it may take a while, got a few jobs stacked on my desk from over the weekend, as we are a 24 hour service but i don't work saturday/sunday

but thank you for your time, and i will get to work on it asap!

 

[Mike375]

I use the code exactly as on the site and DAO reference is there as well.

Another way you could do it which will work for a form so I assume a Report also (I only use Reports in a very restricted way) is to:

1) Make a seperate query with Group By and Count to get the number of unique registrations.

2) Make a form based on this query.

3) Place an unbound text box on your Report but with no data/formula

4) Have you code

a) Open the Repor
b) Open the form based on the Group By query and then set the value of the unbound textbox with the value in the form.
c) Close form
d) Print Report.

Edit: I forgot. I use a Count query of the first Group by to get the single number. But I am sure it can be done with the one query. I often find myself using 2 or 3 queries where others can do it with one.

 

[Rik_StHelens]

I have put the following in an unbound textbox:

=ECount("[RegistrationNumber]","[Costs By Fleet Location]","[RegistrationNumber]",True)

However when i run the report it prompts me for a value for ECount.

What could be causing this and how would it be fixed?

 

[Mike375]

Don't know. Maybe something to do with Report as compared to a Form but I don't see why. And it works for me if there a Null records for the field.

Did you compile to check the module is OK.

Try the other method I gave you as I think that will bed surefire for you.

 

[Rik_StHelens]

I have now sorted the problem using David's suggestion, and a modified report design

Thank you both for your help

 

[Mike375]

That's good.

Actually I got something from this thread by accident When I saw it I remembered I had the ECount thing done but never used in the real world as such. So I went looking around for it in my "tool boxes" which are real disorganised and also found something else I had been looking for.

But interesting to know why it is not working for you. Maybe David will know.

 

[Rik_StHelens]

Actually i sent an email to Allen Browne but the reply didn't shed any light

"Needs to be in a standard module (not that of a form.)"

I just copied and pasted into a module, and saved as ECount.

but never mind

 

[Mike375]

I just put it in the header of a Report and it worked OK, that was looking in Preview as I don't have a printer where I am.

Who knows

 

[Brianwarnock]

I just copied and pasted into a module, and saved as ECount.

but never mind

Do you meann that you saved the module as Ecount?

If so that is a no no the module name must not be the same as a function name

brian

 

[Mike375]

Brian,

I thought that might have been his problem so I renamed my module to Ecount.....and it still worked

 

[Brianwarnock]

Brian,

I thought that might have been his problem so I renamed my module to Ecount.....and it still worked

I really believed that was not allowed.

Brian

 

[Rik_StHelens]

Yes.

Unfortunately i do.

but then again i don't really know anything about VB etc, so im just picking things up as and when i can

 

[Mike375]

Me to.

I just tried another function within the same data base that just () after function name and it worked as well. I even copied the EndofField and pasted into the rename to make sure.

Public Function EndofField()
Forms![ProspectNotes]!ActdateComments.SetFocus
Forms![ProspectNotes]!ActdateComments.SelStart = Len(Forms![ProspectNotes]!ActdateComments.Text)
Forms![ProspectNotes]!ActdateComments.SelLength = 0
End Function

 

[Mike375]

Yes.

Unfortunately i do.

but then again i don't really know anything about VB etc, so im just picking things up as and when i can

Go back and change the name of the module from ECount to xyz or whatever.

Like Brian I was sure but it is not affecting me. Maybe version of A2003.

Are 100% sure you got the function posted in correctly.

I am going to bed soon as 2.10am here in Australia but tomorrow sometime I pull mine out with the form and table and attach and see if it works for you. I am tempted to do it now but you start that sort of stuff and then you notice the sun rising