create query with a loop (in SQL?)

[Guest]

I have a query which has the following data

Symbol Days Pric
A 15
B 15 1
C 45 2
etc..

I have a function which multiplies days*price. What I would like to do is create a query which will take the amount of days and go through a sequential subtraction iteration on days (until zero) and show the results in a query

Example
A 15 5 7
A 14 5 7
A 13 5 6
...
B 15 10 15
B 14 10 14
......
C 1 20 2

Any and all help would be greatly appreciated :

thanks
Zac

 

[Ted Allen]

Hi Zac,

Try the following:

SELECT TblMain.Symbol, TblNums.RowNum, TblMain.Price,
[RowNum]*[Price] AS ExtendedPrice
FROM TblMain, TblNums
WHERE (((TblNums.RowNum)<=[Days]))
ORDER BY TblMain.Symbol, TblNums.RowNum DESC;

where TblMain is the name of your table with the Symbol,
Days, Price info (those are the field names used above as
well) and TblNums is a table with one field (RowNum) with
sequential numbers from 1 to some number greater than the
largest value you ever expect for days.

This query is based on the idea that when you create a
query on based on two tables with no defined
relationship, the result is every possible combination of
the records in the two tables. But, the results are
further limited by the criteria that RowNum <= Days,
which prevents the combinations higher than the number of
days.

Hope this helps. Post back if you have any problems with
it.

-Ted Allen

-----Original Message-----
I have a query which has the following data:

Symbol Days Price
A 15 5
B 15 10
C 45 20
etc...

I have a function which multiplies days*price. What I

would like to do is create a query which will take the
amount of days and go through a sequential subtraction
iteration on days (until zero) and show the results in a
query.

 

[Gary Walter]

I have a query which has the following data:

Symbol Days Price
A 15 5
B 15 10
C 45 20
etc...

I have a function which multiplies days*price. What I would like to do is

create a query which will take the amount of days and go through a
sequential subtraction iteration on days (until zero) and show the results
in a query.

Example:
A 15 5 75
A 14 5 70
A 13 5 65
...
B 15 10 150
B 14 10 140
......
C 1 20 20

Hi Zac,

This sounds like an "enumeration" problem.

You need a number table with one PK field
that goes from 0 to largest Days you expect.

Michel calls this the "Iotas" table (field = "Iota", type Long)

It is easy to build (example for 0 to 999):

-Make a table "Ds" with one field "D", type Long.

-Bring this table into a new query 3 times.

-In field row in your query grid, type

Ds.D+Ds_1.D*10+Ds_2.D*100

-Change to an append query and tell append to Iotas

- This is what the SQL will look like:

INSERT INTO Iotas ( Iota )
SELECT Ds.D+Ds_1.D*10+Ds_2.D*100 AS Expr1
FROM Ds, Ds AS Ds_1, Ds AS Ds_2;

You should end up with table "Iotas" having
one field "Iota" with records going from 0 to 999.

Now (if I understood correctly), the following
query will give the result I believe you wanted
(replace "YourQuery" with name of your query):

SELECT
Q.Symbol, Iotas.Iota AS EnumDays,
Q.Price,
[Price]*[Iota] AS EnumPrice
FROM YourQuery AS Q, Iotas
WHERE (((Iotas.Iota)>0) AND ((Q.Days)>=[Iotas].[Iota]))
ORDER BY Q.Symbol, Iotas.Iota DESC;

Your sample data would produce:

Symbol EnumDays Price EnumPrice
A 15 5 75
A 14 5 70
A 13 5 65
A 12 5 60
A 11 5 55
A 10 5 50
A 9 5 45
A 8 5 40
A 7 5 35
A 6 5 30
A 5 5 25
A 4 5 20
A 3 5 15
A 2 5 10
A 1 5 5
B 15 10 150
B 14 10 140
B 13 10 130
B 12 10 120
B 11 10 110
B 10 10 100
B 9 10 90
B 8 10 80
B 7 10 70
B 6 10 60
B 5 10 50
B 4 10 40
B 3 10 30
B 2 10 20
B 1 10 10
C 45 20 900
C 44 20 880
C 43 20 860
C 42 20 840
C 41 20 820
C 40 20 800
C 39 20 780
C 38 20 760
C 37 20 740
C 36 20 720
C 35 20 700
C 34 20 680
C 33 20 660
C 32 20 640
C 31 20 620
C 30 20 600
C 29 20 580
C 28 20 560
C 27 20 540
C 26 20 520
C 25 20 500
C 24 20 480
C 23 20 460
C 22 20 440
C 21 20 420
C 20 20 400
C 19 20 380
C 18 20 360
C 17 20 340
C 16 20 320
C 15 20 300
C 14 20 280
C 13 20 260
C 12 20 240
C 11 20 220
C 10 20 200
C 9 20 180
C 8 20 160
C 7 20 140
C 6 20 120
C 5 20 100
C 4 20 80
C 3 20 60
C 2 20 40
C 1 20 20


Please respond back if I was not clear about something.

Good luck,

Gary Walter