DSum

[Christina]

I have a table with the following fields. ID autonumber, Check Number, Name,
Date of transaction (med date), check amount, Deposit amount,. I would like
to run a query with all the data in the table with a running balance. I need
detail instructions though, as I am new to Access. I know how to set up the
query with the fields, but will need detal instructions for the DSum.

Thanks

 

[Allen Browne]

DSum() won't really cut it here, because it can't identify the 'previous'
records to sum to get the running balance. They may be the lower ID values,
but they may not be (e.g. where you find an entry from yesterday that wasn't
entered, so the ID numbers are not in date order.) And selecting by the [med
date] won't work unless it's unique.

The simplest approach is to use a report. In the report's Sorting and
Grouping box, you can tell it to sort on the [med date] field, and then on
the next row of the dialog by the ID field. This sorts them in the correct
order. Then add another text box to the Detail section to give the running
sum. The text box will have properties like this:
Control Source =[check amount]
Runing Sum over group
Format currency

If you must do it in a query, a subquery will let you do it. Details:
http://allenbrowne.com/subquery-01.html#Aggregation

 

[Christina]

I have both checks that are written and deposits that are made into the
account in the table. One field for each. I would prefer to credits and
debits seperate if possible.
I created a report and put the text box as you suggested in the detail
section, but it gives me a syntax error.

DSum() won't really cut it here, because it can't identify the 'previous'
records to sum to get the running balance. They may be the lower ID values,
but they may not be (e.g. where you find an entry from yesterday that wasn't
entered, so the ID numbers are not in date order.) And selecting by the [med
date] won't work unless it's unique.

The simplest approach is to use a report. In the report's Sorting and
Grouping box, you can tell it to sort on the [med date] field, and then on
the next row of the dialog by the ID field. This sorts them in the correct
order. Then add another text box to the Detail section to give the running
sum. The text box will have properties like this:
Control Source =[check amount]
Runing Sum over group
Format currency

If you must do it in a query, a subquery will let you do it. Details:
http://allenbrowne.com/subquery-01.html#Aggregation

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table with the following fields. ID autonumber, Check Number,
Name,
Date of transaction (med date), check amount, Deposit amount,. I would
like
to run a query with all the data in the table with a running balance. I
need
detail instructions though, as I am new to Access. I know how to set up
the
query with the fields, but will need detal instructions for the DSum.

Thanks

 

[Allen Browne]

That's the solution - worth persuing.

Perhaps you can drag the field from the Field List, and place it in the same
section (Detail?). Then set Running Sum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have both checks that are written and deposits that are made into the
account in the table. One field for each. I would prefer to credits and
debits seperate if possible.
I created a report and put the text box as you suggested in the detail
section, but it gives me a syntax error.

DSum() won't really cut it here, because it can't identify the 'previous'
records to sum to get the running balance. They may be the lower ID
values,
but they may not be (e.g. where you find an entry from yesterday that
wasn't
entered, so the ID numbers are not in date order.) And selecting by the
[med
date] won't work unless it's unique.

The simplest approach is to use a report. In the report's Sorting and
Grouping box, you can tell it to sort on the [med date] field, and then
on
the next row of the dialog by the ID field. This sorts them in the
correct
order. Then add another text box to the Detail section to give the
running
sum. The text box will have properties like this:
Control Source =[check amount]
Runing Sum over group
Format currency

If you must do it in a query, a subquery will let you do it. Details:
http://allenbrowne.com/subquery-01.html#Aggregation

I have a table with the following fields. ID autonumber, Check Number,
Name,
Date of transaction (med date), check amount, Deposit amount,. I
would
like
to run a query with all the data in the table with a running balance.
I
need
detail instructions though, as I am new to Access. I know how to set up
the
query with the fields, but will need detal instructions for the DSum.