Newby: help with query

  • Thread starter Thread starter Vic
  • Start date Start date
V

Vic

I have imported a flat file composed of "Request ID #s" along with their
result and a reason code that caused that result to happen. Sometimes more
than one event contributes to the result. In that case, I will see another
record with duplicated Request ID # and a different reason code.

The Request ID #s are unique in the file.They represent a single
transaction. They are numeric. Records always have a Request ID #. Here is
the simplified version of what this looks like:

REQ_ID_1 Result_Acc <no reason code when there is no reject or review
result>
REQ_ID_2 Result_Rvw Reason50
REQ_ID_3 Result_Rej Reason12
REQ_ID_3 Result_Rej Reason65
REQ_ID_3 Result_Rej Reason66
REQ_ID_4 Result_Rej Reason4
REQ_ID_5 Result_Rvw Reason24
;;; ;;; ;;;;


I have two needs:
- I would like to count the Request IDs along with their results (accept,
review, reject) but not count duplicate request ID #s. (I can never have a
case where the transaction has a mixed result state. It is always uniquely
Accept, Review or Reject.)

- I would like to count the incidence of individual reason codes and also
obtain a total of all reason codes. (An accept transaction has an Accept
result and generates no reason code. Only Review and Reject results result
in a reason code. Thus, there will be some number of transactions with
Review or Reject states, and there could be more reason codes than reviews
or rejects.)

Can you suggest queries to a newby? I've mucked around, but I haven't gotten
anything satisfactory.

Vic
 
try

SELECT RequestIDFieldName, Count(RequestIDFieldName) AS CountOfFieldName
FROM TableName
GROUP BY RequestIDFieldName;

substitute the correct table and field names, of course. you can use the
same syntax to group and count ReasonCodes. just change the fieldnames to
suit.

hth
 
Thank you!!

Vic
tina said:
try

SELECT RequestIDFieldName, Count(RequestIDFieldName) AS CountOfFieldName
FROM TableName
GROUP BY RequestIDFieldName;

substitute the correct table and field names, of course. you can use the
same syntax to group and count ReasonCodes. just change the fieldnames to
suit.

hth


have
Click to expand...
 
Back
Top