Count IIf based on records above and below

[William McKinley]

I am trying to create a query to count transactions for an Employee if the transaction meets criteria from the above and below records. I am lost on what to do in a few areas in this situation. First, I'm not sure how to check against the records above and below on a table. Second, I'm not sure how to write this counting function. All of the records are for transactions based on an EmployeeID, OrderID, ItemID, PickLocation and some other information. Below is a breakdown of how I would like the count to work.

Counting daily transactions for an EmployeeID:

1. If the transaction's OrderID does not equal the previous/next transaction's OrderID, count the transaction.


2. If the OrderIDs are equal, check the transaction's ItemID. If the ItemID doesn't equal the previous/next transaction's ItemID, count the transaction.
3. If the ItemIDs are equal, check the transaction's PickLocation. If the PickLocation doesn't equal the previous/next transaction's PickLocation, count the transaction.
4. If the PickLocations are equal, don't count the transaction.

I've read that DBLookup might work when referring to the previous/next records, but it was also stated that the records must have a sequential ID that doesn't jump around. In this case, the HistorySequence will be in sequential order, but jumps around considerably for the specific EmployeeID. I also read an article saying that a custom VB function would be required to do something like this. Unfortunately, I have little to no VB experience.

Attached is an example spreadsheet showing what/what not to count. If you want to see how access table and it's data looks, remove the filter from EmployeeID. In this case, Employee1 would be credited for seven transactions.

I can post more information if necessary. Any help would be greatly appreciated.

[ajetrumpet]

I am lost on what to do in a few areas in this situation. First, I'm not sure how to check against the records above and below on a table.

This is impossible to do in the query utility unless you're using the running total technique as demonstrated by MS in their KB.

I've read that DBLookup might work when referring to the previous/next records, but it was also stated that the records must have a sequential ID that doesn't jump around.

that is what they MS KB article is based on.

In this case, the HistorySequence will be in sequential order, but jumps around considerably for the specific EmployeeID.

You can fix this by filtering a query and sorting ASC (WHERE id = employeeID ORDER BY ASC)

I also read an article saying that a custom VB function would be required to do something like this.

yes, more than likely. Are you willing to give it a shot yourself?

There might be someone else here that would write one for you. I could assist you as well, but not right now.

[weekend00]

what you want is the numbers of record not duplicate on (employeeID, orderID,ItemID,pickLocation), isn't it?

you are misleading us to do it with the logic of VBA code. but it can be done by 2 simply query.


We need two query to do this,
query1, get non duplicate records
select employeeD, orderID, ItemID, pickLocation from tablename group by employeeD, orderID, ItemID, pickLocation

query2: get the number of records for each employee

select employeeid, count(*) as num from query1 group by employeeid

[William McKinley]

what you want is the numbers of record not duplicate on (employeeID, orderID,ItemID,pickLocation), isn't it?

you are misleading us to do it with the logic of VBA code. but it can be done by 2 simply query.


We need two query to do this,
query1, get non duplicate records
select employeeD, orderID, ItemID, pickLocation from tablename group by employeeD, orderID, ItemID, pickLocation

query2: get the number of records for each employee

select employeeid, count(*) as num from query1 group by employeeid

That is what I intended to do initially, but then I had a curveball thrown in. Duplicate records that are not immediately preceding or following a record have to be counted.

[William McKinley]

There might be someone else here that would write one for you. I could assist you as well, but not right now.

I do not believe I am capable of something like this. I can post the database if someone wants to take a shot at it.

[ajetrumpet]

might as well post it and see if anyone helps you out

[William McKinley]

Well, the original database is chocked full of proprietary information, so I just included the example I posted with some more duplicate transactions.

I hate to just post a DB begging for a solution, but I'm really out of my league. I will keep working and posting updates as I try stuff out.

Thanks for the help so far.

[William McKinley]

Ok, I think I'm getting close to a solution. I applied an AutoNumber to the Pick Transactions table, Field TransID, with an ascending sort by employee and history sequence. I can sort the data I import, so I think it should work ok when I'm importing a lot of records.

Then I used the DBLookup function and came up with this function:

TransCredit: Count(IIf([PickLocation]<>DLookUp("[PickLocation]","Pick Transactions","[TransID]=" & [TransID]+1) Or [ItemID]<>DLookUp("[ItemID]","Pick Transactions","[TransID]=" & [TransID]+1) Or [OrderID]<>DLookUp("[OrderID]","Pick Transactions","[TransID]=" & [TransID]+1),1,Null))

| EmployeeID | HistorySequence | TransID | OrderID | ItemID | PickLocation | TransCredit |
| Employee1 | 1 | 1 | Order1 | Item6 | 105 - 058 - 100 | 0 |
| Employee1 | 6 | 2 | Order1 | Item6 | 105 - 058 - 100 | 0 |
| Employee1 | 11 | 3 | Order1 | Item6 | 105 - 058 - 100 | 1 |
| Employee1 | 16 | 4 | Order1 | Item1 | 102 - 069 - 100 | 1 |
| Employee1 | 21 | 5 | Order2 | Item1 | 102 - 069 - 100 | 1 |
| Employee1 | 26 | 6 | Order2 | Item5 | 105 - 033 - 100 | 1 |
| Employee1 | 31 | 7 | Order2 | Item3 | 103 - 010 - 100 | 1 |
| Employee1 | 36 | 8 | Order2 | Item4 | 105 - 001 - 100 | 0 |
| Employee1 | 41 | 9 | Order2 | Item4 | 105 - 001 - 100 | 1 |
| Employee1 | 46 | 10 | Order2 | Item5 | 105 - 033 - 100 | 1 |



The Results:

1. TransID's 1 and 2 weren't counted, because they contain duplicate criteria of TransID 3.
2. TransID's 6 and 10 were counted because they are separated by other transactions.
3. TransID 8 wasn't counted because it contains duplicate criteria of TransID 9.

It seems to work well in the test database I've been doing all of this in, however the live database will have 10,000+ records imported daily. Will I run into any performance issues when it is computing hundreds of thousands, even millions of records at a time?

[weekend00]

My solution:

Please create a table for result:
TransCount: employeeid text, countnum long

your tablename: tblOrder (modify the code to match you table)

Public Sub countCount()
    Dim rsEID As DAO.Recordset, rss As DAO.Recordset
    Dim IDstr As String, eid As String, counter As Long
    CurrentDb.Execute "delete * from transcount"
    Set rsEID = CurrentDb.OpenRecordset("select employeeid from tblorder group by employeeid")
    Do While Not rsEID.EOF
        Set rss = CurrentDb.OpenRecordset("select orderid + itemid + picklocation as IDs,historysequence from tblOrder where employeeid='" & rsEID!employeeid & "' order by historysequence")
        counter = 0
        IDstr = ""
        Do While Not rss.EOF
            If IDstr <> rss!IDs Then counter = counter + 1
            IDstr = rss!IDs
            rss.MoveNext
        Loop
        CurrentDb.Execute "insert into transcount (employeeid,countnum) values ('" & rsEID!employeeid & "', " & counter & ")"
        rsEID.MoveNext
    Loop
    Set rss = Nothing
    Set rsEID = Nothing
End Sub

[William McKinley]

Which do you think would work better?

[weekend00]

not necessary if you can use query.

[William McKinley]

Well, I have to consider using this in the live database. This thing is going to grow to 1,000,000+ records within a few months. If you think query performance is going to be an issue I can look at alternate solutions.

[weekend00]

..........................

[William McKinley]

Care to explain?

[weekend00]

I did not think dloopup could do it before. since you've got it done, I just wiped off what i said.