Results 1 to 3 of 3
  1. #1
    Schon731 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2010

    Query prompting for values?? Why?

    I have the following query.

    SELECT [tbl 572 Group Home Analysis].[PT-Patient Group], Sum([tbl 572 Group Home Analysis].[TX-Acq Cost]) AS [Total Acq], Sum([tbl 572 Group Home Analysis].[TX-Price]) AS [Total Price], Count([tbl 572 Group Home Analysis].[TX-Rx Number]) AS [# of RX's], Sum([tbl 572 Group Home Analysis].[$ Margin]) AS [$ Margin], [Total Price]/[# of RX's] AS [Avg $ per RX], [$ Margin]/[# of RX's] AS [Avg Marging per RX]
    FROM [tbl 572 Group Home Analysis]
    GROUP BY [tbl 572 Group Home Analysis].[PT-Patient Group], [Total Price]/[# of RX's], [$ Margin]/[# of RX's];

    When I run the query it prompts me for [Total Price], then for [# of RX's]. If I hit enter past each one it runs and the results are what I want. How do I get it to stop asking me for those values and just run the query?
    The table is created from a Make Table query if that matters.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Nevada, USA
    The problem is that the GROUP BY clause is evaluated before the SELECT clause, so the aliased names are unknown at that point. You can either base a second query one that does the initial calcs, or try this in the GROUP BY clause instead of [Total Price]:

    Sum([tbl 572 Group Home Analysis].[TX-Price])
    Paul (wino moderator)
    MS Access MVP 2007-2019

  3. #3
    Schon731 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2010
    That statement gives me a message about not being able to have an aggregate function in the group clause.

    Also, I have a field that contains a customer name that I want to add to that table. Then I want to count the number of customers served but it counts the number of items instead because I am also counting that field (RX Number) Can you help with that as well or would I again need another query first for that calculation? I am afraid that if I build it into the Make Table query that it will not count all of the items. I am starting to understand why so many queries are needed to get to a final answer. Thanks

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

Similar Threads

  1. Query Table Values in Append Query
    By AKQTS in forum Queries
    Replies: 5
    Last Post: 11-12-2010, 03:58 PM
  2. Replies: 3
    Last Post: 05-13-2010, 08:18 PM
  3. Query help for WTD and MTD values.
    By rzawora in forum Queries
    Replies: 1
    Last Post: 10-26-2009, 04:30 AM
  4. Replies: 3
    Last Post: 10-19-2009, 01:14 PM
  5. Replies: 0
    Last Post: 04-26-2008, 09:59 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