I've inherited an Access db which is used as the source for a dataset in Excel. Excel connects using ADO and Jet.OLEDB and runs a simple select:

SELECT
QUERY.A, QUERY.B, QUERY.C, QUERY.D
FROM QUERY
GROUP BY
QUERY.A
QUERY.B
QUERY.C

I now need to be able to filter on another field, which is already present in the dataset, so select on QUERY.E and group on it as well. Field E only has two possible values, 'T1' or 'T2'. So far so straightforward.

The problem is that when I run this query, E returns only 'T1' in every row, which is not what is in the db. Further, if I copy and paste the SQL into a new query in Access, I return the correct dataset. So there's nothing wrong with the SQL, it just returns the wrong data when I do it via Excel.


As you may have noticed, I am running this SQL against QUERY.A etc. The way the db is set up is that there are a load of 'tables' which are direct links to flat files, then a selection of tiered queries along the lines of:

Query1:
SELECT A, B, C, D, E from File1
UNION
SELECT A, B, C, D, E from File2

Query2:
SELECT A, B, C, D, E from File3
UNION
SELECT A, B, C, D, E from File4

Query3:
SELECT A, B, C, D, E from Query1
UNION
SELECT A, B, C, D, E from Query2

and the QUERY in my SQL statement is the top-level one, ie it ultimately ends up querying all of the source files via other queries. This may or may not be a problem...it's not how I would have done it but it's what I've got.

Has anybody seen this sort of thing happen before? Is it related to ADO, or to the fact that I'm querying a query, or something else?

Thanks.