Hello fellow Access nerds! After many years, I've finally made the post I typically google for.
I have a dataset that regularly reports which products are absent on a warehouse stockcheck, which I am trying to use to analyse when stock is or isn’t available. I’m essentially trying to identify “Has a part been reported as missing? --> If so, count the number of days it is missing until another part in the same category is reported as missing, but the original part was not reported as missing on that date (as we can assume it’s back in stock)”
I’ve managed to make this work in excel, but my spreadsheet began to die from the calculation of 5 locations worth of categories and parts, let alone across the 600+ I’m working on! As a result, I’m trying to set up a similar function in Access to analyse which, and for how long, parts were out of stock.
My dataset looks something like:-
My assumptions are that:-
My initial Access plan was to set up three crosstab queries, to mirror my three excel tables. I can make Table1 and Table2 very easily, but for the life of me can’t make table3 work (currently have a calculated expression that mirrors the formula I had in table 3, but something has gone amiss…).
I’m looking for a steer/advice on setting up the expression in my crosstab query, or other ideas/approaches I could use to calculate how long each part is missing for. Any help would be greatly appreciated, as I’ve lost my mind going in circles today!
Edit:- have uploaded some sample data and my working excel version of what I'm trying to replicate.
I have a dataset that regularly reports which products are absent on a warehouse stockcheck, which I am trying to use to analyse when stock is or isn’t available. I’m essentially trying to identify “Has a part been reported as missing? --> If so, count the number of days it is missing until another part in the same category is reported as missing, but the original part was not reported as missing on that date (as we can assume it’s back in stock)”
I’ve managed to make this work in excel, but my spreadsheet began to die from the calculation of 5 locations worth of categories and parts, let alone across the 600+ I’m working on! As a result, I’m trying to set up a similar function in Access to analyse which, and for how long, parts were out of stock.
My dataset looks something like:-
Location Number | Location | Category | Date Reported | Part number | Part Description | Order number |
1 | London | Car | 03/06/2021 | 2021 | Wheel | 1 |
1 | London | Bus | 03/06/2021 | 1491 | Seat | 2 |
2 | Manchester | Car | 05/06/2021 | 2021 | Wheel | 3 |
My assumptions are that:-
- My data is fed by individual workers who each cover a location, and check all stock for a random selection of categories each visit (with the idea that they cover all of their location’s categories within a certain number of visits) and record which parts are missing. There is no particular visit plan – it can be a random number of days between each visit. This data gets fed into a central table, which I have access to.
- As my workers may not check all categories in a location on each visit, I must assume that a previously reported missing part is OOS until they check products in the same category, but do not report that part again.
- Table1, to look if my concatenated code was reported for each day (and if so, output 1 – essentially working in days) – essentially, was each part reported as missing for each category and location?
- Table2, to look if any parts were reported for each category, for each location – essentially, how many parts were reported for each category for each location, and a value greater than 0 means we can assume that that category at that location has been checked by my workers for that date.
- Table3, that for each location+category+day asked as a formula – IF(category was checked as per table2 = yes , pull the value of 1 for that part/location/category in table 1 , re-use yesterday’s value for this part/location/day in this table). For the 1st day in my date range, I used the values for table1 for that day as a “starting up” point.
My initial Access plan was to set up three crosstab queries, to mirror my three excel tables. I can make Table1 and Table2 very easily, but for the life of me can’t make table3 work (currently have a calculated expression that mirrors the formula I had in table 3, but something has gone amiss…).
I’m looking for a steer/advice on setting up the expression in my crosstab query, or other ideas/approaches I could use to calculate how long each part is missing for. Any help would be greatly appreciated, as I’ve lost my mind going in circles today!
Edit:- have uploaded some sample data and my working excel version of what I'm trying to replicate.
Attachments
Last edited: