Results 1 to 2 of 2
  1. #1
    Adele is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    12

    Combining two queries

    Hi all,

    My aim is to get the total value of orders and the total quantity of orders for each month to appear like so:

    Total Value Total Qty Month
    2000 100 01/2011
    3500 200 02/2011
    1000 80 03/2011
    Etc…

    So far I have managed this by performing a query in two steps.

    As the original data doesn’t have a quantity field, the first query counts for each month the individual order numbers, their value and the qty:

    Order Number Value Count Month
    0125 100 5 01/2011
    0152 80 2 01/2011
    6845 250 3 02/2011
    Etc…

    I then run a second query from the first to sum value and count and group and group by month which appear as above:

    Total Value Total Qty Month
    2000 100 01/2011
    3500 200 02/2011
    1000 80 03/2011
    Etc…



    If you can help please reply with design view instructions as opposed to code as I’m not quite at that stage just yet!

    Many thanks,

    Adele x

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You can open the queries in SQL view. Nest the first query into the second query by copy/paste. Show your query statements for analysis.

    Example of nested query:
    SELECT Err_Cde, Count(Err_Cde) AS CountOfErr_Cde
    FROM (SELECT Prescription_ID, Err_Cde
    FROM Table1
    GROUP BY Prescription_ID, Err_Cde) As Query1
    GROUP BY Err_Cde;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. combining two queries
    By camell in forum Queries
    Replies: 4
    Last Post: 03-04-2011, 02:41 PM
  2. Combining crosstab Queries
    By Garito100 in forum Queries
    Replies: 0
    Last Post: 10-22-2010, 02:26 AM
  3. Combining queries
    By wildlifeaccess in forum Queries
    Replies: 10
    Last Post: 09-20-2010, 07:35 AM
  4. Combining Two Queries
    By csolomon in forum Queries
    Replies: 1
    Last Post: 09-03-2009, 01:33 PM
  5. Combining like update queries
    By xcr800man in forum Queries
    Replies: 2
    Last Post: 05-20-2008, 08:36 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