difference - urgent

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with the following info

date amount Should be date amount
10/24 100 10/24 100
10/25 250 10/25 150
10/26 325 etc 10/26 125

How would I achieve this?

Thanks
 
Are you saying the amount should always be one hundred and something? If
that's the case, use an Update query and update amount to:
[Amount] - (Int([Amount]/100)-1)*100
 
No the info I get is cumulative and I need to substract the previous date
amount to get the actual amount for the day. Thanks for your reply.

PC Datasheet said:
Are you saying the amount should always be one hundred and something? If
that's the case, use an Update query and update amount to:
[Amount] - (Int([Amount]/100)-1)*100

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com




ria said:
I have a table with the following info

date amount Should be date amount
10/24 100 10/24 100
10/25 250 10/25 150
10/26 325 etc 10/26 125

How would I achieve this?

Thanks
Click to expand...
Click to expand...
 
Sorry, I couldn't understand the question either so didn't reply. I'm
not sure that I'm any clearer now.

Brett

No the info I get is cumulative and I need to substract the previous date
amount to get the actual amount for the day. Thanks for your reply.

PC Datasheet said:
Are you saying the amount should always be one hundred and something? If
that's the case, use an Update query and update amount to:
[Amount] - (Int([Amount]/100)-1)*100

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com




ria said:
I have a table with the following info

date amount Should be date amount
10/24 100 10/24 100
10/25 250 10/25 150
10/26 325 etc 10/26 125

How would I achieve this?

Thanks
Click to expand...
Click to expand...
Click to expand...

Cheers,
Brett
 
AFAIK, this would have to be approached by writing code to step through the
data row by row, saving off the new accumulated amount into a variable,
subtracting the current accumulated amount, then moving the new accumulated
amount into the current accumulated amount, moving to the next record, and
repeating the process.
This would definitely require VBA coding.

Another thing you'll want to be careful of is that this code is only applied
once to each import...

HTH
- Turtle
 
ria said:
the info I get is cumulative and I need to substract the previous date
amount to get the actual amount for the day.

date amount Should be date amount
10/24 100 10/24 100
10/25 250 10/25 150
10/26 325 etc 10/26 125
Click to expand...

Then amount = 325 - 250 = 75 where date 10/26.

SELECT
T2.MyDate,
T2.MyAmount - IIF(
(SELECT T1.MyAmount FROM MyTable T1 WHERE T1.MyDate = (SELECT
MAX(MyDate) FROM MyTable WHERE MyDate<T2.MyDate)) IS NULL, 0, (SELECT
T1.MyAmount FROM MyTable T1 WHERE T1.MyDate = (SELECT MAX(MyDate) FROM
MyTable WHERE MyDate<T2.MyDate))
) AS new_amount
FROM MyTable T2 ORDER BY T2.MyDate;

Jamie.

--
 
thank you Jamie .

MacDermott said:
AFAIK, this would have to be approached by writing code to step through the
data row by row, saving off the new accumulated amount into a variable,
subtracting the current accumulated amount, then moving the new accumulated
amount into the current accumulated amount, moving to the next record, and
repeating the process.
This would definitely require VBA coding.

Another thing you'll want to be careful of is that this code is only applied
once to each import...

HTH
- Turtle
Click to expand...
 
Aha! You're trying to do calculations into a table? Shouldn't do
that. It's not safe for your data for a start. A change to a figure
anywhere in your data will not recalculate these results like Excel
will. Calculations are a big No No.

There are solutions of course as every database ever written needs to
do calculations - they just don't happen in the table.

Here's a great link which explains an inventory example

http://members.iinet.net.au/~allenbrowne/AppInventory.html

Brett


Cheers,
Brett
 
Back
Top