MS Access Union Join not working w/ Coldfusion MX

[sic4730]

Hope this makes sense. I'm new to this and trying to figure it out.
I'm having problems getting a union join to work w/ Coldfusion MX 6.1.
I have created a union join within MS Access. The join pull information from
two tables and places them into the query.
The query works fine w/ no errors when I run it.
I created a regular query w/ Access pulling the information from one of the
tables and that works fine. Just not with a join query.
However when I try to pull data from the union join query using Coldfusion I
get the following error message:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Too few parameters. Expected 1.

Exceptions

15:40:49.049 - Database Exception - in C:\Inetpub\Service\Login_Action.cfm :
line 9
Error Executing Database Query.

Here is the code from w/ in my cfm page:

<CFIF IsDefined("form.Agency_Code") AND isDefined("form.ReqAddress")>


<cfoutput>
<CFQUERY NAME="All_Logins" DATASOURCE="Service_Insurance">
SELECT *, number
FROM qry_webaccess
WHERE (number = '#form.Agency_Code#')
</CFQUERY>
</cfoutput>

Is this not possible??? If there is another way to accomplish this I'm
interested in learning.
This just seemed like the only way I could combined 2 tables that have same
field names into one to use.

[paross1]

Your query does not make any sense to me. You are selecting from only one table
(qry_webaccess), but selecting * and number. Is number a field in another
table, or would that be the * (all fields)? Your current query example is not a
UNION (nor a JOIN), and perhaps you are confusing the term UNION with the term
JOIN, which do not mean the same thing. Why don't you list your tables, and the
applicable columns that you wish to select, and which columns link your tables.

Phil

[sic4730]

The query example from above is within my login_action.cfm page. I agree with
you not sure why the number is there when using the * (someone else created our
site & now I'm trying to making changes to it.) I could take the number out
because it is pulling all columns including the number column. The query on
that page is only pulling from a query in my database named qry_webaccess.
Below are the two tables and fields I'm trying to merge into one. (The
database has about 30 tables but the 2 below are the only one I need for this
project).
dbo_agency1(table)
number
state
password
name
security_level
dbo_subcode (table)
number
state
password
name
security_level
In the database I created a Union query to merge the data from the 2 tables
above into 1.
Here is the code I used to create the query for qry_webaccess.
select [number], [state], [password], [name], [security_level]
from [dbo_agency1]
where [state] = "fl"
UNION
select [number], [state], [password], [name], [security_level]
from [dbo_subcode]
where [state] = "fl";
When I run this query in Access it works fine.
Is there a better way of doing this?? I know Coldfusion can pull from a query
because I created a normal query in Access using the design view, w/ the same
name as my Union query (qry_webaccess) only pulling data from one table and it
worked fine.
I must note that currently those 2 tables have no columns that link the
tables. I tried creating a field in both tables (AgencyID) that would link the
two but when I did that and ran a query from the 2 tables it returned double
entries. When I removed the link (AgencyID) it worked fine.
Hope this helps!

[paross1]

Except for usint double quotes instead of single, and possible reserved word
issues with come of your column names, your UNION should work without errors.
You might try aliasing some of the column names that may cause you problems.

SELECT [number] AS num, state, password, name, security_level
FROM dbo_agency1
WHERE state = 'fl'
UNION
SELECT [number] AS num, state, password, name, security_level
FROM dbo_subcode
where state = 'fl'

Phil

[sic4730]

That worked!!!
Thanks!