Help with Status Table

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

Bre-x

Hi, I have a table with the following fiels

ID, Date, Time, Program, Status

1,11/25/09, 6:45 AM, 6261000, Send
2,11/25/09, 6:47 AM, 6261000, Send
3,11/26/09, 7:30 AM, 6261000, Receive
4,11/26/09, 7:30 AM, 6261400, Send
5,11/26/09, 7:40 AM, 6261400, Recive
6,11/26/09, 7:30 AM, 6261500, Send
7,11/26/09, 7:40 AM, 6261600, Receive
7,11/26/09, 8:40 AM, 6261800, Send
7,11/27/09, 8:50 AM, 6261800, Receive
7,11/27/09, 8:52 AM, 6261800, Receive

I would like to show the "Status" of this records, so I end up with a table
like:

Program, Status
6261000, OK (doesnt matter how many times it was send, but it has been
received back)
6261400, OK ( send and receive)
6261500, Send
6261600, Receive
6261800, Ok (It was send, and recieve (Receive twice, it is ok)

Thank you All !!!!

Bre-x
 
You could try the following query.

SELECT Program
, Max(Status)
, Min(Status)
,IIF(Max(Status)="Receive" And Min(Status) = "Receive","Receive"
,IIF(Max(Status)="Send" and Min(Status)="Send","Send"
,IIF(Max(Status)="Send" and Min(Status) = "Receive","OK"
,"UNKNOWN STATE"))) as TheStatus
FROM [YourTable]
GROUP BY Program

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you very much.

It looks like it is working so far

Again thanks!!!!!!


John Spencer said:
You could try the following query.

SELECT Program
, Max(Status)
, Min(Status)
,IIF(Max(Status)="Receive" And Min(Status) = "Receive","Receive"
,IIF(Max(Status)="Send" and Min(Status)="Send","Send"
,IIF(Max(Status)="Send" and Min(Status) = "Receive","OK"
,"UNKNOWN STATE"))) as TheStatus
FROM [YourTable]
GROUP BY Program

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Bre-x said:
Hi, I have a table with the following fiels

ID, Date, Time, Program, Status

1,11/25/09, 6:45 AM, 6261000, Send
2,11/25/09, 6:47 AM, 6261000, Send
3,11/26/09, 7:30 AM, 6261000, Receive
4,11/26/09, 7:30 AM, 6261400, Send
5,11/26/09, 7:40 AM, 6261400, Recive
6,11/26/09, 7:30 AM, 6261500, Send
7,11/26/09, 7:40 AM, 6261600, Receive
7,11/26/09, 8:40 AM, 6261800, Send
7,11/27/09, 8:50 AM, 6261800, Receive
7,11/27/09, 8:52 AM, 6261800, Receive

I would like to show the "Status" of this records, so I end up with a
table like:

Program, Status
6261000, OK (doesnt matter how many times it was send, but it has been
received back)
6261400, OK ( send and receive)
6261500, Send
6261600, Receive
6261800, Ok (It was send, and recieve (Receive twice, it is ok)

Thank you All !!!!

Bre-x
Click to expand...
Click to expand...
 
Back
Top