If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help Please
I have a table with Name, PostDate and amount.
John 1/1/2009 10.00 john 1/2/2009 20.00 john 1/3/2009 50.00 I need to subtract the amount from the prior date and so on. almost like a running total but its the variance i want. thanks. I got this suggestion but i get "#error" in amtprev and Variance SELECT tblPosted.UserName, tblPosted.PostDate, tblPosted.Amount, nz(DLookUp("Amount","tblPosted","PostDate= #" & [PostDate]-1 & "# AND UserName='" & [UserName] & "'"),0) AS AmtPrev, [Amount]-nz(DLookUp("Amount","tblPosted","PostDate= #" & [PostDate]-1 & "# AND UserName='" & [UserName] & "'"),0) AS Variance FROM tblPosted; |
#2
|
|||
|
|||
Help Please
On 10 dec, 17:11, O.... wrote:
I have a table with Name, PostDate and amount. John 1/1/2009 10.00 john 1/2/2009 20.00 john 1/3/2009 50.00 I need to subtract the amount from the prior date and so on. almost like a running total but its the variance i want. thanks. I got this suggestion but i get "#error" in amtprev and Variance SELECT tblPosted.UserName, tblPosted.PostDate, tblPosted.Amount, nz(DLookUp("Amount","tblPosted","PostDate= #" & [PostDate]-1 & "# AND UserName='" & [UserName] & "'"),0) AS AmtPrev, [Amount]-nz(DLookUp("Amount","tblPosted","PostDate= #" & [PostDate]-1 & "# AND UserName='" & [UserName] & "'"),0) AS Variance FROM tblPosted; Like this? SELECT tblPosted.UserName, tblPosted.PostDate, tblPosted.Amount, DVar("Amount","tblPosted","UserName='" & [UserName] & "' AND PostDate=#" & [PostDate] & "#") AS Variance FROM tblPosted; Groeten, Peter http://access.xps350.com |
#4
|
|||
|
|||
Help Please
I don't think it’s the variance you want in the statistical sense of the
variation of a set of values from the mean, but merely the difference between the current amount and the last previous amount per user name, right? Try this: SELECT P1.UserName, P1.PostDate, P1.Amount, P1.Amount - (SELECT P2.Amount FROM tblPosted AS P2 WHERE P2.UserName = P1.UserName AND P2.DatePosted = (SELECT MAX(P3.PostDate) FROM tblPosted AS P3 WHERE P3.UserName = P1.UserName AND P3.PostDate P1.PostDate)) AS Variance FROM tblPosted AS P1 ORDER BY P1.UserName, P1.PostDate; This does of course assume distinct PostDate values per user, i.e. no user name has two or more rows with the same date, though two or more users can legitimately have the same date. Ken Sheridan Stafford, England O.... wrote: I have a table with Name, PostDate and amount. John 1/1/2009 10.00 john 1/2/2009 20.00 john 1/3/2009 50.00 I need to subtract the amount from the prior date and so on. almost like a running total but its the variance i want. thanks. I got this suggestion but i get "#error" in amtprev and Variance SELECT tblPosted.UserName, tblPosted.PostDate, tblPosted.Amount, nz(DLookUp("Amount","tblPosted","PostDate= #" & [PostDate]-1 & "# AND UserName='" & [UserName] & "'"),0) AS AmtPrev, [Amount]-nz(DLookUp("Amount","tblPosted","PostDate= #" & [PostDate]-1 & "# AND UserName='" & [UserName] & "'"),0) AS Variance FROM tblPosted; -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Help Please
Oops! DatePosted should have been PostDate of course.
Ken Sheridan Stafford, England -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200912/1 |
#6
|
|||
|
|||
Help Please
I think you've just given your personal Erinyes here yet more ammunition
against your money-making attempts, Steve. That's a terrible solution. Its not only extremely clumsy but introduces a redundant column into the table and consequently makes it wide open to update anomalies. Ken Sheridan Stafford, England Steve wrote: Add a field named Variance to your table. Create a query based on your table and sort ascending on PostDate. Now you need some code. Create a recordset of the query. Dim a Variable named AmtPrev. Go to the first record in the recordset. Set AmtPrev equal to Amount. Go to next record in recordset. Subtract AmtPrev from Amount and save to Variance. Set AmtPrev equal to Amount. Go to next record in recordset. Subtract AmtPrev from Amount and save to Variance. Repet this in a loop for all the records in the recordset. Steve I have a table with Name, PostDate and amount. John 1/1/2009 10.00 [quoted text clipped - 12 lines] AND UserName='" & [UserName] & "'"),0) AS Variance FROM tblPosted; -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200912/1 |
#7
|
|||
|
|||
Help Please
Ken,
Not so at all! The OP has a table of data and he wants a variance for the records (albeit the first record) in his table. I suggested a way to achieve this as a one-time procedure. The OP then needs to build in a procedure that will compute the variance any time he adds a new record or edits an existing record. It is not clumsy and it does not create a way to introduce anomalies in his data. Steve "KenSheridan via AccessMonster.com" u51882@uwe wrote in message news:a06441f9f1506@uwe... I think you've just given your personal Erinyes here yet more ammunition against your money-making attempts, Steve. That's a terrible solution. Its not only extremely clumsy but introduces a redundant column into the table and consequently makes it wide open to update anomalies. Ken Sheridan Stafford, England Steve wrote: Add a field named Variance to your table. Create a query based on your table and sort ascending on PostDate. Now you need some code. Create a recordset of the query. Dim a Variable named AmtPrev. Go to the first record in the recordset. Set AmtPrev equal to Amount. Go to next record in recordset. Subtract AmtPrev from Amount and save to Variance. Set AmtPrev equal to Amount. Go to next record in recordset. Subtract AmtPrev from Amount and save to Variance. Repet this in a loop for all the records in the recordset. Steve I have a table with Name, PostDate and amount. John 1/1/2009 10.00 [quoted text clipped - 12 lines] AND UserName='" & [UserName] & "'"),0) AS Variance FROM tblPosted; -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200912/1 |
#8
|
|||
|
|||
Help Please
Of course it creates scope for update anomalies. All that has to happen is
for the amount or date posted value to be updated in a row and the variance value in one or more dependent rows is wrong. One could hardly wish for a better example of an update anomaly. And you cannot say that this would be fixed by executing a procedure at every update; if you follow that line of thinking to its logical conclusion then any update anomaly could be similarly fixed and we might as well throw the normalization rule book out of the window. But I'm not going to debate this with you. Your predatory behaviour in this newsgroup is such that you long ago forfeited any right to be treated with any degree of courtesy. I posted merely in order to draw attention to the dubious level of your competence. Ken Sheridan Stafford, England Steve wrote: Ken, Not so at all! The OP has a table of data and he wants a variance for the records (albeit the first record) in his table. I suggested a way to achieve this as a one-time procedure. The OP then needs to build in a procedure that will compute the variance any time he adds a new record or edits an existing record. It is not clumsy and it does not create a way to introduce anomalies in his data. Steve I think you've just given your personal Erinyes here yet more ammunition against your money-making attempts, Steve. That's a terrible solution. [quoted text clipped - 23 lines] AND UserName='" & [UserName] & "'"),0) AS Variance FROM tblPosted; -- Message posted via http://www.accessmonster.com |
#9
|
|||
|
|||
Help Please
Another solution occurred to me, which may well be faster, if you are happy
to use the TOP option: SELECT P1.UserName, P1.PostDate, P1.Amount, P1.Amount - (SELECT TOP 1 Amount FROM tblPosted AS P2 WHERE P2.UserName = P1.UserName AND P2.PostDate P1.PostDate ORDER BY PostDate DESC) AS Variance FROM tblPosted AS P1 ORDER BY P1.UserName, P1.PostDate; Ken Sheridan Stafford, England -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200912/1 |
#10
|
|||
|
|||
Help Please
On Dec 10, 2:20 pm, "Steve" wrote:
Ken, Not so at all! The OP has a table of data and he wants How do you know the OP is a he, huh? BTW, I agree with Ken's comments about your solution. Even using subqueries is a little awkward here, but you've managed to take awkward thinking out of the box :-). James A. Fortune The ancient Egyptian symbol for god, assumed by some to be derived from a flag used to mark sacred places, has been given the Anglicization "netjer" by E. A. Wallis Budge, "netter" or "netcher" by others. The amount of onomatopoeia present in the ancient Egyptian language suggests that word formation follows simple naming ideas. Perhaps "netjer" was originally formed from a simple combination of "neb" (lord of) and the Ancient Egyptian word for Earth (similar to later Latin terre). Note that in the ancient Egyptian language, the "r" sound might have sounded something like a single r trill, possibly more like an l or a w. Thus, the word for god might have originally sounded like "nebtawi," "nebtali" or "nebteri." If it started out as "nebter," then that begs the question of why wasn't the existing "pt" combination hieroglyph available used to represent it, although it's likely that the 'p' and 'b' consonant sounds were not as close to each other then as they are today. Thus, "nebtar" and "neptar" are also possibilities. The "ne-TAH-ru" guess in the movie "The Stargate" can be thought of as applying the Egyptian plural "u" to a relaxed version of "nebtar." It's interesting that Earth's inhabitants in the Stargate SG-1 show are called the "Tauri," as their guess of what an Egyptian god would call the people, except that the plural would actually make it "Tauru." Modern ears are more used to an -i plural for a race than a -u plural. Using a Taur- root, and following the theory above would make the word "Nebtauri," (plural "Nebtauru" - quite close to fitting in with their Nettaru guess). Depending on the closeness of the 'b' and 'p' consonants, my current best guesses are "nebtari" as the earliest use, and "nettari" as the latter use (plurals "nebtaru" and "nettaru") instead of the clumsy "netjeri" or "netjeru." Keeping the hieroglyph letter for the combination "bt" in spite of it softening into "tt" would account for having different symbols for the same sound and resolve much speculation based on the reason different symbols were used. |
|
Thread Tools | |
Display Modes | |
|
|