how many times a particular row has been selected

[jaimenjones]

I was wondering if there was a way to have a coulm in a table that can generate how many times a particular row has been selected yes active (yes/no data type) .
Example.
*Row*
1. Yes
2. No
3. Yes
4. Yes
5. Yes
6. Yes
7. Yes
8. No
The column would report
1. 1
2. 0
3. 1
4. 1
5. 1
6. 1
7. 1
8. 0
The next week I come and change the data to
1. No
2. Yes
3. Yes
4. No
5. No
6. Yes
7. Yes
8. No
The column would update to report (original + new active status)
1. 1
2. 1
3. 2
4. 1
5. 1
6. 2
7. 1
8. 0

 

[CJ_London]

there is always a way - in this case I think you would need to have a history table which is updated everytime your option is selected to true. Suggest the history table needs to contain as a minumum the following fields

ID autonumber PK
RowID - long FK
SelectedDate - date - default now()

So to determine how many times a row is selected you would count the number of records for that id - and can use the date to limit the count is some way

 

[jaimenjones]

there is always a way - in this case I think you would need to have a history table which is updated everytime your option is selected to true. Suggest the history table needs to contain as a minumum the following fields

ID autonumber PK
RowID - long FK
SelectedDate - date - default now()

So to determine how many times a row is selected you would count the number of records for that id - and can use the date to limit the count is some way

Thanks for the reply. I don't know much about access (let alone history tables). Would you have an example of this so I can see exactly how it is done? Thanks.

 

[CJ_London]

A history table is just a table, there is nothing special about it so create your table as suggested in my previous post and call it tblHistory

in the before update event of your form put the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If [COLOR=red]Selected[/COLOR].OldValue <> [COLOR=red]Selected[/COLOR] And [COLOR=red]Selected[/COLOR]=- 1 Then
        currentdb.execute("Insert Into tblHistory ([COLOR=red]RowID[/COLOR]) Values (" & [COLOR=red]RowID[/COLOR] & ")")
    end if
    
End Sub

You'll need to change the bits in red - Selected is the name of your control the user selects with (I'm assuming it is an option box) and RowID is the autonumber field for the table in your subform

 

[jaimenjones]

A history table is just a table, there is nothing special about it so create your table as suggested in my previous post and call it tblHistory

in the before update event of your form put the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    If [COLOR=red]Selected[/COLOR].OldValue <> [COLOR=red]Selected[/COLOR] And [COLOR=red]Selected[/COLOR]=- 1 Then
        currentdb.execute("Insert Into tblHistory ([COLOR=red]RowID[/COLOR]) Values (" & [COLOR=red]RowID[/COLOR] & ")")
    end if
 
End Sub

You'll need to change the bits in red - Selected is the name of your control the user selects with (I'm assuming it is an option box) and RowID is the autonumber field for the table in your subform

Humm... I don't think I am doing this right. Please see the attached database. Thanks.

 

[CJ_London]

You need to create a form to do this - you should not give users direct access to tables or queries. See attached as a demo. Since you have two tables I have modified the historytable and code very slightly.

Note the history table is only updated when the record is updated - otherwise a user could repeatedly click the action control