A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help Please



 
 
Thread Tools Display Modes
  #1  
Old December 10th, 2009, 05:11 PM posted to microsoft.public.access
O....
external usenet poster
 
Posts: 14
Default 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  
Old December 10th, 2009, 05:36 PM posted to microsoft.public.access
xps35
external usenet poster
 
Posts: 22
Default 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
  #3  
Old December 10th, 2009, 05:50 PM posted to microsoft.public.access
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Help Please

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



"O...." wrote in message
...
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;



  #4  
Old December 10th, 2009, 07:34 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default 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  
Old December 10th, 2009, 07:37 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default 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  
Old December 10th, 2009, 07:52 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default 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  
Old December 10th, 2009, 08:20 PM posted to microsoft.public.access
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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  
Old December 10th, 2009, 11:05 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default 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  
Old December 10th, 2009, 11:37 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default 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  
Old December 11th, 2009, 04:54 PM posted to microsoft.public.access
James A. Fortune
external usenet poster
 
Posts: 903
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:21 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.