calc in query - sql

[Guest]

am wrestling w/ a calc and syntax:

warning though - you have to kind of like math puzzles!!

in Form1 user selects a value 500 from Listbox1

Current Table1:

Loc Ref Quant Series

A 1 1350 1350
B 1 1000 2350
C 1 400 2750
C 2 350 350
D 2 600 950

-------
in plain english - above table1 Series runs sequencially for Ref 1 across
multiple Locs and resets when there is a new Ref 2....this table is in place.

needed query result based upon the 500 selected value (which next user might
select a different value) - the new Boxed value can only increment in the
selected value (i.e. 500) but not exceed the Series level.

Query Result:

Loc Ref Quant Series Boxed(this value from form)

A 1 1350 1350 500
A 1 1350 1350 1000
A 1 1350 1350 1350
B 1 1000 2350 1850
B 1 1000 2350 2350
C 1 400 2750 2750
C 2 350 350 350
D 2 600 950 850
D 2 600 950 950
--
I have the query built so it will accurately re-duplicate all the needed
repeated records - and am wrestling with the math/syntax/sql for the Boxed
calculation column....and plugging in that value (500 in this case) from the
form as part of the calc......

much thanks to those further into sql than I am at this point to help on this.

NTC

 

[Marshall Barton]

am wrestling w/ a calc and syntax:

warning though - you have to kind of like math puzzles!!

in Form1 user selects a value 500 from Listbox1

Current Table1:

Loc Ref Quant Series

A 1 1350 1350
B 1 1000 2350
C 1 400 2750
C 2 350 350
D 2 600 950

-------
in plain english - above table1 Series runs sequencially for Ref 1 across
multiple Locs and resets when there is a new Ref 2....this table is in place.

needed query result based upon the 500 selected value (which next user might
select a different value) - the new Boxed value can only increment in the
selected value (i.e. 500) but not exceed the Series level.

Query Result:

Loc Ref Quant Series Boxed(this value from form)

A 1 1350 1350 500
A 1 1350 1350 1000
A 1 1350 1350 1350
B 1 1000 2350 1850
B 1 1000 2350 2350
C 1 400 2750 2750
C 2 350 350 350
D 2 600 950 850
D 2 600 950 950

I don't see a need for table1 (which I called Packaging1) to
have the series field. It is readily calculated from the
other values.

Given that I have understood your example correctly, Create
a table named Numbers with one field named NumCopies.
Populate the table with 100 records each a sequential
integer: 0,1,2,3,4, ... ,99

Then you can try this kind of query:

SELECT Packaging1.Loc,
Packaging1.Ref,
Packaging1.Quant,
Numbers.NumCopies AS Num,
[Enter Box Size] AS BS,
(SELECT Sum(X.Quant)
FROM Packaging1 As X
WHERE X.Ref = Packaging1.Ref
AND X.Loc <= Packaging1.Loc) AS Series,
IIf((NumCopies+1)*[Enter Box Size] <= Quant,
(NumCopies+1)*[Enter Box Size], Quant) +
Nz((SELECT Sum(Y.Quant)
FROM Packaging1 As Y
WHERE Y.Ref = Packaging1.Ref
AND Y.Loc < Packaging1.Loc), 0) AS Boxed
FROM Packaging1, Numbers
WHERE NumCopies * [Enter Box Size] <= Quant
ORDER BY Packaging1.Ref,
Packaging1.Loc,
Quant,
Numbers.NumCopies

Replace the parameter prompt string [Enter Box Size] with a
reference to your listbox.

I hope that's sufficient to get you going because I will be
out of touch for the next week and unavailable for followup
questions.