Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation on Form/Report (2003)

    Attached is a result of a query ( I modified my query to a table in order to reduce the size of my file for attachment). I'm not sure if I need to do a form or a report to handle the next step. I want to be able to allow the user to select the Customer Name and the Routing_NO and it would return a sum of count/amount based on the status/date.

    Example: If I select WELLS (customer name) and 9128 (routing no), then the result would be
    10/1/08 WELLS 7(count) $1102.67(amount) for "OnTime" and 3(count) $42.91(amount) for "Missed Target".
    10/2/08 etc.....

    Thank you for all your effort and time. I'm sorry I'm stink at Access...
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculation on Form/Report (2003)

    Why does your table contain SumOfCount and AvgOfCount with exactly the same values as Count, and similar for Amount? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    You can create a form with combo boxes from which the user selects a customer and routing number, and a query that takes the values of these combo boxes to return the desired data. This query can be used as record source for a form or report.

    See the attached version. (I didn't create error checking)
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation on Form/Report (2003)

    The reason I added the SumOfCount and AvgOfCount is to sum the total counts and amounts. Let say...the user selected the date range from 10/1/08 to 10/31/08 then it would give me the sum of all the counts and amounts at the bottom. When I tried the combo box, it would give me just 1 BAA unique field but rather multiple of BAAs.
    If I already have a parameter date (Between [Enter Start date mmddyy ] And [Enter stop date mmddyy ]) in my query under Date column, can this field be added to the form?

    As always...you're amazing!!<img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculation on Form/Report (2003)

    You could, but the format of your date field isn't suitable - you should either have numbers in yyyymmdd format or use a Date/Time field. With the current numeric mmddyy format, entering 121608 as start date and 011509 as stop date would cause 050301 to be included, since it is between 11509 and 121608 numerically.

    But see the attached version for how to use text boxes.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation on Form/Report (2003)

    I modeled my db just like yours and it works very nicely, except for a minor problem. Since all my queries (routing no, results, customer) are based on my boss's query , my boss's query has a "date" field and its criteria is Between [Enter Start date mmddyy ] And [Enter stop date mmddyy ] so every time I make any select from the drop-down menu (cbo) it forces me to enter a parameter value multiple times. How can I incorporate his date into my form? or should I delete his date's criteria from his query..(he might have my head on the platter for that).

    Thank you.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculation on Form/Report (2003)

    Perhaps you could make a copy of your boss's query, remove the parameters from the copy, and use that for your own purposes. Your boss's query would remain unaltered so your head might be spared.

  7. #7
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation on Form/Report (2003)

    That's a splendid idea. Dahnk for saving my head

    Good night.

Posting Permissions

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