Delta Column in access 2007

[Tabasco]

Hello bytes,
Im playing around abit with graphs in access 2007 and i would need to visualise the diffrence rather then the total;

for example if the total if 100 year one, 120 year two and 150 year three i would like the graph to be ,(1,0) (2,20) (3,30). Scince the graphs are based on quaries i though it would be simple to add a delta column but I find it easier said than done and im stuck.

So what am I trying to do, Im trying to create a column which get the value from its row and subract it with the value from the row before (except the first year which need to be 0)

heres the sql statement i got so far

SELECT PrivatePropertyMarketMDB.Statistic AS SQLSMÅHUS, PrivatePropertyMarketMDB.PubDate, MunicipalityMDB.Municipality, MunicipalityMDB.MunicipalityCode
FROM MunicipalityMDB INNER JOIN (DevelopmentTypeMDB INNER JOIN PrivatePropertyMarketMDB ON DevelopmentTypeMDB.DevType_id = PrivatePropertyMarketMDB.DevelopmentType) ON MunicipalityMDB.Municipality_ID = PrivatePropertyMarketMDB.Municipality
WHERE (((DevelopmentTypeMDB.DevelopmentType)="småhus"));
 

If anyone have any tip of what to do or know any article which could help i would be grateful!

[Rabbit]

I can't comment too much on your SQL because you haven't told us what your table design looks like but I can offer general advice.

You will need to join the table to itself so that you can get the previous year's data so that you can calculate the delta. It will have to be an outer join because the first year will not have a previous year to join to. You will need to handle the resulting Null using the Nz function.

[NeoPa]

Tabasco:
Scince the graphs are based on quaries i though it would be simple to add a delta column but I find it easier said than done and im stuck.

That would be wrong thinking indeed. Database work is all based on Set Theory. There is no concept of juxtaposition (or relative order) within Set Theory. That's a way of thinking you would benefit greatly from leaving behind.

[Rabbit]

@NeoPa, well, I don't know about leaving it behind. Perhaps leave behind the idea of it being easy to compare between records in SQL. But not leaving behind the idea of ever comparing between records in SQL.

If the requirement the customer wants is to graph the deltas, I don't know how they would feel about hearing that they shouldn't because it's not in line with set theory.

[NeoPa]

Indeed. I believe I was quite specific in referring to the way of thinking though.

As you so correctly imply, many things can be achieved in Access, even if they are conceptually foreign to the whole concept of RDBMSs. I do believe though, that the whole way of thinking (that can probably be best explained by thinking of Access as Excel) is quite detrimental to doing work within an RDBMS (of any variety). This thinking (to my mind at least) was illustrated quite clearly in the short excerpt I quoted in my post.

Letting go of that would certainly be of great benefit to the OP (Like leaving behind a ruck-sack full of bricks on a hike). Until they do let go, it will only weigh them down.

[Tabasco]

Thank you both for the useful and insightful tip,
I got it working evantually and I'll write the code if someone else hase the same problem of course you cant copy it but it might serve as a step in the wright direction

SELECT t2.date AS Expr1, t2.TABLE-t1.TABLE AS Delta,
FROM [TABLE] AS t2 INNER JOIN [TABLE] AS t1 ON t2
WHERE (((t2.date)=DateAdd("yyyy",1,[t1].[pubdate])));
 

The access graph tool visualize the SQL statement so I have to get a SQL to show delta because im trying to avoid Excel completely. It much more simple to use excel to get the deltacolumn however it wast so hard in access either once I knew how to do it.


Thanks again for the help!

[Rabbit]

I noticed you used an inner join instead of the outer join I suggested. Do you not need the first year? If you do, you'll have to use an outer join.

[Tabasco]

Yes i need the first entry and, i tried to use outer join directly in the first sqlstatement but it didnt work, in access you seem to have to use "FULL OUTER JOIN", "LEFT OUTER JOIN" and "RIGHT OUTER JOIN" I tried all three but it seems not to give the result i need.

However i use it later because i also have a sqlstatement which calculate the total for evervy entry (So i get every row) so I join this statement whith the other statement with an outerjoin, I just posted the sqlstatement for which i got the deltacolumn.

But I works kind of slow maybe it will be faster if I'm able to use the outer join directly?

PS I used IIF(ISNULL(DELTA); DELTA; 0) mainly because of old habits instead of NZ() function maybe thats slowing it down DS

[Rabbit]

You have to use a left outer join. And your where condition needs to be in the join. You can use the IIF or the NZ, I doubt if one will be significantly faster than the other. NZ is probably only slightly faster.

[NeoPa]

Rabbit:
your where condition needs to be in the join

Further to Rabbit's comment - the ON section of the JOIN needs to be specified to select the required records only. Your code :

FROM [TABLE] AS t2 INNER JOIN [TABLE] AS t1 ON t2

... is incorrectly formatted. I don't even know how it worked, but I expect it simply treats it as TRUE. This would produce a Cartesian Product (At this point see SQL JOINs.) which makes sense when using a WHERE clause, but would be expected to run relatively slowly.

Instead of using this particular WHERE clause, you should specify the logic using ON, such as :

SELECT t1.Date
     , t1.Table-t2.Table AS Delta
FROM   [TABLE] AS t1 LEFT JOIN [TABLE] AS t2
  ON   t1.Date=DateAdd('yyyy',1,t2.PubDate)

Having now looked at your SQL in more detail it is clear that it has never run as you posted it. It had some serious syntactical errors in it that no SQL engine could run.

You should also be aware that, as a function is integrally involved in the selection of records, this SQL will run slower than if that were not the case. Faster than the same code in the WHERE clause hopefully, but nevertheless not top speed for SQL. I don't see a way of avoiding this. Even if each record represents a separate year there would still be some level of function call required to determine which records matched which.