I have a table in Access 365 with this column: "tblfreq"
| Col1 |
|---|
| 0 |
| 0 |
| 1 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 1 |
| 0 |
| 0 |
| 1 |
| 0 |
I want to create a query that shows ranks/count(?!) rows between the value "1" of "Col1". "Col1" only has "0" and "1". That would create a query with a column "colaux" result like this:
| Col1 | Caux |
|---|---|
| 0 | 1 |
| 0 | 2 |
| 1 | 1 |
| 0 | 2 |
| 0 | 3 |
| 0 | 4 |
| 0 | 5 |
| 0 | 6 |
| 1 | 1 |
| 0 | 2 |
| 0 | 3 |
| 1 | 1 |
| 0 | 2 |
CodePudding user response：
as June 7th said there must be a unique identifier for each row. just add an autonumber column to the table. then you can calculate caux. if you have a fresh autonumber column which will go 1,2, 3, 4, 5, etc then you can actually just subtract id's to get the rows between ids, but I went ahead and counted rows anyway. The expression for calculating caux is long and I needed a function to calculate the first row in tblFreq where col1=1 anyway so I abstracted most everything to the function getCaux.
after adding the autonumber column ID go to the create tab on the ribbon and add a code module with the following
'Utilities
Private firstIDtblFreqcol1is1 As Long
Public Function getfirstIDtblFreqcol1is1() As Long
If firstIDtblFreqcol1is1 = 0 Then
firstIDtblFreqcol1is1 = DMin("ID", "tblFreq", "col1=1")
End If
getfirstIDtblFreqcol1is1 = firstIDtblFreqcol1is1
End Function
Public Function mostrecentrowwithcol1is1(id As Long) As Long
mostrecentrowwithcol1is1 = DMax("ID", "tblFreq", "ID < " & id & " AND col1 = 1")
End Function
'if col1=1 caux =1
'first row where col1=1 is 3 so if id < 3 caux is column count
'else caux = column count(id) - column count(id last row with col1=1)
Public Function getCaux(id As Long) As Long
If DLookup("col1", "tblFreq", "ID = " & id) = 1 Then
getCaux = 1
ElseIf id < getfirstIDtblFreqcol1is1 Then
getCaux = DCount("ID", "tblFreq", "ID <= " & id)
Else: getCaux = DCount("ID", "tblFreq", "ID <= " & id) - DCount("ID", "tblFreq", "ID <= " & mostrecentrowwithcol1is1(id)) 1
End If
End Function
Then the query is just:
CodePudding user response：
I trully don't understand what are you trying to accomplish in the end. What info is encoded in the table tblfreq. What knowledge do you want to extract from that table. Not in db language, in plain language.