Tracking Changes - Concatenate / Join

chris1987 — Tue, 17 Jan 2017 15:03:19 GMT

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

topic Tracking Changes - Concatenate / Join in App Development

Tracking Changes - Concatenate / Join