Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Red face Unanswered: Combining two queries

    I'm trying to combine two queries in Access. At this stage, I use one query to load stuff into a table called TempCount, then another query to transfer it from there to PCPD.

    This is the query to put stuff into TempCount:
    ===================================
    SELECT SED.Date, PC.PCP, SUM(PC.HouseHolds * SED.[% <512]) AS Connections
    INTO TempCount
    FROM (SED INNER JOIN PC
    ON PC.[SE-Type]=SED.[SE-Type])
    GROUP BY [SED].Date, PC.PCP;
    ===================================

    And from there it goes into the final table:
    ===================================
    UPDATE PCPD, TempCount
    SET PCPD.[Count <512] = TempCount.Connections
    WHERE TempCount.[Date] = PCPD.[Date]
    AND TempCount.[PCP] = PCPD.[PCP];
    ===================================

    I need to combine them b/c the database is very large and takes many hours to run, so halving the run time is significant. More importantly, when I drop the temp table, of course it doesn't really drop it, but just tags it for deletion. This query is actually run many times in my application, so I end up with numerous temp tables, and Access hits its 2GB limit before completion and so falls over.

    My attempt to combine the queries is below:
    ===================================
    UPDATE PCPD, SED, PC SET PCPD.[Count <512] = (
    SELECT SUM(PC.HouseHolds * SED.[% <512])

    FROM (SED INNER JOIN PC ON PC.[SE-Type]=SED.[SE-Type])
    )
    WHERE SED.Date = PCPD.[Date]
    And PC.PCP= PCPD.PCP;

    ===================================

    which comes up with the error "Operation must use an updateable query". I'm sure that what I'm trying to do is possible, and fairly sure that I'm close, but unfortunately close doesn't count for much!
    Suggestions really would be appreciated.

    Cheers,
    B.

  2. #2
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Re: Combining two queries

    Is the query running on local or network data?

  3. #3
    Join Date
    Nov 2003
    Posts
    14
    The whole thing is running locally.

  4. #4
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    As far as I know, I do not think you can use a subquery to define the 'set' value in Access, not like you can in Oracle. Even a simple sql statement, like...

    update table_1
    set field_1 = (
    select pk_id
    from table_2
    where pk_id = 1
    )

    ...produces the 'must use updateable query' message.

    Even if you were on Oracle, though, the sql...

    UPDATE PCPD, SED, PC SET PCPD.[Count <512] =
    (
    SELECT SUM(PC.HouseHolds * SED.[% <512])
    FROM (SED INNER JOIN PC ON PC.[SE-Type]=SED.[SE-Type])
    )
    WHERE SED.Date = PCPD.[Date]
    And PC.PCP= PCPD.PCP;

    ...would not produce the desired results because there is no join between the fields of the subquery and those of the parent query. The SED table in the subquery would be interpreted as being a different one that the SED table in the parent query.

    I don't know if it would save any time, but you could try saving your select into query as a select query, then running the update query off that, with the appropriate joins. It would prevent the temp table issue anyway.

    In Access I archive data as much as possible into other mdb files, just to keep the tables from getting too unwieldy. Then if needed I create a third app to do reporting or whatever, I just export the very minimum amount of data (records and columns) necessary for the reports. A bunch of little Access db's are a lot easier to handle than one mammoth one.

  5. #5
    Join Date
    Nov 2003
    Posts
    14
    Thanks for your very helpful reply. It's good to know that what I was trying to do wasn't possible (not as good as being shown that is was possible though! :-) )

    I am now trying to make it work using SELECT queries rather than creating temp tables. Unfortunately, I'm still having trouble.

    The query I am using now is:

    UPDATE [PCPD], TempQuery
    SET [PCPD].[Count <512] = [TempQuery].[Connections]
    WHERE TempQuery.Date = [PCPD].[Date] AND TempQuery.PCP=[PCPD].[PCP];

    TempQuery is a SELECT query with three fields, Date, PCP, and Connections.

    This gives me the same error that I had earlier: Operation must use an updateable query.

    Would really appreciate it if you could tell me what I'm doing wrong - or more importantly, how to do it right!

    Cheers,
    B.

Posting Permissions

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