Running Subtotal of Text Values on a SubForm

[ChadDiesel]

I'm new to access and need some help.

I have a form with a shipment destination at the top. I then have a subform
that I enter boxes for that shipment. I have several shipments going at a
time, so I can use the control at the bottom of the form to move from
shipment to shipment and I can see all the boxes in each shipment.

I'd like to put a subtotal for the shipment weight and box types at the
bottom of the subform.

I placed =Sum([Weight]) in the footer of the subform and it works fine.
When I switch to the next shipment, it shows a total for just that
shipment-just what I want.

The problem is with text values. I want to have running total of each type
of box I have. I have a field called Box_Type that contains text values
like Assembly Box, Regular Box, Small Box, etc. I would to have running
totals in the footer like this:

Total Assembly Boxes:

Total Regular Boxes:

Total Small Boxes:

These are text values, so I need to count every time the value is "Assembly
Box" for example.

I tried to use the Dcount function with the value = "Assembly Box", but it
gives me a total for the entire table and not just the shipment. I guess
this is because you have to specify the table in the conditions.

What I was thinking is to use the =Count([Box_Type]) with some kind of
condition that it counts "Assembly Box" or whatever other types.

I'm new to Access, so I might not be using the right terminology or going at
the problem the right way. Any information would be appreciated.

Thanks,

Chad

[Phil Stanton]

Hi Chad

Before you come to the counting problem, I strongly suggest you have a table
of BoxTypes and use a combo box to select the right type of box. The reason
being that Assembly Box is not the same as AssemblyBox or Asembly Box. So
there will be a strong probability of a miscount anyway. Apart from that it
is much quicker to select the box type rather than type (or miss type) it
in.
So the Boxes table will also have a ShipmentID, Box Number, BoxTypeID as
well as the weight.

You may then be able to gave an expression like
DCount("BoxType ID","Boxes","Bo xTypeID = 2 AND ShipmentID =
Forms!Shipments !ShipmentID")
That should find the second type of box.

HTH

Phil
"ChadDiesel " <sh************ ***********@yah oo.com> wrote in message
news:zI******** *******@newsrea d2.news.atl.ear thlink.net...

I'm new to access and need some help.

I have a form with a shipment destination at the top. I then have a
subform
that I enter boxes for that shipment. I have several shipments going at a
time, so I can use the control at the bottom of the form to move from
shipment to shipment and I can see all the boxes in each shipment.

I'd like to put a subtotal for the shipment weight and box types at the
bottom of the subform.

I placed =Sum([Weight]) in the footer of the subform and it works fine.
When I switch to the next shipment, it shows a total for just that
shipment-just what I want.

The problem is with text values. I want to have running total of each
type
of box I have. I have a field called Box_Type that contains text values
like Assembly Box, Regular Box, Small Box, etc. I would to have running
totals in the footer like this:

Total Assembly Boxes:

Total Regular Boxes:

Total Small Boxes:

These are text values, so I need to count every time the value is
"Assembly
Box" for example.

I tried to use the Dcount function with the value = "Assembly Box", but it
gives me a total for the entire table and not just the shipment. I guess
this is because you have to specify the table in the conditions.

What I was thinking is to use the =Count([Box_Type]) with some kind of
condition that it counts "Assembly Box" or whatever other types.

I'm new to Access, so I might not be using the right terminology or going
at
the problem the right way. Any information would be appreciated.

Thanks,

Chad