Hi
I have a table that contains the following Key Fields:
Req Date, Order Number, Line Number, Qty
I am adding a column called "Status" by adding the following:
IF(today()-[Req Date]<=7,'1to7',if(today()-[Req Date]<=14,'8to14',if(today()-[Req Date]<=21,'15to21',if(today()-[Req Date]<=28,'22to28','Over28'))))
and adding this field so I can refer back to what a position was at a selected date:
today() as [Date Added]
So I end up with a table containing the following fields Req Date, Order Number, Line Number, Qty, Status,Date Added
I need to track the following:
New items:
Items that were not in arrears but are now
Status Change:
Items that were on the report previously but now moved Status, e.g. they were 1to7 days yesterday, but today they are 8to15days old.
Cleared:
Items that were in arrears before but are no longer (they will disappear from the main import if they are no longer in arrears)
What I was thinking is create a master table (Called "Master") which would be my starting point. I would then do the following when the import runs each morning:
1) Find what is new and store in the "New Arrears" table:
So I would check for items that exist in the import but not in the "Master" table and store these in "New Arrears" (along with today() as [Date Added])
2) Find Items where the status has changed and store in the "Changes" table:
I would check for items that exist in the "Master" table AND in the Import, but where "Status" is different - I would then store these in the "Changes" table (along with today() as [Date Added])
3) Find Items that have been cleared and store in the "Cleared" table:
I would check for items that exist in the "Master" table but not in the Import - I would then store these in the "Cleared" table (along with today() as [Date Added])
4) Updated the "Master" table ready for the next run:
I would then add all items that exist in the import but not in the "Master" table into the "Master" table ready for the next time the report is run.
I will then be able to select a date and see what was in arrears, how old it was, what was getting older and what had been cleared in a graph.
I've play around a lot with concatenate but I'm struggling as it's the first time I've really used it in this way.
Please can someone point me in the right direction or advise a better way to do this? An example would be great if someone could take the time to do that.
Thanks in advance
Chris
