1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Invalid Operation on a grouped query

Discussion in 'Business Applications' started by peggieg, Sep 24, 2008.

Thread Status:
Not open for further replies.
  1. peggieg

    peggieg Thread Starter

    Joined:
    Sep 24, 2008
    Messages:
    2
    I have an Access 2003 database with linked tables from a SQL2000 database. I am trying to create the following query:

    SELECT C.ID, L.MaxofCollect_date
    FROM Table1 C LEFT JOIN table2 L
    ON C.ID = L.ID;

    Where C is an Access table, and L is a query based on a linked SQL2000 table. I keep getting an error message saying "invalid operation". This seems to occur when I outer join a query with a Min/Max/Last, etc. field.

    Any ideas as to why this is happening? I've been solving it by making the queries into Access tables, but that is a real pain.

    Thanks!

    Peggie
     
  2. peggieg

    peggieg Thread Starter

    Joined:
    Sep 24, 2008
    Messages:
    2
    I played with this further, and discovered if I remove the grouping (Max in this case) from the first query before I join it to the Access table, the problem resolves itself. Apparently the program can't figure out the order to perform these operations in? This seems like bug, but I'm not willing to pay Microsoft $259 to find out.

    Any light that could be shed on this problem would be appreciated, I've run into this frequently.

    Peggie
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    It could be the fact that due to the Max Filter that you end up with uneven Joins between the C.ID and the L.ID.
    Any possibilty that you could copy some of the records from the SQL2000 table into an Access table and then post it on here so that we can look at it.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/752982

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice