Count IIf based on records above and below

[oldman]

Try an easy approach. I have set up a query that can be run at any time and produces an auxiliary table with no duplications. Run the query then go to your counting crosstab query based on the sample in T Counting Table. As long as the order number, item numbers and locations are not all repeated for the same employee for items that should count, this will give an accurate count. I ignored the up down aspect since a unique order number by an employee with unique items and unique locations was a single order. If the order number, location or item number changed, I assumed it should count as another order for that employee.

[William McKinley]

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

Ahh sorry, as I am a novice, I assumed I said something wrong .

[oldman]

I see nothing at all wrong with your answers William. I am not fluent in SQLs so my solutions always simply involve using the nice looking query building displays that MS provides for us to use as users. I have merely used what little I know how to do to try to solve a problem after first ignoring all hints about how they would go about it. If I published the SQL, I would only just barely be able to read it but I have designed a rather extensive database that primarily reads records over an ODBC connection and organizes things for people to use as simple users. I have it organized to use simple macros to update things in the database. When a revision to the software comes out, my databases convert flawlessly to the new software version because I practice the KISS principle, Keep It Simple Stupid.

[William McKinley]

I see nothing at all wrong with your answers William. I am not fluent in SQLs so my solutions always simply involve using the nice looking query building displays that MS provides for us to use as users. I have merely used what little I know how to do to try to solve a problem after first ignoring all hints about how they would go about it. If I published the SQL, I would only just barely be able to read it but I have designed a rather extensive database that primarily reads records over an ODBC connection and organizes things for people to use as simple users. I have it organized to use simple macros to update things in the database. When a revision to the software comes out, my databases convert flawlessly to the new software version because I practice the KISS principle, Keep It Simple Stupid.

Ugh, the macros... I'll have to set those up soon too. Be on a look out for that thread .

[oldman]

Macros are a breeze. You list each thing you want the macro to do. Always start with a setwarnings to NO, or the thing will really bug you for letting it change things. End with 2 things, setwarnings to YES and mgsbox with a message that it is done.
In between you openquery, the name of the query goes into the box at the bottom of the page, as often as it takes to accomplish what you are doing. In this case it looks like about 2 queries and leave the crosstab query open for manual closure unless you want to write that crosstab result to a new table. In that case, set up a query to purge the results table by deleting all records, then run the append query, the crosstab that writes back to the results table and open the report that gets its data from the results table. The end result is a report ready to print in less than a minute based on as much data as your organization can generate in a week, or whatever time period you select. If you print preview the report, you don't even need the msgbox because you will know it is done.