Help Please

[O....]

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;

 

[xps35]

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

 

[Steve]

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
(e-mail address removed)

 

[Steve]

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.
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

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
(e-mail address removed)

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;

 

[Diallo Sariou]

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;

 

[Diallo Sariou]

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;

 

[James A. Fortune]

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
(e-mail address removed)

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.

 

[Dirk Goldgar]

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.

Very interesting. But huh?

 

[James A. Fortune]

But huh?

Pretty close! The "he, huh" is what made me think of the word
onomatopoeia. The overall effect is assuaged, however, when fully
explained.

James A. Fortune
(e-mail address removed)