Count of code per order

  • Thread starter Thread starter Danu
  • Start date Start date
D

Danu

How can I count the number of times a particular two-character code appears
in a work order.

I have a table of records and the work order number appears several times,
each time associated with a two-character code. I need to know how many times
each code appears in the work order. Will this require a subquery?

Example: Work order 1234 has codes FJ, MN, FJ, ER, FJ. I need the result to
display

Work order FJ MN ER
1234 3 1 1

Thank you.
 
A Crosstab query will fit the bill. Assuming A03 go to Query in the database
window. Go to New and select the Crosstab Query Wizard. It may take a few
tries to get it right. Somewhat confusing at first.
 
Thank you! I'll give it a try!

Jerry Whittle said:
A Crosstab query will fit the bill. Assuming A03 go to Query in the database
window. Go to New and select the Crosstab Query Wizard. It may take a few
tries to get it right. Somewhat confusing at first.
Click to expand...
 
Your table structure is wrong. You should have a separate table for the
failures linked to your IncomingInspection table.

IF you cannot change the table structure, then you can use a UNION query to
normalize your data. Union queries cannot be built in the query grid, but
must be built using the SQL view. The union query would look something like
the following - substitute you field names and table name.

SELECT InspectionID, InspectionDate
, Failure1 as FailureType, Failure1Count As FailureCount
FROM YourTable
UNION ALL
SELECT InspectionID, InspectionDate, Failure2, Failure2Count
FROM YourTable
UNION ALL
SELECT InspectionID, InspectionDate, Failure3, Failure3Count
FROM YourTable
UNION ALL
SELECT InspectionID, InspectionDate, Failure4, Failure4Count
FROM YourTable
UNION ALL
SELECT InspectionID, InspectionDate, Failure5, Failure5Count
FROM YourTable

Now after you create and save that query, you can use it as if it were a
table and get your statistics from it. You could build a crosstab query
based on the saved union query.

TRANSFORM Sum(FailureCount) as TotalFailures
SELECT Format(InspectionDate,"YYYY-MM") as TheMonth,
Sum(FailureCount) as TotalFailuresForMonth
FROM SavedUnionQuery
WHERE InspectionDate Between #2007-01-01# and #2007-12-31#
GROUP BY Format(InspectionDate,"YYYY-MM") as TheMonth
PIVOT FailureType

You might consider posting your query (hint: View: SQL from the menu) if you
want specific advice on why it is failing. I suspect that you are only
applying the date criteria to one instance of the criteria.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top