Need to Avoid Counting Duplicate Data in a Report

[Uvuriel03]

Help with Counting in a report

Alright, so here's the setup.


Every once in a while, a truck comes to our warehouse and drops off several pallets of product. I enter these products into the computer, one line for each pallet, and also include the truck information for each pallet. The truck information includes a truck number and the truck length.

One truck may bring as many as 30 or 40 pallets. What I need to do is be able to count how many trucks of each length came in between two dates.

I already have the query set up, as well as most of the report. With some work, I was able to make the report display something like this:

Truck Size_______Truck Number_(Date Received)
48'
________________A1 (Date)
________________A3 (Date)
53'
________________A2 (Date)
________________A5 (Date)
________________A6 (Date)
24'
________________A4 (Date)

Now what I would like to do is be able to have a sum of how many Large trucks (48' or 53') and how many small trucks (24') came in for that month.

In order to get the report to display as it does above, here is what I have done:


There are three data columns: Truck Type, Truck Number, and Date Received.

Truck Type has a header of its own.
Underneath that header is a Truck Number header, and the Date Received is also in that header.

Now I need to know where to put what in order to get a count of how many trucks there were of each type, large and small!

Thanks!

 

[gemma-the-husky]

look at sorting/grouping (if not there already)

sort by truck size, then say you want a section footer

in the section footer you can have

=count(trucknumber) or any other sutiable field to give you a subtotal - the same as with an overall total - this doesnt interfere with the overall total - if the field is named right, access should add square brackets to the field name

you can also have =sum(pallets) to total the pallets

etc

 

[Uvuriel03]

This is getting closer to what I need.

The problem is that the count in the section footer is counting each line that has that particular truck size, which means it is counting each pallet that had that size truck. I just need it to count that truck number *once*, not once for each pallet on the truck.

Got any further ideas?