Re: Report filtered on a calculated field

From: Steve Schapel (schapel_at_mvps.org.ns)
Date: 02/16/04 

Date: Tue, 17 Feb 2004 10:31:16 +1300

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 Hickman wrote:
> 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
>

Relevant Pages

  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
  • Re: Run macro for each record in a query
    ... the new query will pick up the correct ... This will make the report run for the current user. ... which contains details of 'client ... I have a macro called 'send_emails' which uses the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Field Blank in Report - New Information
    ... Please post the SQL of your query. ... I've been testing the report and have noticed that.. ... when I create a new record and type in a [client name] ... It's in the query that I created the [SortName] field. ...
    (microsoft.public.access.reports)
  • Re: Can I add a summary in the report footer to do this...?
    ... The report's source is the Audit Details Query. ... There is another text box with a similar calculation, ... As the report runs through each BU it does the calcs, sums them up in the BU ... >>> Create a totals query similar to your report's record source. ...
    (microsoft.public.access.reports)
  • Re: Page totals error in report - doesnt add correctly
    ... display it on a form or report. ... query based on the Import Table. ... All Totals text boxes properties are set to General number with 2 decimal ...
    (microsoft.public.access.reports)