How do I create the table below (1 Viewer)

[Gichukipaul13]

 

[onur_can]

id - Automatic Number
name - Short Text
dateofpayments - Date/Time
credit - Integer
debit - Integer
totals - Integer

You can create it.

 

[The_Doc_Man]

The table you describe is more like a spreadsheet. What is your data source? What are you going to do with this if you get it?

You can create it by manually entering data into the the first four columns and use a cross-row formula in Excel to do the incremental addition and subtraction in the fifth column.

However, if you want this in Access, you would not use this structure because it is not normalized properly and it becomes difficult to do the kind of references you need. No experienced Access designer would create that structure. Access is not Excel.

To do this right in Access, you would have at least two tables and a couple of queries. I will make up names for illustration purposes.

tblPerson would have a PersID field, probably an autonumber but certainly if not that then a LONG INTEGER. Then it would have all the data about each person. The PersID field would then become the primary key of this table.

tblTransact would have a foreign key linking back to the PersID of the person table. It would have a date, an amount, and an indicator for whether it would count as a credit or debit. These transactions would be mixed with both credit and debit in the same table and in the same field, but with the indicator that identifies what each transaction represented.

Then you would have query to form the transaction sums grouped by the PersID and taken in date order.

If you want this in an ordered report, you would do grouping by person and sorting by date in the report and use the "running sum" option to show the daily balance for each person.

That was an overview using the correct terms for you to use if searching the forum for how this sort of thing would be done.

 

[Gichukipaul13]

Thank you so much that was exactly what I was just looking for. Now how do I create the query please.
in line with the description you gave.

 

[Gichukipaul13]

id - Automatic Number
name - Short Text
dateofpayments - Date/Time
credit - Integer
debit - Integer
totals - Integer

You can create it.

Yes I have created it. The problem is how to generate now what I have written on the totals.

 

[arnelgp]

You remove the total. You can calculate the total in a Query:

Select *, dsum("nz(credit,0)-nz(debit,0)", "yourtable","id<=" & [id]) as total from
Yourtable;

 

[Pat Hartman]

Create the output using a report. That's what they are for. Creating the output with a query is very inefficient because it requires the use of domain functions. Reports process a recordsource 1 row at a time so that makes them ideal for calculating balances.