Results 1 to 3 of 3
  1. #1
    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.

    Thanks for all your Help!

    -Dave

  2. #2
    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, % 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. #3
    Join Date
    Nov 2008
    Posts
    12
    Quote Originally Posted by NTC View Post

    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
  •