Pls help putting this in access query (1 Viewer)

[Kiron]

Need help with applying an Excel formula in my access tool.

Excel formula is
=If(a1<>a2,1,b+1)

Basically what it does is it checks the current value with previous value and returns a 1 if duplicate and if not duplicate it adds the value by 1. If any change in the sequence is found, the counter resets to 0 and starts counting for that new value.
I need this to work in access but dont know how.
Basically am trying to locate the 20th untouched record from my database for audit. So i am bucketing them in 20s.

My current access formula in query does
Dcount("id","table","id<=" & id & "consol='" & consol &"'") mod 20 which works fine for a counter but doesn't reset counter to 0 when change in record is found.

Problem
Consolidate counter
Kiraccden 1
Kiraccden 2
Kiraccden1 1
Kiraccden 3
Kiraccden 4

Required solution
Consolidate counter
Kiraccden 1
Kiraccden 2
Kiraccden1 1
Kiraccden 1
Kiraccden 2

In above example i expect the case 3 to reset my counter to 1. Instead it continues from case 2.

Please advise on a formula i can put to my query to do this please.

Thanks heaps champs.

 

[arnelgp]

always add autonumber to your table, this is very helpful.
when you already have the autonumber field, create this function in a module:

public function fnCounter(id as long, tbl as string) as long
dim sConsolidate as string
dim i as long
with currentdb.openrecordset("select * from " & tbl & _
" order by yourAutoNumberFieldName;")
    if not (.bof and .eof) then 
        .findfirst "autoNumberFieldName = " & id
        sConsolidate = !Consolidate
        do while not .bof
            if sConsolidate<> !Consolidate then exit do
            i = i + 1
            .moveprevious
        loop
    end if
end with
fnCounter = i
End function

now build your query:

select id, consolidate, fnCounter([id], "yourTableNameHere") as counter from yourTableNameHere;"

 

[Pat Hartman]

I would rethink the whole concept of what you are trying to do. In a relational table all rows are independent and no row has any dependency on any other row. Also, rows are an unordered set. That means that there is no guaranteed order to a recordset that is returned to you UNLESS you specifically sort it by something that will make the rows appear in a consistent, specific order. Using your example, there is absolutely no way to do what you are asking because there is no way to force the sequence shown given that there is nothing you can sort by. That is why arne suggested adding an autonumber. However, given that the rows are already in the table, the autonumber may not be applied to them in the order you expect.

Flat files such as Excel work very differently from RDBMS' such as Access. Don't let the similarity in DS view fool you. Behind the scenes there is a world of difference. Some things such as maintaining order without a unique ID to sort on are very easy in Excel because it "remembers" the order in which you entered the data and if you don't sort it, the data is always returned in that order. Not so with RDBMS'. Data is returned at the convenience of the database engine NOT how it is physically stored. This is much more apparent with a large file than a small one. Think about it this way, disk platters spin. Read/Write heads pick up a segment of data at a time, As that data is being transferred, the head is moving and it just drops down and picks up the next segment that passes under it. Of course solid state drives work differently and may in fact always return rows in physical order but I can't say and if you can't specify what type of drive your data is stored on, you cannot depend on any sequence UNLESS your query includes an order by clause.

 

[Kiron]

Hi arnelgp
Your solution worked. Am able to get the desired result.
Thanks heaps. U r a true champion.
Really Appreciate your help