Count of code per order

[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.

 

[Jerry Whittle]

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.

 

[Danu]

Thank you! I'll give it a try!

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.

 

[John]

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.

 

[John]

Jerry:

Do you think this would work on multiple columns?

We use a database (Access 2003) to record failures at Incoming
Inspection. There are 12 two-letter failure codes. We've set the
database up such that up to five different types of failures can be
entered for each record. The table has five columns for these codes
(each is followed by a column that lists the number of failures for each
type).

Somebody asked if I could query the database by month to see if a
certain type of failure was increasing with time. I set up a select
query starting with the "Between" function for a date range. After some
work, I was finally able to get the query to show all records containing
the desired failure type in any of the five columns. However, it now
ignores the start and end dates in my "Between" function, and lists
every record with that failure type regardless of date. Also, I want it
to count the instances of the failure code in each column, but I can't
get it to do so thusfar.

Would a crosstab query help me?

John

 

[John Spencer]

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
..