Query to calculate difference between year 1

[corobori]

Say a table with the following fields showing salary and benefits

Year
EmployeedId
ItemId
Amount

Containing the following records

1999; 1; 1; 52000
1999; 1; 2; 1000

2000; 1; 1; 50000
2000; 1; 2; 1000
2000; 1; 3; 1000

2001; 1; 1; 60000
2001; 1; 2; 2000

2002; 1; 1; 69000
2002; 1; 2; 4000
2002; 1; 3; 2000

I need to make a graphic showing the percentage evolution of the money
each employee is receiving this way

1999; 0
2000; -2%
2001; 19%
2002; 21%

Which query could do the trick ?

Jean-Luc

 

[WildHare]

I think you're going to need more than just a simple query to do this. Access (or more correctly, SQL) has a bit of a problem doing record-by-record manipulations like this, where essentially you need to look at the "previous" record within this "key" in order to make a calculation. This LOGICAL KEY operation means that a simple "PREVIOUS" positional operation will not necessarily be correct. So essentially you need to
1) Grab the First record.
2) Set this "Previous Amount" field to "null"
3) Save the "current" amount value.
4) Go to the NEXT LOGICAL Record according to KEY structure.
5) Compare the Amount HERE to the amount "saved" in step3 to do your calculation
6) Go to step 3 and repeat as necessary.

See the problem? SQL is a SET oriented guy, not a record-by-record thing.

This is NOT to say that this can not be done. You have two options - restructure the table a bit, so that you STORE the value from the prior year when you add a new record for an employee for a year. While not relationally pure, and Celko and Date would probably skin me alive for it, will work so long as maintenance to these records is controlled and the possibility of fudging with the records is minimized.

The other option is to write a function that will populate a temporary table with your values, using recordset movement operations, and then perhaps using a query or two on it. Since I hate writing code when a database operation will suffice, I would probably lean to the former option.

Perhaps some other denizens here can offer you a better answer with a SQL based solution that I do not have the expertise to come up with right now.

I'd be happy to persue this if you wish, though, because it's an interesting, and common enough, problem.

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[gol4]

Just a suggestion but I think using a subquery should produce the results

Not tested but something like this should be close

select year, sum(amount)/(select sum(amount) from table1 as a where a.year = table1.year -1)
from table1
group by year

should work same as ranking or ordering

will need to do some formatting and obviously some further criteria and grouping by employee

 

[corobori]

SELECT CLng(

.[Year]) AS [Year], Table.EmployeeID, Sum(Table.Amount) AS SumOfAmount
FROM Table
GROUP BY CLng(

.[Year]), Table.EmployeeID;

Somebody suggested me the following and it works:

save this query (in my case QRY1)

now you can use this query

SELECT QRY1.Year, ([QRY1].[SumOfAmount]-[QRY1_1].[SumOfAmount])/[QRY1_1].[SumOfAmount] AS Expr1
FROM QRY1, QRY1 AS QRY1_1
WHERE (((QRY1_1.Year)=[QRY1].[Year]-1) AND ((QRY1_1.EmployeeID)=[QRY1].[EmployeeID]));

Notice that 1999 will not be in the output (it will be always 0)
If you really want this record too, you should use an union query

 

[WildHare]

Ok, I've tried this with some sample data, and I'm getting wacky results. I'll investigate further, but you might want to hand-calculate your answers to MAKE SURE you're getting the one's you want....

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[WildHare]

Works correctly now - here's my SQL of the above response, modified to fit my table (empid, year, amount). I had to order by EMPID and YEAR...

----------------------------------------
SELECT QRY1.EmpID, QRY1.Year, QRY1.SumOfAmount, ([QRY1].[SumOfAmount]-[QRY1_1].[SumOfAmount])/[QRY1_1].[SumOfAmount] AS Expr1
FROM QRY1, QRY1 AS QRY1_1
WHERE (((QRY1_1.Year)=[qry1].[year]-1) AND ((QRY1_1.EmpID)=[qry1].[empid]))
ORDER BY QRY1.EmpID, QRY1.Year;
----------------------------------------

Excellent work. Stars to all...

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[gol4]

Seems a bit late for my follow up but
No need to do 2 querys just use a sub with an alias and format the cell as percentage

something like this should be close

select tbl.[employeeid], [tbl].[year],1-((select sum(a.amount) from tbl as a where a.[year] = (tbl.[year]-1) and a.employeeid = tbl.employeeid)/Sum([tbl].[amount])) AS percentage
from tbl
group BY tbl.[employeeid],tbl.[year];,

 

[corobori]

Hi,

I tried the one given by gol4 as I liked the fact of doing in one query. Am I right to say that there is a problem as it seems to divide by the value of the current year instead of the previous year ?