Grabbing Data from Previous Row

[LarryE]

I tried that but it didnt work - i will update the basic db, appreciate any help really do appreciate it all.

You will have nothing but problems and error messages if you use these field names:

Change the Date, Day and Month field names. They are reserved words in ACCESS and cannot be used as field names effectively. Do this before you go any further. You should also change the ID AutoNumber to a Primary Key and the name should be something like MoneyID, not just ID. Change the Date field to MoneyDate, the Day field to MoneyDay and the Month field to MoneyMonth.

In addition to the above, none of your tables have Primary Keys or Foreign Keys so they cannot be used as ACCESS tables and relationships cannot be established using Primary and Foreign Keys.

I don't know what you are attempting to do here, but ACCESS is not likely to do it until you create proper tables and field names as well as establish proper relationships between tables.

If you will describe the business process you are involved in and what you want to track, maybe someone in this forum can help you build the proper tables, field names and relationships so ACCESS can work properly.

 

[CJ_London]

When you have the situation where the order is based on more than one field and you need a running sum - in this case date and ID, you need to combine them into one field which provides a unique ID for the row. Providing your ID's are not going to exceed 86400 and your date does not contain a time element then the easiest way is to create query to calculate this rowID - such as

Edit: formula needs to return a double
SELECT TBL_Money.*, cdbl([date]+[id]/86400) AS RowID FROM TBL_Money;

the time element of dates is stored as a fraction of 86400 - the number of seconds in a day

now you can base your dsum on the query, rather than the table

Amount RowID rSum
£1.00 45294.0000231481 1
£1.00 45295.0000694444 2
£10.00 45444.0000347222 12
£1.00 45446.0000115741 13
£1.00 45446.0000810185 14
£1.00 45446.0000925926 15
-£20.00 45447.0000462963 -5
£2.00 45637.0000578704 -3

And I agree with others - use meaningful names for your fields - such as TranDate and avoid reserved words such as Date, Day and Month - other popular reserved words to avoid include, name, desc, description

 

[Mark_]

@OP,

Why do you have "Date", "Day", and "Month" as separate fields? You can return the day and month from a date with a function or format, so what advantage is there storing it separately?

Also, as you are already using a "Date", why not just add the time and be done with it? You can always add the time in automatically if you need when the record is saved, unless you are entering it after the fact. This also keeps your records in order even if they are not entered in date/time order.

 

[Pat Hartman]

Ah, its not working if the same date is used (which will be the case sometimes)

I was about to warn you that this would happen. To make this running sum work, you MUST have a UNIQUE ID for each record and the sort sequence of that ID dictates the running sum.

 

[DHookom]

Add the ID as seconds to the Date field. Then use this to sort by and total.

 

[CJ_London]

Add the ID as seconds to the Date field. Then use this to sort by and total.

per post #22 - that doesn't work with dsum - you need to define it as a double not a date

 

[arnelgp]

I tried that but it didnt work - i will update the basic db, appreciate any help really do appreciate it all.

try this db

 

[Gasman]

Almost there @arnelgp

 

[CJ_London]

My example works - post#22

 

[SpyhunterUK]

Try sorting in your query first by date and second by ID. So if dates are the same, they will sort then by ID.

How can I do this please, whats the SQL to add for a 2nd sort order please?

 

[SpyhunterUK]

Any help would be appreciated. I cannot work out how to sort by 2nd order (Date then ID) in SQL, Ive added Row Counter but this doesnt change when I refresh (after adding rows that should be in the middle, etc) so I cannot use this. Help!!

 

[SpyhunterUK]

ignore my last comments, just saw page 2 lol - the db you sent back works - i will look at picking it apart from all your comments and changing as advised so I can learn from this as well - many thanks all for your help, it is really appreciated.

 

[CJ_London]

I didn't send back a db but the sql to generate your list is simply
for qry_Money

SELECT TBL_Money.*, cdbl([date]+[id]/86400) AS RowID FROM TBL_Money;

and to produce your running sum

SELECT qry_Money.*, DSum("Amount","Qry_Money","RowID<=" & [rowID]) AS rSum
FROM qry_Money
ORDER BY qry_Money.RowID;

 

[DHookom]

per post #22 - that doesn't work with dsum - you need to define it as a double not a date

This is what I meant by adding the ID as seconds to Date field.

First create a query [qryMainUnique] with the ID added as seconds to the date:

SELECT TBL_Money.*, DateAdd("s",[ID],[Date]) AS DateUnique

Lastly create a query for the subform that orders by DateUnique with he running balance:

SELECT qry_MainUnique.*, 
Val(DSum("Amount","qry_MainUnique","DateUnique <=#" & [DateUnique] & "#")) AS Balance
FROM qry_MainUnique
ORDER BY qry_MainUnique.DateUnique;

 

[arnelgp]

Almost there @arnelgp

that is because the subform is only Sorted on [Date] field. but if you add the ID it will work because the Query and the function is using Sort order on [Date] and ID field.

 

[June7]

Must you show this running sum in query or form? Access won't handle that well as this discussion so far has shown. Requires either correlated subquery or DSum() domain aggregate, both of which can cause slow performance with large dataset. This is a very common topic.

However, a report can do this quite easily because in a report, textbox has RunningSum property. The calc will work no matter the order of records.

Ideally, forms are for input of raw data and reports output manipulated data.