Select a distinct row per ID, but rather complicatedly.

[jooon3]

Hi, all,

I have a data as follows.
ID t Status
1 0 Y
1 1 Y
1 2 Y
1 3 N
1 4 N
1 5 N
1 6 N
2 0 X
2 1 X
2 2 Y
2 3 Y
2 4 Z
3 0 Y
3 1 Y
3 2 Z
3 3 Z
3 4 Z
3 5 N
3 6 N
3 7 Y
3 8 Y
4 0 Y
4 1 Y
4 2 X


Assume I have the data above.
I want to create a query generating its result as follows.

ID Delta T
1 1 3
2 0 4
3 1 5
4 0 2.

More explanation of the rule is as follows.
Each ID has varying status X/Y/Z etc. and N.

Basically, want to select a row from each ID.
Once 'N' is observed at least once in the status field,
I want to define Delta(ID)=1. Otherwise =0.
In other words, I want Delta to be an indicator if there was any status 'N' in the history of the ID. Then want to let T be the first month the 'N' is observed.

If no 'N' is observed, I want Delta(ID)=0.
Then I want T to be the largest t observed.

I cannot create exactly what I really want for myself.
Please give me a help.

Thank you,

Joon

[Carl Witte]

I'm confused. Could you post a mathmatical formula of what exactly the transformation is supposed to be?

[jooon3]

Let Time(ID) be a set of available t's corresponding to the given ID.
(e.g. Time(ID=1) = {0,1,2,3,4,5,6}.)

If Status(ID, t)= "N" for some t in Time(ID), then

Delta(ID) := 1
T(ID) := min{t in Time(ID) : Status(ID, t) = "N"}.

Else (that is, if Status(ID, t)<> "N" for all t in Time(ID) ),

Delta(ID) := 0
T(ID) := max{t in Time(ID)}.


I don't know this is enough for you to understand.

Thank you,

Joon

[Carl Witte]

Its better. Seems to me that you are trying to calculate answers for some variables. I'd separate it into multiple queries. Call the first your "N" query. Perform your first calculation.

min{t in Time(ID)}

Making sure to use the "WHERE" your field for your data ="N"

Then make a second query called your "Not N" query.

max{t in Time(ID)}.

Making sure to use the "WHERE" your field for your data <> "N"

Generally speaking when I do things like this (multiple level queries from the same data), I put the input data in one table and the output data in a second table. That way I can watch it better. Then after I make sure it works I steal the SQL code the program has made for me and run it from VBA or a macro. That way I can run the queries quicker in the future and never wonder, "Did I run both of those or just the first one?"

[FishVal]

Hello, jooon.

May be not the most elegant but a working one:

qryHasN

SELECT t.lngID, Min(t.lngT) AS T, Sgn(Sum(t.txtStatus="N")) AS blnDelta
FROM t
WHERE t.txtStatus="N"
GROUP BY t.lngID
HAVING Sgn(Sum(t.txtStatus="N"));
 

qryHasNoN

SELECT t.lngID, Max(t.lngT) AS T, Sgn(Sum(t.txtStatus="N")) AS blnDelta
FROM t
GROUP BY t.lngID
HAVING Not Sgn(Sum(t.txtStatus="N"));
 

qryOverall

SELECT * FROM qryHasN
UNION
SELECT * FROM qryHasNoN;
 

Regards,
Fish

[jooon3]

Thank you all.
I will try both. (they are possibly same?)
Then get back to you whether they work or not.
If not, I will ask another question.

Thank you again,

Joon

[Stewart Ross]

Hi jooon3. I do not want to disrupt this thread as your question has kindly been answered by two posters, but does your question relate to a real requirement you are trying to implement, or is it instead taken from some form of homework assignment? The phrasing of your question, and in particular of your clarification in post 3, looks very like the phrasing of an assignment question of some kind to me.

If it is, please be aware that it is against our site rules to expect others to answer homework questions for you - our posting guidelines and FAQ are quite clear about this.

Of course it may be that you are simply solving a particular work-related problem - if this is the case the assistance you have received will be very welcome I am sure.

-Stewart

[jooon3]

I am sorry if my post was not appropriate in this forum. But it was none of HW.

I generated the simple data for my other project because the real data was confidential.
The post 3 was fully written by myself based on the rules I wanted to apply.

I was just new to MS-Access.
Sorry again if the question was too elementary.

Joon

[ADezii]

I am sorry if my post was not appropriate in this forum. But it was none of HW.

I generated the simple data for my other project because the real data was confidential.
The post 3 was fully written by myself based on the rules I wanted to apply.

I was just new to MS-Access.
Sorry again if the question was too elementary.

Joon

Here is an Algorithm that will produce the desired results in the Form of a Table (tblFinalResults), but first, two simple steps:

1. Create a Table named tblTestData and populate it with your sample data, or simply rename your present Table, but maintain the following Data Types and Field Names:
   • [ID] - (INTEGER)
   • [t] - (BYTE)
   • [Status] - (TEXT 1)
2. Create a Table and name it tblFinalResults:
   • [ID] - (INTEGER)
   • [Delta] - (BYTE)
   • [T] - (INTEGER)
3. Create the following Public Function in a Standard Code Module:
   

   Public Function fGenerateResultSet() As Boolean
   Dim MyDB As DAO.Database
   Dim rstUniqueIDs As DAO.Recordset
   Dim rstResults As DAO.Recordset
   Dim strSQL As String
   Dim strSQL_2 As String
   Dim bytDelta As Byte
   Dim intT As Integer
    
   fGenerateResultSet = False      'Initialize to False
    
   Set MyDB = CurrentDb()
    
   'Used to generate 'Unique ID' Values in Ascending Order
   strSQL = "Select Distinct [ID] From tblTestData Order By tblTestData.[ID];"
   Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    
   Set rstResults = MyDB.OpenRecordset("tblFinalResults", dbOpenDynaset)
    
   'Delete any prior contents of the Table
   strSQL_2 = "Delete * From tblFinalResults;"
   CurrentDb.Execute strSQL_2, dbFailOnError
    
   With rstUniqueIDs
     Do Until .EOF
       'N is observed for specified [ID]
       If DCount("*", "tblTestData", "[Status] = 'N' And [ID] = " & ![ID]) > 0 Then
         bytDelta = 1      'N was observed for [ID], min{t in Time(ID): Status(ID, t) = "N"}
         intT = DMin("[t]", "tblTestData", "[Status] = 'N' And [ID] = " & ![ID])
       Else
         bytDelta = 0      'N was not observed for [ID], max{t in Time(ID}
         intT = DMax("[t]", "tblTestData", "[ID] = " & ![ID])
       End If
         rstResults.AddNew
           rstResults![ID] = ![ID]
           rstResults![Delta] = bytDelta
           rstResults![T] = intT
         rstResults.Update
         'Debug.Print ![ID] & " ==> " & bytDelta & " ==> " & intT      Test String
       .MoveNext
     Loop
   End With
    
   'If you've gotten this far, success
   fGenerateResultSet = True
    
   rstResults.Close
   rstUniqueIDs.Close
   Set rstUniqueIDs = Nothing
   Set rstResults = Nothing
    
   'Finally, lets see the fruits of our labor
   DoCmd.OpenTable "tblFinalResults", acViewNormal, acReadOnly
   End Function

4. Sample Function Call:
   

   If fGenerateResultSet() Then
     MsgBox "Success!"
   Else
     MsgBox "ADezii screwed things up again!"
   End If

5. OUTPUT (tblFinalResults)
   

   ID    Delta    T
   1      1    3
   2      0    4
   3      1    5
   4      0    2

6. Any questions, feel free to ask.