# Thread: Need help Calculating with Access.

1. Registered User
Join Date
Nov 2008
Posts
12

## Unanswered: Need help Calculating with Access.

I need help with a couple different things, the first one is simple I bet. I've done a few searches, but is someone can point me in the right direction that would be great.

I have a simple database that I used to has the data of my sales each day. I want to be able to calculate a few different things, and put that on a report, so I have an overview of everything. I can do this easily in excel, but I'm sure its probably easy in Access too. Some of thing I'd like to be able to do are:

* Calculate how much profit I'm tracking. Basically, it will total a column (gross profit), and divide by working days this month (will prompt for this (unless you know a way for it to automatically figure this out), then multiply by working days of the month (a figure I will build in).

* The rest are all simple totals I need to pull from different columns in my query and put the totals (some will require a few extra equations)

Now for the hard part........

I have this query, that list customer info, profits, status of the deal, date of the deal, and the salesman. I want to be able to total up how many deals each salesman has done. Sounds simple right? The problem is sometimes there are 2 salesmen. A "split" deal, and this only counts as 1/2 a deal. There is a field for Salesman #1, and Salesman #2. If Johnny is in salesman #1 field, and salesman #2 field if blank, it is a whole deal (1). If Johnny is in Salesman #1 field, but Tommy is in Salesman #2 field, then each have 1/2 deal. I can change what is in these fields (it is automatically generated from my accounting program). I would be so helpful if someone could help me figure this out.

-Dave

2. Registered User
Join Date
Oct 2009
Posts
340
one can do calculated fields in a query or in a form/report - 2 different places.

query calcs are for within row only - for instance
TotalRev:[field 1] x [field 2]

you will then have a TotalRev field in your query results with a value for each record.

Calcs in Forms/Reports can be multi record - i.e. Sum the entire column/field - - it most easily done in Reports for novices because the Report wizard prompts you (and includes other typical math; sum, max, min, &#37; etc)

As to PartII : you need to learn up on IsNull and IIf statements

Probably in the query that is going to be your source for your report - you will have the calculated field that is something like this:

FinalShare1: Iif ((IsNull(SalesMan2)),SalesMan1,SalesMan1 x 0.5)
looks like gobbledegook but should work if you plug in your actual field names

3. Registered User
Join Date
Nov 2008
Posts
12
Originally Posted by NTC

FinalShare1: Iif ((IsNull(SalesMan2)),SalesMan1,SalesMan1 x 0.5)
looks like gobbledegook but should work if you plug in your actual field names
I tried that, and it didn't work. There was an Error on all the ones that had a salesman for Salesman 2. Basicly there are a list of say 200-300 Deals. There are 2 Collumns for Salesman. Salesman 1, and Salesman 2. How man I make it Could all Deals that have Salesman 1, but and empty Salesman 2 field count as 1.0 Units. And on the deals that have a salesman1, and a salesman2, Salesman1 would count as .5 units, and Salesman 2 would count as .5 units. Then run a Query and find out how many each guy has.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•