Simple SQL Query. I want mutiple commands 2

[dewsbury]

Hi,

This is probably a fairly basic query...
I am familiar with SQL in general but new to Microsoft Access.

I want to do multiple commands within one query but cannot.

Simple example:
Select * from file1 where name1="bob";
Select * from file1 where name1="tim";

I get an error saying "CHaracters After end of SQL Statement"

Please help !

 

[Golom]

Try

Select * from file1 where name1 IN ('bob','tim');

 

[GoDawgs]

Confused as to what you are doing...if you want to see both tim and bob records this will work:

Select * from file1 where name1="bob" OR name1="tim";

You can't just put two sql statements next to each other and run them in the same query...hope that helps.

Kevin

 

[dewsbury]

Thanks for your help.

(I am a relatively experienced user of Informix Sql).

I had hoped it was possible to put more than one SQL statement in an Access SQL script.

Since this is apparently not possible how do I achieve more sophisticated SQL matters.
E.g. on a monthly basis I want to run a script that will clear down data more than a year old and at the same time import new data.

If I am pushing the limits of Access SQL then where do I go from here?

Thanks again.

 

[GoDawgs]

You can't pull it off simultaneously...just one at a time (as far as I know...I've been proven wrong before on here though). Sorry for the talking well below your level...

Kevin

 

[dodge20]

As an Informix user also, I was frustrated by this in access (especially the temp tables). But if you know any VBA, you can probably accomplish what you want. There are some very good VBA programmers here, you may want to post your actual question and someone can probably help you out.

Dodge20

 

[substitute]

For most SQL scripts you don't need to go into VBA. Save each individual SQL statement as a query and write a macro to execute them one after another. You can get some of the functionality of temporary tables by turning warnings off around action (e.g. select..into) queries as the default response is usually what you want to do.

A problem you'll have if you are used to Informix SQL is that Access SQL has a different set of functions and operators. I have the same problem switching from SQL Server. People deride the use of the query grid and expression builder but they do help get the hang of the language differences.