how to subtract number in column A from column B ? 1

[sdollen]

I had some help regarding how to count some items in a query. It's a contact log and I wanted to count the number of visits and calls made to our "clients'. Someone helped by creating a "count type" query which looked through the contact log and for each "client" would count the contact type and then in a seperate column display the number. With me so far?

He then created a query which pulls in a "desired" number of visits and calls for each client and puts them in an appropriately titled column, and then pulls the info from above and puts the info in columsn titled "visits made" and "calls made".

Now, I've tried several different ways to create a query to now look at each client and then compare the desired number of visits versus the number of visits made, then list the difference. I'm going to use that to create a report which will list the client and how many visits are left to be made so we can monitor it on a monthly basis. The same with the phone contacts.

Any help on this?

I would really appreciate it.

Thanks!

 

[Tranman]

sdollen,
If you can create a query that contains all of the info you need for each client (desired visits/completed visits, desired calls/completed calls; etc.), then all you need to do is create some calculated columns in the same query.

Do this by entering (in the field name box) VisitsRemaining:[Desired Visits] - [Completed Visits]

The same for CallsRemaining; etc.

Was that what you were asking?

Tranman

 

[sdollen]

WooHoo! That was exactly what I was asking... and, it works perfectly!!!!!

Many thanks!

-Stefan

 

[sdollen]

Oh wait..just noticed one thing though... if the number of visits desired is 1 and the number of visits made is blank (because no visits have been made yet) this returns a blank field. Same with the calls. I'd like to see it do this 1-0=1

Any thoughts on that?

Thanks again!

 

[Tranman]

Stefan,
This will fix that:

VisitsRemaining:[Desired Visits] - Nz([Completed Visits],0)

Paul