Query to flag daily change in order status

[Relyuchs]

I have a table for Order_History where I import an excel file from our ordering system daily. This Order_History table houses all historical order data for a 6 month period. The fields in this table are: [Order_no], [Status_Code], [Date_Code_Chg] and [Report_Date]. The [Status_Code] field indicates which step in our ordering process that the order is in. [Date_Code_Chg] indicates the date that the order moved from one step to another. [Report_Date] indicates the date that the information was imported into the table.



I need a query that I will run daily to provide me with the beginning count of orders in each Status_Code, and a count of how many orders in that Status_Code that moved to a different status the next day. The query would need to compare the record for each order on a given day against the record for the same order the previous day and let me know if there has been a change from one day to the next. This needs to be done for every order in the table. Note that even though there will be duplicates in the [Order_no] field, each row of data is individual because the [Report_Date] field is different every day. Hope I’m making sense. Thank you for any help you can give – I’m very limited in writing anything other than simple queries.

[NTC]

beginning count of orders in each Status_Code
* Should be a straight forward AggregateQuery; groupon by StatusCode, and criteria of date

count of how many orders in that Status_Code that moved to a different status the next day.
* create a Duplicates query against current day's record set and prior day's record set. Of course auto finding the 'prior' work day might be the trickiest part of this....

Very do-able just gotta noodle thru it. 1 query doesn't do it all of course - but you can put the results all together in a Report or on a Form as subobjects...

Hope this helps.