Help: Query in which rows grow and reference previous rows to calculate rate increase

[lostmySQL]

Basically I am trying to create a query that would dynamically grow based on another table of of labor positions and would also forecast their yearly increase. I want to avoid having to create new columns and calculations for every new year.


Data: Table 1: LCAT

LCAT	Base Wage
General Worker	$4.00
General Admin	$5.00

Data: Table 2: Escalation Rate

Year	Escalation
Year 1	0%
Year 2	4%
Year 3	4%

Desired Query Result:

LCAT	Year	Annual Rate
General Worker	Year 1	$4.00
General Worker	Year 2	(4.00 * (1+4%) = $4.16
General Worker	Year 3	(4.16 * (1+4%) = $4.33
General Admin	Year 1	$5.00
General Admin	Year 2	(5.00 * (1+4%) = $5.20
General Admin	Year 3	etc...

[CJ_London]

not quite the format you wanted but something like this is a way that could work

SELECT Table1.LCAT, [BaseRate]*[YR1] AS ar1, [BaseRate]*[YR2] AS ar2, [BaseRate]*[YR3] AS ar3
FROM (SELECT 1+[y1].[escalation] AS yr1, [yr1]*(1+[y2].[escalation]) AS yr2, [yr2]*(1+[y3].[escalation]) AS yr3
FROM Table2 AS y1, Table2 AS y2, Table2 AS y3
WHERE (((y1.Yr)=1) AND ((y3.Yr)=3) AND ((y2.Yr)=2)))  AS E, Table1;

LCAT	ar1	ar2	ar3
worker	4	4.16	4.3264
admin	5	5.2	5.408

you can use a union query to get it into the vertical format

[June7]

Queries pulling from multiple tables and don't have JOIN clause invoke Cartesian relationship - every record of each table associates with every record of other table. This type of query can perform slowly with large dataset. If query performs so slowly to be non-functional, should probably create records for each year.

[Gicu]

Looks like your question is related to this earlier one, might want to have a look at the sample:
https://www.accessforums.net/showthr...148#post493148

Cheers,