PREVIOUS RECORD VALUE IN FIELD

  • Thread starter Thread starter laveshhp via AccessMonster.com
  • Start date Start date
L

laveshhp via AccessMonster.com

HELLO
PLEASE CAN SOMEBODY HELP ME OUT ON THIS PROBLEM
I AM WRITING AND ACOUNTING DATABASE AND I NEED TO GET THE PREVIOUS RECORD
FIELD (WHICH IS SUM( "A" FIELD + "B" FIELD)
E.G
DATE FIELD A FIELD B TOTAL (FIELD A + FIELD B)
9/1/2006 100.00 100.00 200.00

NOE I NEED LIKE THIS

DATE OPBALA NCE FIELDA FIELDB TOTAL
9/2/06 200.00 50.00 50.00 300.00
9/3/06 300.00 100.00 100.00 500.00
9/4/06 500.00

THANKS
LAVESH
 
SELECT Table1.DateField, Nz(DSum("FieldA","Table1","DateField < #" &
[DateField] & "#"),0)+nz(DSum("FieldB","Table1","DateField < #" &
[DateField] & "#"),0) AS OpenBal, Table1.FieldA, Table1.FieldB,
[OpenBal]+[FieldA]+[FieldB] AS Total
FROM Table1;

You will need to substitute your field names and tables name in the
appropriate places.
 
Jeff said:
SELECT Table1.DateField, Nz(DSum("FieldA","Table1","DateField < #" &
[DateField] & "#"),0)+nz(DSum("FieldB","Table1","DateField < #" &
[DateField] & "#"),0) AS OpenBal, Table1.FieldA, Table1.FieldB,
[OpenBal]+[FieldA]+[FieldB] AS Total
FROM Table1;

You will need to substitute your field names and tables name in the
appropriate places.
HELLO
PLEASE CAN SOMEBODY HELP ME OUT ON THIS PROBLEM
Click to expand...
[quoted text clipped - 13 lines]
THANKS
LAVESH
Click to expand...
Click to expand...


THANKS FOR THE PROMPT REPLY BUT I AM STILL CONFUSED
COS THE "OPENBAL" IS NOT A FIELD IN THE TABLE1 BUT I WANT TO ADD IT IN THE
QUERY SO PLEASE ADVICE ME

LAVESH
 
The query I gave you creates the OpenBal field and then uses that field
in the Total field.

Jeff said:
SELECT Table1.DateField, Nz(DSum("FieldA","Table1","DateField < #" &
[DateField] & "#"),0)+nz(DSum("FieldB","Table1","DateField < #" &
[DateField] & "#"),0) AS OpenBal, Table1.FieldA, Table1.FieldB,
[OpenBal]+[FieldA]+[FieldB] AS Total
FROM Table1;

You will need to substitute your field names and tables name in the
appropriate places.
HELLO
PLEASE CAN SOMEBODY HELP ME OUT ON THIS PROBLEM
Click to expand...
[quoted text clipped - 13 lines]
THANKS
LAVESH
Click to expand...
Click to expand...


THANKS FOR THE PROMPT REPLY BUT I AM STILL CONFUSED
COS THE "OPENBAL" IS NOT A FIELD IN THE TABLE1 BUT I WANT TO ADD IT IN THE
QUERY SO PLEASE ADVICE ME

LAVESH
Click to expand...
 
The query I gave you creates the OpenBal field and then uses that field
in the Total field.

Jeff said:
SELECT Table1.DateField, Nz(DSum("FieldA","Table1","DateField < #" &
[DateField] & "#"),0)+nz(DSum("FieldB","Table1","DateField < #" &
[DateField] & "#"),0) AS OpenBal, Table1.FieldA, Table1.FieldB,
[OpenBal]+[FieldA]+[FieldB] AS Total
FROM Table1;

You will need to substitute your field names and tables name in the
appropriate places.
HELLO
PLEASE CAN SOMEBODY HELP ME OUT ON THIS PROBLEM
Click to expand...
[quoted text clipped - 13 lines]
THANKS
LAVESH
Click to expand...
Click to expand...


THANKS FOR THE PROMPT REPLY BUT I AM STILL CONFUSED
COS THE "OPENBAL" IS NOT A FIELD IN THE TABLE1 BUT I WANT TO ADD IT IN THE
QUERY SO PLEASE ADVICE ME

LAVESH
Click to expand...
 
Jeff said:
The query I gave you creates the OpenBal field and then uses that field
in the Total field.
SELECT Table1.DateField, Nz(DSum("FieldA","Table1","DateField < #" &
[DateField] & "#"),0)+nz(DSum("FieldB","Table1","DateField < #" &
Click to expand...
Click to expand...
[quoted text clipped - 16 lines]
LAVESH
Click to expand...
Click to expand...
hi!
thanks for your reply but still i didn't understand how to do that plase can
you explain in detail cos i am ne w in access
thanks
 
Hi -

A suggestion if I may -

While MS Access is very good in many applications, Accounting is not one
of them, for exactly the reason that is giving you trouble (a ledger).

Why not investigate the many accounting programs that are out there?
Many are not very expensive, and are easy to use. I think you would
find a there is lot less frustration, and it is easier to learn.

Just a thought.

John
Jeff said:
The query I gave you creates the OpenBal field and then uses that field
in the Total field.

SELECT Table1.DateField, Nz(DSum("FieldA","Table1","DateField < #" &
[DateField] & "#"),0)+nz(DSum("FieldB","Table1","DateField < #" &
Click to expand...

[quoted text clipped - 16 lines]
LAVESH
Click to expand...
Click to expand...

hi!
thanks for your reply but still i didn't understand how to do that plase can
you explain in detail cos i am ne w in access
thanks
Click to expand...
 
Back
Top