By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,384 Members | 927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,384 IT Pros & Developers. It's quick & easy.

Select a distinct row per ID, but rather complicatedly.

P: 8
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
Sep 12 '08 #1
Share this Question
Share on Google+
8 Replies


P: 37
I'm confused. Could you post a mathmatical formula of what exactly the transformation is supposed to be?
Sep 12 '08 #2

P: 8
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
Sep 12 '08 #3

P: 37
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?"
Sep 12 '08 #4

FishVal
Expert 2.5K+
P: 2,653
Hello, jooon.

May be not the most elegant but a working one:

qryHasN
Expand|Select|Wrap|Line Numbers
  1. SELECT t.lngID, Min(t.lngT) AS T, Sgn(Sum(t.txtStatus="N")) AS blnDelta
  2. FROM t
  3. WHERE t.txtStatus="N"
  4. GROUP BY t.lngID
  5. HAVING Sgn(Sum(t.txtStatus="N"));
  6.  
qryHasNoN
Expand|Select|Wrap|Line Numbers
  1. SELECT t.lngID, Max(t.lngT) AS T, Sgn(Sum(t.txtStatus="N")) AS blnDelta
  2. FROM t
  3. GROUP BY t.lngID
  4. HAVING Not Sgn(Sum(t.txtStatus="N"));
  5.  
qryOverall
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM qryHasN
  2. UNION
  3. SELECT * FROM qryHasNoN;
  4.  
Regards,
Fish
Sep 12 '08 #5

P: 8
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
Sep 12 '08 #6

Expert Mod 2.5K+
P: 2,545
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
Sep 12 '08 #7

P: 8
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
Sep 20 '08 #8

ADezii
Expert 5K+
P: 8,627
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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fGenerateResultSet() As Boolean
    2. Dim MyDB As DAO.Database
    3. Dim rstUniqueIDs As DAO.Recordset
    4. Dim rstResults As DAO.Recordset
    5. Dim strSQL As String
    6. Dim strSQL_2 As String
    7. Dim bytDelta As Byte
    8. Dim intT As Integer
    9.  
    10. fGenerateResultSet = False      'Initialize to False
    11.  
    12. Set MyDB = CurrentDb()
    13.  
    14. 'Used to generate 'Unique ID' Values in Ascending Order
    15. strSQL = "Select Distinct [ID] From tblTestData Order By tblTestData.[ID];"
    16. Set rstUniqueIDs = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    17.  
    18. Set rstResults = MyDB.OpenRecordset("tblFinalResults", dbOpenDynaset)
    19.  
    20. 'Delete any prior contents of the Table
    21. strSQL_2 = "Delete * From tblFinalResults;"
    22. CurrentDb.Execute strSQL_2, dbFailOnError
    23.  
    24. With rstUniqueIDs
    25.   Do Until .EOF
    26.     'N is observed for specified [ID]
    27.     If DCount("*", "tblTestData", "[Status] = 'N' And [ID] = " & ![ID]) > 0 Then
    28.       bytDelta = 1      'N was observed for [ID], min{t in Time(ID): Status(ID, t) = "N"}
    29.       intT = DMin("[t]", "tblTestData", "[Status] = 'N' And [ID] = " & ![ID])
    30.     Else
    31.       bytDelta = 0      'N was not observed for [ID], max{t in Time(ID}
    32.       intT = DMax("[t]", "tblTestData", "[ID] = " & ![ID])
    33.     End If
    34.       rstResults.AddNew
    35.         rstResults![ID] = ![ID]
    36.         rstResults![Delta] = bytDelta
    37.         rstResults![T] = intT
    38.       rstResults.Update
    39.       'Debug.Print ![ID] & " ==> " & bytDelta & " ==> " & intT      Test String
    40.     .MoveNext
    41.   Loop
    42. End With
    43.  
    44. 'If you've gotten this far, success
    45. fGenerateResultSet = True
    46.  
    47. rstResults.Close
    48. rstUniqueIDs.Close
    49. Set rstUniqueIDs = Nothing
    50. Set rstResults = Nothing
    51.  
    52. 'Finally, lets see the fruits of our labor
    53. DoCmd.OpenTable "tblFinalResults", acViewNormal, acReadOnly
    54. End Function
  4. Sample Function Call:
    Expand|Select|Wrap|Line Numbers
    1. If fGenerateResultSet() Then
    2.   MsgBox "Success!"
    3. Else
    4.   MsgBox "ADezii screwed things up again!"
    5. End If
  5. OUTPUT (tblFinalResults)
    Expand|Select|Wrap|Line Numbers
    1. ID    Delta    T
    2. 1      1    3
    3. 2      0    4
    4. 3      1    5
    5. 4      0    2
  6. Any questions, feel free to ask.
Sep 20 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.