Setting up crosstab queries re: stock counts (1 Viewer)

LiamKJ

New member
Local time
Today, 14:21
Joined
Jun 3, 2021
Messages
4
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:-

Location NumberLocationCategoryDate ReportedPart numberPart DescriptionOrder number
1LondonCar03/06/20212021Wheel1
1LondonBus03/06/20211491Seat2
2ManchesterCar05/06/20212021Wheel3


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.
I made this work on excel by setting up another column that concatenated my location, part number, and date reported, and then set up three tables (all of which are essentially locations, categories, and parts down my X axis, and dates across the Y axis):-
  • 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.
When I look at Table3, I can visually the run of days products were out of stock, and can from there crunch numbers related to that, which is what I want!

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

  • Example excel data - stock analysis - crosstab.zip
    22.4 KB · Views: 38
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 06:21
Joined
Oct 29, 2018
Messages
15,573
Hi. Welcome to AWF!

Are you able to post some sample Excel files to give us a better idea of what you're trying to achieve?
 

LiamKJ

New member
Local time
Today, 14:21
Joined
Jun 3, 2021
Messages
4
Cheers! Absolutely, it'll be tomorrow by the time I can replicate with some dummy data, but I'd appreciate it if you could take a look once I've thrown some up!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:21
Joined
Oct 29, 2018
Messages
15,573
Cheers! Absolutely, it'll be tomorrow by the time I can replicate with some dummy data, but I'd appreciate it if you could take a look once I've thrown some up!
No worries. I just couldn't imagine needing to use crosstab queries, unless you're still thinking the "Excel" way, rather than the "Access" way.
 

LiamKJ

New member
Local time
Today, 14:21
Joined
Jun 3, 2021
Messages
4
I've attached an excel file with a sample dataset, and showing my three (now four!) tables.

I've essentially loaded my dataset into access, and have set up queries (first a regular, then also showing in a crosstab) for tables 1 and 2, but am super stuck writing the queries for 3 & 4.

I think you've stuck the nail on the head - it's very likely I am thinking in Excel terms, rather than Access - any suggestions for smarter/streamlined approaches would be very appreciated!
 

Attachments

  • Example excel data - stock analysis - crosstab.zip
    22.4 KB · Views: 41

arnelgp

once i caught a fish alive...
Local time
Today, 21:21
Joined
May 7, 2009
Messages
13,236
how come there are Same part number for different Car/Bus?
 

LiamKJ

New member
Local time
Today, 14:21
Joined
Jun 3, 2021
Messages
4
Same parts, but stored in different locations. Some restraints on how I would structure it, as I'm fed the data as a list of missing parts from multiple locations, and am trying to identify what isn't present at each location, and for how long it has been missing.
 

Users who are viewing this thread

Top Bottom