Count(?) Problem

[frankbutcher]

Hi All.
I have looked through the existing threads....but with no joy to the problem I have.
I have a report that is run from a query, that shows the job details
for each job (ID_number), and it also adds up the cost of materials used
on that job. This info is stored on 2 different tables, with ID_number
as the link.
So, on a report that is run for the chosen month, it could show 10
jobs, and it will show the total material amount for each job.

This is all OK, but what I would like to do is have a 'count' option in the footer
of the report. I normal use =count(*) but when I use this it doesn't
seem to work.
If, for example, there are 10 jobs in a month, and job ID_number 10 has
2 materials booked against that job, it will count ID_Number 10 twice, and make the total jobs for that month 11.

I have tried hiding duplicates, changing the sort and grouping, placing
=count(*) in different places, and using =count(ID_number) but nothing
seems to work!!

Is count the right one to use? I looked at a few threads that mention sum? Do I have to change something in the query? (but then I need all
materials carried through to the report as this is where I do the total cost)

Hope this makes sense.

Frank.

 

[RV]

What's you're counting is the total number of materials booked against a job, cause that's how you've set up your query

Have you tried the DCount function yet?
If not, give it a go.

RV

 

[frankbutcher]

Thanks for the reply.
Been looking up Dcount but with no luck. Sorry to be thick, but that would go within a report and not the query?

Attached are 2 jpg's, one of the query and one of the results. I was looking at a way of hiding duplicates within the query, but that got me no-where.

It is driving me mad!!

Thanks

Frank.

 

[RV]

Hard to tell from screendumps what's going on.
Post your db, in Access 2000 format, winzipped.

RV

 

[frankbutcher]

Hi.
Thanks for the reply.
I have it sorted now, thanks to another reply you gave to me on another thread!
On that thread, you said to remove the materials from the query, which got me thinking.....
I made another query from the query, with only the field (ID_Number) where I want to count the records. This obviously still shows all the values, but then just select "Unique Values" on the query......and then do a count!!

Thanks for the hep!!

Frank.