Report filtered on a calculated field

  • Thread starter Thread starter Robin Hickman
  • Start date Start date
R

Robin Hickman

I am using Access 97 and am a database novice. I'm a professional
photographer and have put together a database that stores all the data
relative to each client. I'm sure it is very simple to get the report I
want, but I can't figure it out. :)

The main form ("Client") displays the name, address and phone, etc., for
each of my photography clients. this data comes from the main table
(also "Client".) (I learned about naming conventions after getting well
into this project so the names are not frmClient and tblClient like they
should be) The form "Client" also contains a subform that displays all
of the currently displayed client's money transactions. the subform is
called Client_Account and it gets it's data from a table also called
Client_Account that is linked to the Client table. Client_Account has
the following fields:

Client_Account_ID (The primary key (autonumber))
Client_ID (linked to the Client_ID field in the main Client table)
Transaction_Date (date field)
Transaction_Type (text field)
TRansaction_Description (text field)
Client_Debit (A number field, any charges incurred are entered here)
Client_Credit (A number field, payments or other credits entered here)

The Client_Account subform displays all these fields in a continuous
forms type view (except the first two key type fields). At the bottom is
a calculated field that uses the formula

=NZ(Sum([Client_Debit]))-NZ(Sum([Client_Credit]))

to determine how much money the client owes. (Many clients make a
deposit when they place an order, so there is a balance on their account
due on delivery. Others pay in full at the time of order so there is no
balance.) If the charges and payments that are linked to any particular
client are equal, then the sume of Client_Debit and Client_Credit are
equal and this field displays zeros, otherwise it displays the total
amount owed by the client. this subform works perfectly.

Now for the reports I want to create:

I want a report that only shows the clients that owe me money. (Actually
I want two reports like this, one only showing the client name and total
amount owed with a total of the money owed at the bottom, and one that
shows all the clients that owe money and under each client a list of all
of that client's transactions.

Like I said, this is probably simple, but I'm better at creating images
than databases, so I appreciate any help. Remember, I really know very
little about this so don't assume that I know much of anything in your
reply. :)

Thanks again!!!
Robin
 
Robin,

Make a query to determine who owes you. This query will include both
your tables, joined on the Client_ID field from both tables. In the
query design grid, put the Client_ID and Client_Name fields into the
first two columns. In the third column, put this...
Transaction: Sum(Nz([Client_Credit],0)-Nz([Client_Debit],0))
Now, make this into a Totals Query (select Totals from the View menu).
Leave Group By in the Totals row of the first two cilumns in the grid,
and in the third, put Expression in the Totals row, and >0 in the
Criteria row. Save this query, let's say you call it MoneyOwed.

Ok, now you can base your first report directly on this query. In the
Report Footer, put an unbound textbox, with its Control Source set to
=Sum([Transaction])

Now make another query. This query will be based on your Client_Account
table, and also the MoneyOwed query. Join these on the Client_ID field
from both. Put all the fields that you want on the report into the
query design grid. Save this query. You can base your second report on
this query. The neatest way to set out the report would probably be to
use the report's Sorting & Grouping facility (available from the View
menu of the report design), to make a Client Header section in the
report. Just enter the ClientName or Client_ID field in the
Field/Expression column of the Sorting & Grouping dialog, and then enter
Yes for Group Header in the Group Properties panel. Then, in you report
design, you can place the ClientName control in the group header, and
the transaction fields in the Detail section. If you want to show
totals for each client, also define a Client footer section as well.

--
Steve Schapel, Microsoft Access MVP


Robin said:
I am using Access 97 and am a database novice. I'm a professional
photographer and have put together a database that stores all the data
relative to each client. I'm sure it is very simple to get the report I
want, but I can't figure it out. :)

The main form ("Client") displays the name, address and phone, etc., for
each of my photography clients. this data comes from the main table
(also "Client".) (I learned about naming conventions after getting well
into this project so the names are not frmClient and tblClient like they
should be) The form "Client" also contains a subform that displays all
of the currently displayed client's money transactions. the subform is
called Client_Account and it gets it's data from a table also called
Client_Account that is linked to the Client table. Client_Account has
the following fields:

Client_Account_ID (The primary key (autonumber))
Client_ID (linked to the Client_ID field in the main Client table)
Transaction_Date (date field)
Transaction_Type (text field)
TRansaction_Description (text field)
Client_Debit (A number field, any charges incurred are entered here)
Client_Credit (A number field, payments or other credits entered here)

The Client_Account subform displays all these fields in a continuous
forms type view (except the first two key type fields). At the bottom is
a calculated field that uses the formula

=NZ(Sum([Client_Debit]))-NZ(Sum([Client_Credit]))

to determine how much money the client owes. (Many clients make a
deposit when they place an order, so there is a balance on their account
due on delivery. Others pay in full at the time of order so there is no
balance.) If the charges and payments that are linked to any particular
client are equal, then the sume of Client_Debit and Client_Credit are
equal and this field displays zeros, otherwise it displays the total
amount owed by the client. this subform works perfectly.

Now for the reports I want to create:

I want a report that only shows the clients that owe me money. (Actually
I want two reports like this, one only showing the client name and total
amount owed with a total of the money owed at the bottom, and one that
shows all the clients that owe money and under each client a list of all
of that client's transactions.

Like I said, this is probably simple, but I'm better at creating images
than databases, so I appreciate any help. Remember, I really know very
little about this so don't assume that I know much of anything in your
reply. :)

Thanks again!!!
Robin
Click to expand...
 
Hey thanks!

Wow, fast reply especially considering how detailed and easy to follow
your instructions were!!! I did it just like you said and it worked
perfect the first time!

Thanks,
Robin
 
Back
Top