Invoice with If statement (2002, sp2)

I have a database with a customer table that lists a beginning balance and a minimum fee. Then thereare three sets of charges: mileage, fees, and other.

I have queries that have grouped and totaled the fees individually.

Now I need an invoice ( I suppose a report) for each client with the beginning balance, minimum fee, each of the three categories of charges, a total for the charges, then a comparision of the total charges to the minimun fee. The amount due will be the lesser of the two. If they are billed for the lesser amount, the overage should go to their balance due.

Re: Invoice with If statement (2002, sp2)

You are correct that you will need a report. I would create a subreport for each of the categories, with subtotals in the footer of each, and then sum the three subtotals to get the total charges on the main form. Then you can calculate the amount due based on the minimum fee, the total charges, and the prior balance due. Beyond that you will need something to adjust the balance due when payments are received, and so on, but the Invoice report should be fairly straightforward. Post back if/when you encounter further problems.

Re: Invoice with If statement (2002, sp2)

I have used subforms but not subreports. I set up my main report using the client table, which has name, balance due and minimum fee. Then I added a subreport based on one of my queries. I added a group footer with a sum function in it. When I preview the main report under each client I see their names repeated with a fee as many times as a fee was entered, and no total. I experimented with adding a group header to my subform (after opening it in a new window), and I tried making the name and fee fields in the subform not visible. I was unsuccessful in getting them to display as I wanted, and I never did see a total.

What do you think I am doing wrong??? Can you point me in the right direction? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

Re: Invoice with If statement (2002, sp2)

Subreports are much like subforms, but are designed to display multiple records in some way associated with the data source of the main report. To do a total, you put a text box in the footer of the subreport and make it a calculated expression. If you search in Access Help for Calculate a total or other aggregate values, you are given a detailed set of steps for doing just that. It also suggests another way of doing it by using the GroupBy capabilities of reports, and putting the total box in the group footer. It is a little easier to get a grand total that way on the report footer. We use both approaches. Try playing with the report wizard a bit, and you can see how it does grouping and sorting.

Re: Invoice with If statement (2002, sp2)

I have gotton the basic totals on my Invoice, thanks for your help. As you said, I also need something to adjust the balance due when payments are received. Right now there is a Client table with ID#, Minimum due, Balance Due. For the first month, the data would be entered in the table. I'm thinking of a query that has a payments field, then calculates a revised balance. Then my report control could change to be based on that field instead of the original balance due from the table.

Re: Invoice with If statement (2002, sp2)

How are you planning to record a payment? The typical way this is handled is with a single transaction table that contains both charges and payments, and you simply add up the payments and charges. Charges are typically positive, and payments are negative, so you add up the payments, subtract that from the prior balance, and then add the charges to get a new balance due. One of the issues with this sort of thing is auditability, so in systems where the accountants are involved, you may want to keep a running balance each time a new record is added, and track the prior balance and new balance with each transaction. In that case you never allow editing of a record once it has been entered. This may well be overkill in your situation, but it never hurts to be thinking ahead. Post back if this doesn't make sense.

Re: Invoice with If statement (2002, sp2)

My system is a little more complicated than that, I guess. There are three types of charges, each being recorded in a different table. Then there's a master table with the client name. That table has a beginning balance, which was going to be the amount brought in from another application. I also set up a payments table, again linked by client.

Two of the charges tables have queries that calculate totals. One is for mileage, which takes the miles times the mileage rate; the other for client fees. Depending on the fee, the rate varies. I have a query pull in the rate and multiply it times the time spent on it. The other charges are simple dollar amounts.

I created a query that brought the client name from the client table, the calculated field for the client fees and mileage, and the dollar amounts for the other charges. I included dates, since I figured they will be necessary. I added payments and date. I then tried to use a calculated field to get the balance due:

Re: Invoice with If statement (2002, sp2)

In order to debug what is going on with the query error message, I think we'll need to see the complete table structure, and the queries you are trying to run. It appears that somewhere you have an expression that is trying to aggregate the client name, but it's impossible to guess where the problem is without seeing it. Can you attach a zipped version for us to look at? I guess I don't understand how you plan to track and update the balance due field in the client table - can you describe the process?

Re: Invoice with If statement (2002, sp2)

Thanks for getting back to me. I'm afraid I wasn't planning enough ahead. I was so concerned with my invoice, and forgot that the beginning balance wouldn't STAY the beginning balance!

I set up a payments table, thinking that once payments are entered, then the query I posted about could calculate the new balance due, which would be pulled into the Invoice Report, which would calculated the three different types of charges and give an amount due. A lot of the invoices will not be paid in full each month. I'm also thinking my invoice will need to be revised to prompt for date ranges, so only new charges are added in.

I have attached a copy for you to review. Any and all suggestions are welcome.

Re: Invoice with If statement (2002, sp2)

Having quickly looked at your database I would be inclined to do what Wendell has suggested by putting the Payments and Charges into 1 table. The Client Mileage table doesn't really belong in this table though.
When I try to open the main form it comes up with an error message saying that the record source (Time by Employee) cannot be found, is this a table or query you have forgotten to include.