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.
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" wrote: > 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. >
Thanks Dale. Worked like a charm. "Dale Fye" wrote: > 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" wrote: > > > 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. > >
Glad to help. -- Dale email address is invalid Please reply to newsgroup only. "Cam" wrote: > Thanks Dale. Worked like a charm. > > "Dale Fye" wrote: > > > 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" wrote: > > > > > 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. > > >
