Re: Count of code per order

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Danu wrote:
Thank you! I'll give it a try!

"Jerry Whittle" wrote:

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.
--
Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Danu" wrote:

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

Relevant Pages

  • Re: Integral-Brakes from BMW unreliable (YouTube Video)
    ... At what time did the crash occur and at what time were the codes ... Does the sporadic recording of codes relate to ... falsely without a failure being present? ...
    (rec.motorcycles)
  • Re: first program evaluation
    ... Using such deep nesting? ... Using failure result codes? ... I'd utilize RAII for handling resources (and the ... success or failure of obtaining them). ...
    (comp.lang.cpp)
  • Re: Count of code per order
    ... IF you cannot change the table structure, then you can use a UNION query to ... There are 12 two-letter failure codes. ... I have a table of records and the work order number appears several ...
    (microsoft.public.access.queries)
  • Re: does a failed foundation violate any codes ?
    ... Building codes are designed to standardize a minimum level of quality ... for new construction and alterations. ... evaluate failure in existing buildings. ... The local building inspector ...
    (alt.home.repair)
  • Re: Counter
    ... This is because the query joins the table to itself, using A and B for the ... B.AreaOfLocation AS PreviousArea ... how can I show the Previous"AreaOfLocation" and The Present ... it's returning the number of days since the previous failure for that PR ...
    (microsoft.public.access.forms)