Generate such a report

[Faraz Azhar]

I have a small table in which I'm recording my receivables and
payables in a table called "Settlements". It has only 3 columns:
Receivable By, Receivable From, and Amount. We are 4 persons whose
data is maintained here.

Currently it has the following data:

Recv From Recv By Amount
FA AM 100
FA MB 132
MB FA 34
AM MB 43
FA AM 76



I would like to generate a report in which only 'net' totals of these
are appearing, ie. the amount receivable and payable from the same
person is set-off. For example in the following format:


FA Details
Receivable from AM 176
Receivable from MB 98

AM Details
Receivable from FA (176)
Receivable from MB 43

MB Details
Receivable from FA (98)
Receivable from AM (43)

The above balances are set-off against each other, e.g. if MB owes FA
132 and FA owes MB 34 then net balance is 98 which is payable by MB to
FA, and receivable by FA from MB.

Also another logical problem is that the number of persons in this
database is not restricted to 4. It may increase later on. I currently
have a separate table named "Employees". The "Settlements" table is
linked up with "Employees" table. It picks up the names from there. I
have created a lookup in "Settlements" table for 'Receivable By' and
'Receivable From' fields. So I may add more names in "Employees" table
later on therefore the report format will have to be flexible for more
names to be added later on.

I'm unable to generate such kind of report. I need help from you guys.

Thank you
Faraz Azhar

 

[Marshall Barton]

I have a small table in which I'm recording my receivables and
payables in a table called "Settlements". It has only 3 columns:
Receivable By, Receivable From, and Amount. We are 4 persons whose
data is maintained here.

Currently it has the following data:

Recv From Recv By Amount
FA AM 100
FA MB 132
MB FA 34
AM MB 43
FA AM 76

I would like to generate a report in which only 'net' totals of these
are appearing, ie. the amount receivable and payable from the same
person is set-off. For example in the following format:


FA Details
Receivable from AM 176
Receivable from MB 98

AM Details
Receivable from FA (176)
Receivable from MB 43

MB Details
Receivable from FA (98)
Receivable from AM (43)

The above balances are set-off against each other, e.g. if MB owes FA
132 and FA owes MB 34 then net balance is 98 which is payable by MB to
FA, and receivable by FA from MB.

Also another logical problem is that the number of persons in this
database is not restricted to 4. It may increase later on. I currently
have a separate table named "Employees". The "Settlements" table is
linked up with "Employees" table. It picks up the names from there. I
have created a lookup in "Settlements" table for 'Receivable By' and
'Receivable From' fields. So I may add more names in "Employees" table
later on therefore the report format will have to be flexible for more
names to be added later on.

As with any report, you first need to use a query to
retrieve the needed data. In this case, I think you need to
make all the tranactions go in the same direction. I.e.
every record neds to appear twice, once as in your table and
the other with the from an to reversed with the negative
amount:

qryALL:
SELECT [Recv From] As F, [Recv By] As B, amount
FROM table
UNION ALL
SELECT [Recv By], [Recv From], -amount
FROM table

Then use another query to sum up the amounts
SELECT F, B, Sum(Amount) As Total
FROM qryALL
GROUP BY F, B

Use the second query as the report's record source. Use
Sorting an Grouping (View menu) to group on the B field.

Put a text box for the B field in the group header and the F
and Total text boxes in the detail section.

 

[Faraz Azhar]

Hello

Thank you very much Marshall, it worked beautifully. Now I have to
resolve just one more thing. The balances appearing in report are as
follows:

FA Details
Receivable from AM 176
Receivable from MB 98


AM Details
Receivable from FA (176)
Receivable from MB 43


MB Details
Receivable from FA (98)
Receivable from AM (43)


Now I want to add a sum to the amounts appearing in each person's
recievables and payables. Like this:


MB Details
Receivable from FA (98)
Receivable from AM (43)
-------
Net Recievable/Payable (141)
=====

How do I add a sum to a query you've made for me ?

Thanks again!
Faraz Azhar

I have a small table in which I'm recording my receivables and
payables in a table called "Settlements". It has only 3 columns:
Receivable By, Receivable From, and Amount. We are 4 persons whose
data is maintained here.

Currently it has the following data:

Recv From           Recv By         Amount
FA          AM              100
FA          MB              132
MB          FA              34
AM          MB              43
FA          AM              76

I would like to generate a report in which only 'net' totals of these
are appearing, ie. the amount receivable and payable from the same
person is set-off. For example in the following format:

FA Details
   Receivable from AM      176
   Receivable from MB      98

AM Details
   Receivable from FA      (176)
   Receivable from MB      43

MB Details
   Receivable from FA      (98)
   Receivable from AM      (43)

The above balances are set-off against each other, e.g. if MB owes FA
132 and FA owes MB 34 then net balance is 98 which is payable by MB to
FA, and receivable by FA from MB.

Also another logical problem is that the number of persons in this
database is not restricted to 4. It may increase later on. I currently
have a separate table named "Employees". The "Settlements" table is
linked up with "Employees" table. It picks up the names from there. I
have created a lookup in "Settlements" table for 'Receivable By' and
'Receivable From' fields. So I may add more names in "Employees" table
later on therefore the report format will have to be flexible for more
names to be added later on.

As with any report, you first need to use a query to
retrieve the needed data.  In this case, I think you need to
make all the tranactions go in the same direction.  I.e.
every record neds to appear twice, once as in your table and
the other with the from an to reversed with the negative
amount:

qryALL:
SELECT [Recv From] As F, [Recv By] As B, amount
FROM table
UNION ALL
SELECT [Recv By], [Recv From], -amount
FROM table

Then use another query to sum up the amounts
SELECT F, B, Sum(Amount) As Total
FROM qryALL
GROUP BY F, B

Use the second query as the report's record source.  Use
Sorting an Grouping (View menu) to group on the B field.

Put a text box for the B field in the group header and the F
and Total text boxes in the detail section.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

 

[Marshall Barton]

Thank you very much Marshall, it worked beautifully. Now I have to
resolve just one more thing. The balances appearing in report are as
follows:

FA Details
Receivable from AM 176
Receivable from MB 98


AM Details
Receivable from FA (176)
Receivable from MB 43


MB Details
Receivable from FA (98)
Receivable from AM (43)


Now I want to add a sum to the amounts appearing in each person's
recievables and payables. Like this:


MB Details
Receivable from FA (98)
Receivable from AM (43)
-------
Net Recievable/Payable (141)
=====

How do I add a sum to a query you've made for me ?

Add a text box to the group footer section and set its
expression to:
=Sum(Amount)