Query Help

[renenger]

I have a query that is looking at the Lot Delivery Date (LOTDELDATE) and summing the quantity of units that are due to be delivered that day and summing the number of boxes associated with that lot. It look at the State and groups them by day and state.

I want to add the status field and have it count the number of units that are considered not built and the number of units considered built.

If the unit is not built then the status would be In Layout, Ready for Production, In Mill or In Bank

If the unit is considered built then the status would be On Floor.

In the query I have

LotDelDate
Sum(Qty)
Sum(Boxes)
State
NotBuilt:Status = "In Layout" and "Ready for Production" and "In Mill" and "In Bank"
Built:Status = "On Floor"

It gives me 0 or -1 values

I then created a summary query based on the one above and wanted it to add those two fields up. Sum(NotBuilt) Sum(Built)

With the negative values this doesn't work.

Can anyone help me with this?

 

[ThreeCrow]

Just need a little more information


NotBuilt: Status etc. etc. are these differant fields in your table(s) do they have 0 or -1. Are they in the same field. Where do these words come from.

If they all answer 0 or -1, just use the Count Function. To use the Sum Function will add them up. Example Sum 50 0's the answer is 0.

NotBuilt:Count IIf([NotBuilt]="-1",0))
This will Count all of the -1 (Yes) and give you a total count.

Built is the same.

Hope this helps