Access Invalid Operation on a grouped query

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

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
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top