Results 1 to 15 of 15
  1. #1
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28

    Help with Query See attached sample database

    I am creating a database but I am stuck on a query. If you look on the contact form, on the reporting tab it has a section called all products sold current month by premium amount. YOu will see two numbers under EPLI total. I need everything in that column to sum in a different cell. And leave the numbers next to the company listed. I need this for each column on that section. Then it would be nice to add everything together and multiply by ten and show that result.



    Is this possible.

    CRM-Version-2.1.4.zip

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Oops.....Never mind.....I see the attachment now.

  3. #3
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    I just reposted the newest database. please use that one.

  4. #4
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    I was wondering if someone could help with this.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'll try and look at your dB tonight, snow permitting. (received 9 inches of snow yesterday and last night - so still shoveling)
    I have A2K and can't open the dB.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You must be using access 2013 because I can't open it with Access 2007 either. Try converting it to an earlier version.

  7. #7
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    Quote Originally Posted by rpeare View Post
    You must be using access 2013 because I can't open it with Access 2007 either. Try converting it to an earlier version.
    I actually have access 2007 on my computers.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Can't you expand the form footer of that subform and put in a text box with the formula =sum([Fieldname]) to get the total?

    I was able to open this with 2013, but not 2007.

  9. #9
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    Quote Originally Posted by rpeare View Post
    Can't you expand the form footer of that subform and put in a text box with the formula =sum([Fieldname]) to get the total?

    I was able to open this with 2013, but not 2007.
    That worked. Haha Simple solution. So how would I format that to a dollar format?

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    open the properties window of the field in the form footer and look at the FORMAT setting, one of them should be currency

  11. #11
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    Oh my. Again super Simple. Haha. Thanks for your help. I don't know how to mark this solved.

  12. #12
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    My follow up to this. I tried to Sum 4 fields and it is not working. It only allows me to sum up to 2. Also I will need to do an equation for commision which is 10%. Can all that be done?

  13. #13
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    Actually I think I see the issue. It will not allow me to sum a field if one of them is blank. Im not sure why that is. Any ideas.

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    sum(nz([fieldname], 0))

  15. #15
    lehi53 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2013
    Posts
    28
    I figured it out. Thank you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 29
    Last Post: 08-16-2011, 05:52 PM
  2. Replies: 6
    Last Post: 06-27-2011, 07:11 PM
  3. sample database
    By TP takamiya in forum Access
    Replies: 4
    Last Post: 10-06-2010, 07:33 PM
  4. ULS database sample please...
    By genesis in forum Access
    Replies: 1
    Last Post: 07-17-2009, 12:12 PM
  5. Northwind sample database query
    By wasim_sono in forum Programming
    Replies: 5
    Last Post: 02-28-2006, 07:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums