Differences when querying between Access and Excel

[zaza]

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.

[Ecniv]

1 - if possible do your coding in Access and export to excel where required
2 - import those flat files (again if possible - not sure why you wouldn't want to...)
/\ - Are you merging all the data together with all the unions? If so, can't you import the lot into a database table (just one) and do your querying off that?

You could erase the data before each reporting run from the table and re-import the flat files (assuming they change).

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.

You mean you want to only return T1 and T2 ... and ignore the rest? But its only bringing back T1?
You haven't posted how you filtered that...

[Ecniv]

it's not how I would have done it but it's what I've got

Ahhh so you can't change that?

Shame.


ADO does react differently to DAO on querying. Some queries will run in Access, but not from Excel. Usually its the difference in the SQL parser. eg fuzzy matching/pattern matching. In Access its '*' in ADO its '%'.

It may be something on this that is causing the problem.
The filter you need is probably :

WHERE QUERY.e in ('T1','T2')

Also might be an idea not to use Query as the name, perhaps use QryAllData or something to make sure its not clashing with internal code.

[zaza]

The name isn't actually QUERY, that was just for illustrative purposes. The thing is that there's nothing populating the database, all that's in there is a load of links to files (which define the "tables") and some queries which are already coded to return data based on those tables, and other queries which return data based on those queries.
As a result, the entire process is driven by Excel. At the moment, all that happens is Excel runs a SELECT on a subset of the fields in the top query that's hardcoded in the DB. It picks fields by name, and groups them. That's all. The act of running a SELECT on this top query causes the top query to reach out to all of the underlying files.
My problem is that I am grouping too much data. My dataset, as it currently stands, is aggregating T1 and T2 data together, because my SELECT takes no account of the value of field E. All I need to do is return field E into my dataset in Excel, so that Excel knows which rows correspond to T1 and which to T2, and then the rest of my processing will work from there. At the moment, there is no distinction. The problem is that by just adding it in as you would expect to, field E only returns the value T1, regardless of whether the database holds T1 or T2.
I'm not using any pattern matching in Excel, it's all defined. I don't even have a WHERE clause, so it doen't make a lot of sense to me that I would need to use "WHERE QUERY.E in ('T1,'T2')" (which would return the entire dataset anyway) given that I don't need to do this for fields A, B or C which also cover the entire dataset.
Perhaps "filter" wasthe wrong word. "Distinguish" would be better. I will still be returning the entire dataset, just with an extra field of data.

Unfortunately, I'm a bit stuck with the database structure as this workbook is not the only one connecting to the DB.



An analogy: my dataset is returning a load of information about computers; processor, RAM, graphics card, manufacturer and volume sold. I aggregate it together to sum up the Volume Sold (using GROUP BY). I have suddenly realised that I am presenting this data but am unable to tell whether it is a desktop or a laptop. I have a field in my database (field E) which tells me what this is, and it contains "Desktop" or "Laptop" (T1 or T2). All I need to do is retrieve it in the query and group on it. However, when I include it in the SELECT and GROUP BY, it always returns "Desktop", irrespective of the actual data in the database.

[Ecniv]

Yeah access can directly link to files. Not the best method, but if thats what you are stuck with we will just have to work with it.


Now lets take it step by step. Boring I know, but it might highlight something.

1 - do all the files have the column E ?
2 - in access can you see column e in the table view ?
3 - in access can you see column e in the query 1 view ?
4 - in access can you see the column e in the overall query (3?) ?

now the harder bits...
5 - in excel, a new sub, create a connection to the db and open a table directly. Does it show the data you expect?
6 - in excel, same sub, but change to pull back the query 1 - does it show the data as expected
*** (Use debug line and debug.print) ***
7 - in excel, same sub, point it to the overall query - does it show the right data.


All I'm doing is taking it step by step. If it doesn't return at the table view or first query, it wont be there for the rest. In which case we 've got to figure out why its not showing like it should be...