Results 1 to 6 of 6
  1. #1
    PRTP is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    2

    Select query to gather results of other select queries

    Hi



    I'm fairly new to Access. All I can do so far is tweak an existing query or setup the most basic select query.

    I'm trying to setup what I thought would be a basic query but I'm puzzled.

    There's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.

    e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
    Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
    Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B

    I want a select query that returns 1 row showing (6 items):
    Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.


    I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.

    Is there a way?

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,011
    You could try a UNION query. Add all three queries into 1...Select * from Q1 union Select * from Q2 union Select * from Q3.
    BUT union queries must have the same # columns so you may have to pad each individual query. i.e:
    Q1 = [Average Age], [Average Price], [Total rainfall], "" as Average Weight, "" as Average Salary, "" as Total snowfall...
    Q2 = "" as Average Age, "" as Average Price, "" as Total rainfall, [Average Weight] ,[Average Salary], [Total snowfall]...
    etc.

  3. #3
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    If you already have the query 3 to return the information that you need, why don't you use DLOOKUP and extract the information from query 3.

  4. #4
    TomLoder is offline Novice
    Windows 8 Access 2013
    Join Date
    May 2014
    Posts
    1
    You could do this by using DLookUp, or otherwise if you have more than 2 towns,
    You could build 3 cross tabs for the Distance, Population and Hours of Daylight, than build a single query that combines these three with you original 2 queries into one very wide table.
    The SQL for the cross tab might be something like:
    TRANSFORM
    Avg([Hours of Daylight]) AS [Hrs]
    SELECT "POP" as RHEAD
    FROM TownData
    GROUP BY "POP"
    PIVOT TownData.Town;

  5. #5
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    I agree with the UNION ALL query.

    I haven't tried more than combining two though.

    Code:
    SELECT rptRetention.*
    FROM rptRetention;
    UNION ALL 
    SELECT rptRetentionBSMtoRegMbr.*
    FROM rptRetentionBSMtoRegMbr;
    I would imagine it would be something like this:
    Code:
    SELECT rptRetention.*
    FROM rptRetention;
    UNION ALL 
    SELECT rptRetentionBSMtoRegMbr.*
    FROM rptRetentionBSMtoRegMbr;
    UNION ALL
    SELECT [queryname3].*
    FROM [queryname3];

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,423
    I disagree with UNION approach.

    One way to accomplish is to make query 3 into 2 queries, each filtered to one of the towns.

    Since each query results in a single record, simply pull all 4 queries into the query builder but don't bother with join links. Drag desired fields to the grid.

    These 5 queries could be constructed as a single SQL with nested subqueries.

    DLookup as suggested is another approach.

    A third option requires VBA code.

    I do wonder if queries 1 and 2 could really be accomplished with 1 query, but since don't know your data structure can't determine that.
    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. Queries to select MAX
    By johnseito in forum Queries
    Replies: 20
    Last Post: 09-15-2013, 07:13 AM
  2. Select Query returning no Results
    By Rhemo in forum Access
    Replies: 2
    Last Post: 09-15-2012, 04:11 AM
  3. Replies: 5
    Last Post: 02-27-2012, 08:37 PM
  4. Replies: 11
    Last Post: 12-14-2010, 01:25 PM
  5. Replies: 2
    Last Post: 03-31-2009, 11:15 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