SQL Command for creating a Query 2

[thegad]

Hi,

As the SQL command "CREATE TABLE" allows creating a new table, I'm looking for the same kind of SQL command for creating an Access Query.

Thanks for your help,

Gad.

 

[nicsin]

Hi Gad,

I have used

CREATE VIEW view1 as SELECT * FROM table1

but as I remember it doesn't appear in the QUERIES window in ACCESS. I was able to use it in my program though.

 

[MakeItSo]

A QUery that creates a query? Are you sure you really need to do it that way?
If this helps you: You can always implement a sub query:

SELECT * FROM (SELECT whatever FROM WHEREEVER...)

Is that what you need?

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[BNPMike]

I think nicsin is right. Access allows you to create a query that you can then use like a table in another query. This is normally called a view in relational databases - and the on-line documentation says CREATE VIEW is valid but only for Jet databases.

 

[MakeItSo]

Aha! Didn't know that. Thanks nicsin and BNPMike.

 

[nicsin]

;-)

 

[thegad]

Thanks for the help.

I was already trying the CREATE VIEW before but indeed it does not create a QUERY in Access. Nicsin had the good idea to try if the view is hidden somewhere. Apparently, it is the case, so thank you. Good idea!

But... (there is always a but)

I try the command : CREATE VIEW view1 as SELECT * FROM table1 ORDER BY var1. And surprise! I get the following error message : "Only simple SELECT queries are allowed in VIEWS". Argh...

So. HELP!

Gad.

 

[nicsin]

Indeed the case. Now we come to the question: How are you going to use this view? The simplest answer to your question would be to ommit the

order by

clause from the

select

and use it when calling the view.

select * from view1 order by var1

 

[thegad]

For the order stuff, you right, but I have much more complex queries with plenty of LEFT JOIN. By removing the ORDER BY in these complex queries and creating them, I don't get any error message. So we can suppose that the others commands are accepted. Nevertheless, my calling software is not very happy because of the false ordering. Hence, i can't verify (nor in Access nor in my software as it is) if the VIEW is OK. Tomorrow (I'm living in Europe), I will modify my soft to put the order in, but it will surely cost run time.

Anyway, thanks to all, especially nicsin who was very helpfull.

 

[nicsin]

Good luck and have a safe journey!

PS I don't know where you are but make sure you bring some warm clothes...;-)