CFQUERY an MS Access Query

[parly]

I think I remember doing this before with earlier versions of CF but I'm having trouble querying an Access query with MX. I want to do this because I'm trying to offload some aggregate processing to the database hoping to make the app more efficient. Isn't the syntax the same to query a query in Access as it is to query a table? This is what I used that won't work.

<cfquery name="getData" datasource="MDS" dbtype="ODBC">
SELECT *
FROM MyAccessQuery_NotATable
</cfquery>

 

[r937]

please explain "won't work"

the query syntax looks okay

rudy

http://r937.com/

SQL Consulting

 

[TruthInSatire]

that works for me.
averageListeners is a query run in access i just call it like a table.

<cfquery name = "qDjRanking" datasource = "#application.dsn#">
SELECT dj, Sum(averageListeners) AS avgListeners
FROM averageListeners
GROUP BY dj
ORDER BY sum(averageListeners) Desc
</cfquery>

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[parly]

r937,

What doesn't work is the result. It throws the error, fieldname does not exist. If I copy the result of the Access query I'm querying from CF into an *table* and use the same syntax it works fine. I just can't figure this out.

bombboy,

Although my CFQUERY is much simplier than the one you posted it would seem as though mine would work but I can't figure out why it doesn't.

 

[r937]

fieldname does not exist?

i bet it doesn't exist

this is just another example of one of the many reasons why you shouldn't use "select star" in your queries, whether they be queries of tables or queries of views (i.e. views are stored queries in access)

try listing the exact columns you want, and that should narrow down where the problem is

rudy

http://r937.com/

SQL Consulting

 

[TruthInSatire]

I would be willing to bet the field you're looking for exists in one of the original tables but does not exist in the view. if you want that field in CF, you have to add it to the view in access.

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[parly]

r937 & bombboy,

I changed the query to read:


<cfquery name="getAtRisk" datasource="ARENA" dbtype="ODBC">
SELECT AccidentDate, SSN, AccType
FROM getAggregateAccComposite;
</cfquery>

These are the only three fields I need to see. This is a UNION query in Access.

The exact error is:


[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.

SQL = "SELECT AccidentDate, SSN, AccType FROM getAggregateAccComposite;"

Data Source = "ARENA"

This is the SQL I'm using for getAggregateAccComposite


SELECT AccHistory.AccDate, AccHistory.iSSN, AccHistory.Type_Desc
FROM AccHistory
WHERE (((AccHistory.AccDate) Between Now() And Date()-730) AND ((AccHistory.iSSN)<>"null"))
UNION SELECT AccInjLog_WO_Matching_AccHistory.AccidentDate, AccInjLog_WO_Matching_AccHistory.SSN, AccInjLog_WO_Matching_AccHistory.AccType
FROM AccInjLog_WO_Matching_AccHistory
WHERE (((AccInjLog_WO_Matching_AccHistory.AccidentDate) Between Now() And Date()-730) AND ((AccInjLog_WO_Matching_AccHistory.AccType)<>"Incident"));

Each section of the UNION works in CF when it's not joined but as soon as add the UNION it throws the error about field names not existing. They do.

I guess I may as well post the SQL for AccInjLog_WO_Matching_AccHistory too:


SELECT AccInjLog.AccidentDate, AccInjLog.SSN, AccInjLog.AccType
FROM AccInjLog LEFT JOIN AccHistory ON AccInjLog.SSN = AccHistory.iSSN
WHERE (((AccInjLog.AccidentDate) Is Not Null) AND ((AccInjLog.SSN)<>"999999999") AND ((AccHistory.iSSN) Is Null));


I hope you can see what I'm doing wrong as this is driving me nuts and I'm running out of time...Thanks for everything.

 

[r937]

does that actually say iSSN and not SSN?

that'd be your error

rudy

http://r937.com/

SQL Consulting

 

[parly]

Rudy,

At one point I thought that it was choking on the SSN field because both original tables (AccHistory and AccInjLog) used the same name field, SSN. So I changed the name in AccHistory to iSSN so now each table has field names which do not match. However, as you know, it still throws the error, so then I changed the fields to match exactly, (AccDate, SSN, AccType) and still received the same error.

Currently both tables have different field names but same datatypes and it's reflected in the SQL.

All these SELECTS work separately in CF. It's choking on the UNION.

 

[r937]

the column names for the result set of a UNION query are taken from the first subselect

so if currently both tables have different field names, make sure the names used in the first select in the UNION are the names your CFQUERY uses



rudy

http://r937.com/

SQL Consulting

 

[parly]

They are.