Comparing rows 1

[alh1002]

I have a DB with a numerical value for a given date.

I want to calculate the daily difference, i.e. if the value is 125 on March 20, and 150 on March 21. I want to calculate the difference (which is 25 in this example). And then I will create new table which has the 25 value as a new column on the row associated with March 21.

Also I will have to handle days only being the days entered in DB. As no data is inputted on weekends.

I need to loop thru the entire DB.

I am stuck on how to do this. Any help would be greatly appreciated.

 

[Remou]

Perhaps SQL might work?
[tt]SELECT tblTableS.EnteredDate, tblTableS.Total, (Select Top 1 Total From tblTableS A Where A.EnteredDate<tblTableS.EnteredDate Order By EnteredDate Desc) AS PreviousDay, [Total]-Val(Nz([PreviousDay],0)) AS Difference
FROM tblTableS
ORDER BY tblTableS.EnteredDate;[/tt]

 

[alh1002]

this select statement does work, thank you

but a couple of difficulties:
I reset the number each month, so the first entry of the month, the difference should tblTableS.Total-tblBeginMonth.Value

How do I get that value?

And what is Select Top 1

Thank you again, this does help

 

[lameid]

Select Top 1 says to select the top 1 row... You could just as easily specify 10 instead of 1 for 10 rows (that would break this query).

As for changing it the first of the month what does tblBeginMonth have in it? How do you match it up to tblTableS?

 

[Remou]

Top 1 is the first record in the sort order, in the case above, the highest date less that the date in tblTableS.

The first subtraction uses Nz to get rid of the Null for the first PreviousDay:
Nz([PreviousDay],0)
However, it does not have to be 0. I think the first of the month is the only place you will get a null, so:
Nz([PreviousDay],<tblBeginMonth.Value>)
Might suit. I am not sure where tblBeginMonth.Value (a form?) is coming from, so I cannot give a proper formula.

 

[alh1002]

tblBeginMonth has a date and a value in it.

I was matching via a where clause
DatePart("m",[tblBeginMonth])=DatePart("m",[tblTableS])
and
DatePart("yyyy",[tblBeginMonth])=DatePart("yyyy",[tblTableS])

and therefore I could see the difference in a given month

 

[Remou]

If you would like to post your SQL, perhaps? In the meantime, try substituting the name of the begin month field for the zero in the Nz statement:
[tt]Nz([PreviousDay],tblBeginMonth.Value)[/tt]
And see what happens.

 

[lameid]

Can you post your current SQL? It would be easier to fix that way.

 

[alh1002]

THis query works but gives no value for the first date of Previous Day is always blank, so I have to have another query to get the true Base. I would like something cleaner

Key: tblNAV has values
tblBOM has beginning of Month values

SELECT tblNAV.ladder_date, tblBOM.BOM AS BOM, tblNAV.NAV AS NAV, NAV-BOM AS RT, 100*RT/NAV AS MTDPER, (Select Top 1 Val(Nz([A.NAV],[tblBOM.BOM])) From tblNAV A Where A.ladder_date<tblNAV.ladder_date and (((DatePart("m",[A.LADDER_DATE]))=DatePart("m",[tblBOM.BOM_DATE])) AND ((DatePart("yyyy",[A.LADDER_DATE]))=DatePart("yyyy",[tblBOM.BOM_DATE]))) Order By ladder_date Desc) AS PreviousDay, Val(Nz([PreviousDay],[tblBOM.BOM])) AS Base, [NAV]-Val(Nz([PreviousDay],[tblBOM.BOM])) AS Difference, 100*(Difference/(Val(Nz([PreviousDay],[tblBOM.BOM])))) AS CHANGEPER
FROM (tblBook INNER JOIN tblNAV ON tblBook.book_id=tblNAV.book_id) INNER JOIN tblBOM ON tblBook.book_id=tblBOM.BOM_book
WHERE (((DatePart("m",[tblNAV.LADDER_DATE]))=DatePart("m",[tblBOM.BOM_DATE])) AND ((DatePart("yyyy",[tblNAV.LADDER_DATE]))=DatePart("yyyy",[tblBOM.BOM_DATE])));