trouble with union query

[Effie]

I have a bunch of union queries in my Access database in which I've written
the following type of SQL code:

SELECT [Field1]
FROM [Table1]
WHERE [Field2]=True
UNION SELECT [Field 3]
FROM [Table1]
WHERE [Field4]=True;

These queries work fine in MS Access, but when I try to pull these queries
into a program I'm using for statistical analysis (R and RODBC), I get the
following error message:
[RODBC] ERROR: Could not SQLExecDirect
07001-3010 [Microsoft] [ODBC Microsoft Access Driver] Too few parameters.
Expected 3.

I am totally a novice at SQL -- the SQL for union queries is about all I
know because Access has no point and click option. Can any of you tell me
whether there is there something fundamentally wrong with the SQL I've
written above?
Can any of you decipher this error message for me?

 

[Jeff Boyce]

Since the "program [you are] using for statistical analysis" seems to have
the problem, have you considered asking in a newsgroup specifically
dedicated to that program?

By the way, and perhaps I'm reading way too much into your UNION query, but
it looks like your data structure may be more like a spreadsheet than a
relational database.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Effie

The problem I'm having is that your SQL statement works in Access, but not
in something else (that I don't work in either <g>).

Is there a chance you can get into the HELP that comes with that stats
package? There may be example of syntax you could adapt.

If you are interested in learning more about tables and dbdesign, consider
posting a description of your table structure to the tablesdbdesign
newsgroup. By that, perhaps something like:

tblStudent
StudentID
FirstName
LastName
DOB

tblClass
ClassID
ClassTitle

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have tried getting help from the people who run the statistical
program --
they've been kind of curt and said I'm probably writing the SQL
incorrectly
and that its a problem with the ODBC, not their program ... which sent me
here to try to get some opinions on the SQL I wrote.

I think my data structure is probably o.k., at least for my purposes --
these are sort of special built-up queries for analyses, and not really
the
main database. On the other hand, I'm sure it could all be better, and if
you'd like more details on these union queries, I am gung-ho to learn and
get
any advice I can

Since the "program [you are] using for statistical analysis" seems to
have
the problem, have you considered asking in a newsgroup specifically
dedicated to that program?

By the way, and perhaps I'm reading way too much into your UNION query,
but
it looks like your data structure may be more like a spreadsheet than a
relational database.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a bunch of union queries in my Access database in which I've
written
the following type of SQL code:

SELECT [Field1]
FROM [Table1]
WHERE [Field2]=True
UNION SELECT [Field 3]
FROM [Table1]
WHERE [Field4]=True;

These queries work fine in MS Access, but when I try to pull these
queries
into a program I'm using for statistical analysis (R and RODBC), I get
the
following error message:
[RODBC] ERROR: Could not SQLExecDirect
07001-3010 [Microsoft] [ODBC Microsoft Access Driver] Too few
parameters.
Expected 3.

I am totally a novice at SQL -- the SQL for union queries is about all
I
know because Access has no point and click option. Can any of you tell
me
whether there is there something fundamentally wrong with the SQL I've
written above?
Can any of you decipher this error message for me?