Help with summarized query

[Cam]

Hello,

I am trying to create a query to capture the total count in each WC where
the operation corresponding to the WC vary and can be multiple operation per
WC.
The work center ref information is:
WC Operation
Heat 1 to 90
Cool 100
Inspect 110 to 130
QA 140
Super 150
Process 170 to 310
Assembly 320 to 500

I got several hundred data where an order# is at an operation. I need a
query to determine say if the order is at operation 1 to 90, then it count as
WC heat. I would like the query to summarize the total count in each WC.
WC Total count
Heat 35
Cool 12
Inspect 30
QA 20
Super 31
Process 50
Assembly 24

Thanks for any help.

 

[Dale Fye]

Easiest way is to create a table which contains info about the Operation
bands, something like:

tbl_Op_Bands
WC BandStarts BandEnds
Heat 1 90
Cool 100 100
Inspect 110 130
QA 140 140
Super 150 150
Process 170 310
Assembly 320 500

Then add this table, along with your other table that indicates what
operation an order is in to a new query. Don't join them on any field. The
query might look something like:

SELECT OB.WC, Count(T.ID) as Total
FROM tbl_other_table as T, tbl_Op_Bands as OB
WHERE T.Operation >= OB.BandStarts
AND T.Operation <= OB.BandEnds
GROUP BY OB.WC

You probably will need another element in your WHERE clause to weed out all
of those orders that have already been completed.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Cam]

Thanks Dale. Worked like a charm.

Easiest way is to create a table which contains info about the Operation
bands, something like:

tbl_Op_Bands
WC BandStarts BandEnds
Heat 1 90
Cool 100 100
Inspect 110 130
QA 140 140
Super 150 150
Process 170 310
Assembly 320 500

Then add this table, along with your other table that indicates what
operation an order is in to a new query. Don't join them on any field. The
query might look something like:

SELECT OB.WC, Count(T.ID) as Total
FROM tbl_other_table as T, tbl_Op_Bands as OB
WHERE T.Operation >= OB.BandStarts
AND T.Operation <= OB.BandEnds
GROUP BY OB.WC

You probably will need another element in your WHERE clause to weed out all
of those orders that have already been completed.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Glad to help.
--

Dale

email address is invalid
Please reply to newsgroup only.