Loose Stock in Days

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a field, LsDays, where a calculation is made like this...
Lstock Day1 Day2 Day3 Day4 LsDays
75 25 20 30 10 3.0
In other words LsDays is telling me how many days stock I've got. Days1-4
can be as high as Day50.
I'm afraid this has beaten me can anyone help me out?
 
You should store the data vertically, not horizontally, like:

StockID, GetStock, DayNumber ' fields
xxx, 75 0
xxx -25 1
xxx -20 2
xxx -30 3
xxx -10 4
....
yyy 120 0 'starting another sequence



So, now, we can make a running sum, and pick the bigger (maximum )
daynumber for which the running sum is still positive (or the smallest one
for which it is negative, your choice).




I personally like to make running sum though join:


SELECT a.StockID, a.dayNumber, SUM(b.GetStock) AS running
FROM myTable As a INNER JOIN myTable As b
ON (a.stockID = b.stockID AND a.dayNumber >= b.dayNumber)
GROUP BY a.StockID, a.dayNumber


saved it as qu1.


SELECT StockID, MAX(dayNumber)
FROM qu1
WHERE running >= 0 ' or running <0, your choice


should give the desired result.



Hoping it may help,
Vanderghast, Access MVP
 
Calculated values should not be stored in a table; they should be calculated
on the fly in a query or on a form or report. Please explain more what you
are doing and we can help you design the correct tables.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
Are you using Access or Excel? As Michel points out, Access works (better)
with data organized vertically.

The example you gave seems like the values would have to be entered. Are
they being calculated based on some other aspect?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The data looks like this in Access...
TableA PartNum Lstock
12345 75
67890 700
TableB PartNum Day1 Day2 Day3 Day4 Day5
12345 30 20 25 10 10
67890 250 240 200 0 100
The query would ideally look like this...
Query1 PartNum Lstock LsDays
12345 75 3.0
67890 700 4.1
Day1 is always today and calendar days are work days. If LsDays becomes < 3.0
I need to start worrying about getting the stock to plant. Of course I can
always do...
Day1+Day2+Day3 > Lstock Show PartNum etc

I think the answer to this is in Michel's post -
thanks for the previous replies...
 
Back
Top