Query

[fabian24]

I am pretty new at access and have just been trying a few things.



I have made the tables that I require for keeping track of the Electricity Bills for various locations.

I am now stuck at a point where I have been using calculated values.

As I don't store the values in any table, the formulae does not know where to get the data from.

I have current bill, outstanding balance as calculated fields.

Say for record 1 - Current Bill - 1000. Amount Paid - 500. The outstanding will be 500.

When I go to the second record - Current Bill - 2000. Amount Paid - 400. The outstanding will be - 1600+500 = 2100

In my query, I cannot get this. It only shows the present record balance and ignores the previous record.

Somebody please enlighten me.

I am attaching the file.

Thank you.

[LillMcGill]

Query the query you have, adding only the field that uniquely identifies each location and the Outstanding to the grid. Click the Totals button. Change the Total row for Outstanding to Sum... so you are grouping by Location and Summing Outstanding.

[fabian24]

I am new at using access... Could u please explain it a little more....

[LillMcGill]

Create tab of the Ribbon. Click Query Design. Use the radio button to choose to see queries instead of tables. Add the query where you calculate CurrentBill minus AmountPaid to get OutstandingBal.

Add just two fields to the grid... whatever field is unique for each Location, and OutstandingBal.

On the Design tab of the Ribbon is a button that looks like Autosum in Excel. Click it and notice it changed the grid... you now have a Totals row in the grid, with the default instruction "Group by" in each column.

In the OutstandingBal column, click inside "Group By" and use the drop-down to change it to "Sum".

view the datasheet...You are now grouping by Location and summing OutstandingBal.