Need Help Adjusting Counting Query

[chaddiesel]

Hello Everyone,

I need some help adjusting this query:

Expr1: (SELECT Count(A.ID) FROM Label_Info AS A WHERE A.HW_Number = Label_Info.HW_Number And A.ID <= Label_Info.ID)-(SELECT Count(Nz(A.Packed_With)) FROM Label_Info AS A WHERE A.HW_Number = Label_Info.HW_Number And A.ID <= Label_Info.ID And A.Packed_With = 'W')

ID-----------Auto-number field
Label_Info---Table Name
Packed_With--Text Value--Mostly blank, but can be "C" or "W"
HW_Number----Contract Number--can repeat

I use this query to assign part of a Parcel ID that goes on our shipping boxes. What is does is count up until the user enters a new HW_Number. We also have situations where we pack a part with another part. The original part (marked with a Packed_With value of "C" for "contains") is assigned a number, but the parts that are packed with it (marked with a Packed_With value if "W" for "with") have the same number. For example:

HW_Number 1111111 Box 1 of 2 Number Value: 1
HW_Number 1111111 Box 2 of 2 Number Value: 2
HW_Number 1111111 Skid 1 of 1 Number value: 3
HW_Number 1111111 Box 1 of 1 C Number Value: 4
HW_Number 1111111 W Number Value: 4
HW_Number 1111111 W Number Value: 4
HW_Number 1111111 Skid 1 of 1 Number Value: 5
HW_Number 2222222 Box 1 of 1 Number Value: 1
HW_Number 2222222 Box 2 of 2 Number Value: 2

This works great. However, things have changed. Now we are packing parts with different HW_Numbers. This is how it should work now:

HW_Number 1111111 Box 1 of 1 C Number Value: 1
HW_Number 2222222 W Number Value: 1
HW_Number 7777777 Skid 1 of 1 Number Value: 1
HW_Number 7777777 Box 1 of 1 Number Value: 2


Right now, this is what I get with my current query:

HW_Number 1111111 Box 1 of 1 C Number Value: 1
HW_Number 2222222 W Number Value: 0
HW_Number 7777777 Skid 1 of 1 Number Value: 1
HW_Number 7777777 Box 1 of 1 Number Value: 2

I suppose that is because since it starts counting at 1 in the first part of the query because of the new HW_Number and then subtracts the second part of the query which is also 1 because of the "W" Value of the Packed_With field. It looks like I need an OR statement in the first part of the Query to say logically "Count up if the HW_Number is the same or if the Packed_With field contains a W" That way, when it subtracts the running count of the Packed_With containing W, everything will work out okay. I can't seem to get this to work any way I try.

Now I am very new to access, so maybe I am looking at things the wrong way. I know that assigning IDs in the manner I have discussed is probably not a good way to do things, but this is the way I have to do it for this particular customer. I would greatly appreciate any help you could give me. I am just about to finish this project, and this is the only thing I have left.

Thanks,

Chad