Results 1 to 4 of 4
  1. #1
    OB7 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2013
    Posts
    16

    Why query prompting for parameter value? - Aggregate function and Calculated Field?

    My query contains the following two fields (among others):

    Query Field #1
    Credits (numeric data type)


    ("Total" in Design View is set to "Sum"; an aggregate function)

    Query Field #2
    FTE: [SumOfCredits]/15


    When I run this query, I'm prompted to enter a parameter value for "SumOfCredits." If I just click "OK" without entering any value and allow the query to run, it appears to run correctly--the query results are correct.

    Why is it prompting me for a parameter value? Is there any possibility of incorrect or unexpected query results in this situation, and, if so, is there a work-around?

    Thank you!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    The query can not find SumOfCredits. Is this an alias within the query? You may have to build a separate query and join that query to get a valid SumOfCredits field. Seems as though you may be trying an aggregate calculation. Give the query something to bite onto by getting your sum first.

    The other thing may be SumOfCredits is a field name in more than one table in your query. If so, try adding the table name in front of the field name

    FTE: [TableName].[SumOfCredits]/15

  3. #3
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    157
    Try Aggregate Query

    Query Field #1
    Field:Total_Amount:Credits
    Table:Table Name
    Total Sum
    Query Field #2
    Field:FTE: [Total_Amount]/15
    Table:
    Total Expression



    Query Field #2
    FTE: [SumOfCredits]/15

  4. #4
    GAtkins is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    11
    Try setting the FTE field to "Expression" in QBE view.

    Glenn

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

Similar Threads

  1. Replies: 3
    Last Post: 04-18-2012, 07:10 AM
  2. Parameter Query on a calculated field
    By l3111 in forum Queries
    Replies: 5
    Last Post: 10-12-2011, 02:18 PM
  3. parameter prompting multiple times
    By sandyg in forum Access
    Replies: 1
    Last Post: 08-15-2011, 10:10 AM
  4. Replies: 0
    Last Post: 04-25-2011, 07:58 PM
  5. Access SQL Query - Aggregate function
    By jack in forum Access
    Replies: 0
    Last Post: 11-10-2009, 08:06 PM

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 - Senior Forums