Sort SQL

[rebelscum0000]

Dear All,

I do not undestand why this code is not working, I Only want to Sort
Ascending
The Field MyKeyword from the Tbl MyKeywords_Tbl

--->My Code:

'Counts ALL Records FROM Tbl MyKeywords_Tbl
MyToReCo_Keywords_Tlb = DCount("*", "MyKeywords_Tbl")

MsgBox "My Total Records are: " & MyToReCo_Keywords_Tlb

'Order my Records From Tbl MyKeywords_Tbl
strSelectSQL = "SELECT MyKeywords_Tbl.MyKeyword " & _
"FROM MyKeywords_Tbl " & _
"ORDER BY MyKeywords_Tbl.MyKeyword;"


CurrentDb.Execute strSelectSQL, dbFailOnError

rst_Keyword.Close


Set rst_Keyword = Nothing
Set db_Keyword = Nothing


---->I Get a Run-time error 3065*
"Cannot execute a select query."

I Found out The execute method only works with Action Queries (Update,
Append and Make
Table)

So I added and changed to my code:

Dim db_Keyword As Database 'Current Dbs
Table
Dim rst_Keyword As DAO.Recordset 'DAO.Recordset
Table
Dim strSelectSQL As String

Set db_Keyword = CurrentDb
Set rst_Keyword = db_Keyword.OpenRecordset(strSelectSQL)

DoCmd.RunSQL strSelectSQL

I Get a Run-time error 2342

"A RunSQL action requires an argument consisting of an an SQL
statementet"

-----> I did change :

DoCmd.RunSQL strSelectSQL to DoCmd.OpenQuery strSelectSQL

and I get another

Run-time error 7874

Microsof acces can't find the object 'SELECT
MyKeywords_Tbl.MyKeyword FROM Mykeywords_tbl ORDER BY
MyKeywords_Tbl.Mykeyword;.'


->>>>Please help me what I am doing wrong???

->>>>>@Dim db_Keyword As Database" is wrong? it shoul be "Dim
db_Keyword As DAO.Database"?

->>>>>>Also what is the diference between CurrentDb.Execute
strSelectSQL, dbFailOnError &
DoCmd.RunSQL strSelectSQL?

--->This is my entire code, Maybe is something wrong:

Thanks in advance
Antonio Macias

Private Sub Keyword_AfterUpdate()

Dim db_Keyword As Database 'Current Dbs
Table
Dim rst_Keyword As DAO.Recordset 'DAO.Recordset
Table
Dim SQL3, sQL4, strSelectSQL As String 'Select Query Table
Dim Msg, Style, Title, Response, MySelection
Dim MyCuKeywordID As Variant 'My Current
Keyword ID
Dim MyReSeKeyword As Variant 'My Record
Search Keyword
Dim MyToReCo_Keywords_Tlb As Variant 'My Total
Records Keywords_Tbl


'Make sure Microsoft DAO 3.6 Library in included in the References

'Initialize Variables
Msg = "Are you sure " & "[ " & Keyword & " ]" & " Will be your
default search?"
msg1 = Keyword & " Is now your default search"
msg2 = "The Keyword: " & "[ " & Keyword & "]" & " Already Exists,
Please enter another Keyword"
Style = vbYesNoCancel + vbQuestion 'Define buttons.
Style1 = vbYesNo + vbInformation 'Define buttons.
Style2 = vbOKOnly + vbInformation 'Define buttons.
Title = "Setting Keyword" 'Define title.
Title1 = "Keyword Set" 'Define title.
Title2 = "Keyword Dulicated" 'Define title.



MyReSeKeyword = _
DLookup("[MyKeyword]", "MyKeywords_Tbl", "[MyKeyword] =
'" & Keyword & "'")

If IsNull(MyReSeKeyword) Then

Response = MsgBox(Msg, Style, Title)
If Response = 6 Then 'User chose Yes.
MySelection = "Yes" 'Perform some action.

Response1 = MsgBox(msg1, Style1, Title1)

If Response1 = 6 Then

'This SQL Statement UPDATE All the Field MyKeyword OF THE
'Tbl MainExclude_Tbl With a Control Source (Keyword)

'This is an Action querie (Update, Append, and Make Table)

'When is an UPDATE Query and the Control Source Comes from
a
'Form the sintaxis has to be '" & Contol Source &"' ended
with ; and closed
'With a quote.
SQL3 = _
"UPDATE MainExclude_Tbl SET MainExclude_Tbl.MyKeyword = '"
& Keyword & "'"

'Please read Write Conflict Form Subform.pdf
If Me.Dirty Then Me.Dirty = False

CurrentDb.Execute SQL3, dbFailOnError

'Searchs the First occurrence that contains the Control
Source (Keyword)
'in the Tbl (MainExclude_Tbl) and Returns its ID, to ensure
that
'the DLookup function returns a unique value:

MyCuKeywordID = _
DLookup("[ID]", "MainExclude_Tbl", "[MyKeyword] = '" &
Keyword & "'")

'This SQL Statement INSERT INTO the Tbl (MyKeywords_Tbl) the
Control Source
'(Keyword) FROM the Tbl MainExclude_Tbl, WHERE "ONLY" INSERT
FROM Tbl
'MainExclude_Tbl The Variable MyCuKeywordID:

sQL4 = _
"INSERT INTO MyKeywords_Tbl ( MyKeyword ) " & _
"SELECT MainExclude_Tbl.MyKeyword " & _
"FROM MainExclude_Tbl " & _
"WHERE (((MainExclude_Tbl.ID) = " & MyCuKeywordID & " ))"

CurrentDb.Execute sQL4, dbFailOnError

------------------------ My Problem Here
--------------------------------------------------

'Counts ALL Records FROM Tbl MyKeywords_Tbl
MyToReCo_Keywords_Tlb = DCount("*", "MyKeywords_Tbl")

MsgBox "My Total Records are: " & MyToReCo_Keywords_Tlb

'Order my Records From Tbl MyKeywords_Tbl
strSelectSQL = "SELECT MyKeywords_Tbl.MyKeyword " & _
"FROM MyKeywords_Tbl " & _
"ORDER BY MyKeywords_Tbl.MyKeyword;"


Set db_Keyword = CurrentDb
Set rst_Keyword = db_Keyword.OpenRecordset(strSelectSQL)


DoCmd.OpenQuery strSelectSQL



rst_Keyword.Close


Set rst_Keyword = Nothing
Set db_Keyword = Nothing

-------------------------------------------------------------------------------------------------------------------------------

ElseIf Response1 = 7 Then
Keyword = ""
MsgBox "User Cancel", vbInformation, "Canceled
default Search"
End If

ElseIf Response = 7 Then 'User chose No.
MySelection = "No" 'Perform some action.
Keyword = ""
ElseIf Response = 2 Then 'User chose Cancel.
MySelection = Cancel 'Perform some action.
Keyword = ""
MsgBox "User Cancel", vbInformation, "Canceled default
Search"
End If
Else
Response2 = MsgBox(msg2, Style2, Title2)
Keyword = ""
Keyword.SetFocus
End If


'3075 Please enter a valid Keyword



End Sub

 

[Jeff L]

What are you trying to do? If you are just trying to view the data,
you would be better off making and saving a query in the database
window. Then use the OpenQuery to run the query and see the data.
Open Query requires a pre-existing query.

Dear All,

I do not undestand why this code is not working, I Only want to Sort
Ascending
The Field MyKeyword from the Tbl MyKeywords_Tbl

--->My Code:

'Counts ALL Records FROM Tbl MyKeywords_Tbl
MyToReCo_Keywords_Tlb = DCount("*", "MyKeywords_Tbl")

MsgBox "My Total Records are: " & MyToReCo_Keywords_Tlb

'Order my Records From Tbl MyKeywords_Tbl
strSelectSQL = "SELECT MyKeywords_Tbl.MyKeyword " & _
"FROM MyKeywords_Tbl " & _
"ORDER BY MyKeywords_Tbl.MyKeyword;"


CurrentDb.Execute strSelectSQL, dbFailOnError

rst_Keyword.Close


Set rst_Keyword = Nothing
Set db_Keyword = Nothing


---->I Get a Run-time error 3065*
"Cannot execute a select query."

I Found out The execute method only works with Action Queries (Update,
Append and Make
Table)

So I added and changed to my code:

Dim db_Keyword As Database 'Current Dbs
Table
Dim rst_Keyword As DAO.Recordset 'DAO.Recordset
Table
Dim strSelectSQL As String

Set db_Keyword = CurrentDb
Set rst_Keyword = db_Keyword.OpenRecordset(strSelectSQL)

DoCmd.RunSQL strSelectSQL

I Get a Run-time error 2342

"A RunSQL action requires an argument consisting of an an SQL
statementet"

-----> I did change :

DoCmd.RunSQL strSelectSQL to DoCmd.OpenQuery strSelectSQL

and I get another

Run-time error 7874

Microsof acces can't find the object 'SELECT
MyKeywords_Tbl.MyKeyword FROM Mykeywords_tbl ORDER BY
MyKeywords_Tbl.Mykeyword;.'


->>>>Please help me what I am doing wrong???

->>>>>@Dim db_Keyword As Database" is wrong? it shoul be "Dim
db_Keyword As DAO.Database"?

->>>>>>Also what is the diference between CurrentDb.Execute
strSelectSQL, dbFailOnError &
DoCmd.RunSQL strSelectSQL?

--->This is my entire code, Maybe is something wrong:

Thanks in advance
Antonio Macias

Private Sub Keyword_AfterUpdate()

Dim db_Keyword As Database 'Current Dbs
Table
Dim rst_Keyword As DAO.Recordset 'DAO.Recordset
Table
Dim SQL3, sQL4, strSelectSQL As String 'Select Query Table
Dim Msg, Style, Title, Response, MySelection
Dim MyCuKeywordID As Variant 'My Current
Keyword ID
Dim MyReSeKeyword As Variant 'My Record
Search Keyword
Dim MyToReCo_Keywords_Tlb As Variant 'My Total
Records Keywords_Tbl


'Make sure Microsoft DAO 3.6 Library in included in the References

'Initialize Variables
Msg = "Are you sure " & "[ " & Keyword & " ]" & " Will be your
default search?"
msg1 = Keyword & " Is now your default search"
msg2 = "The Keyword: " & "[ " & Keyword & "]" & " Already Exists,
Please enter another Keyword"
Style = vbYesNoCancel + vbQuestion 'Define buttons.
Style1 = vbYesNo + vbInformation 'Define buttons.
Style2 = vbOKOnly + vbInformation 'Define buttons.
Title = "Setting Keyword" 'Define title.
Title1 = "Keyword Set" 'Define title.
Title2 = "Keyword Dulicated" 'Define title.



MyReSeKeyword = _
DLookup("[MyKeyword]", "MyKeywords_Tbl", "[MyKeyword] =
'" & Keyword & "'")

If IsNull(MyReSeKeyword) Then

Response = MsgBox(Msg, Style, Title)
If Response = 6 Then 'User chose Yes.
MySelection = "Yes" 'Perform some action.

Response1 = MsgBox(msg1, Style1, Title1)

If Response1 = 6 Then

'This SQL Statement UPDATE All the Field MyKeyword OF THE
'Tbl MainExclude_Tbl With a Control Source (Keyword)

'This is an Action querie (Update, Append, and Make Table)

'When is an UPDATE Query and the Control Source Comes from
a
'Form the sintaxis has to be '" & Contol Source &"' ended
with ; and closed
'With a quote.
SQL3 = _
"UPDATE MainExclude_Tbl SET MainExclude_Tbl.MyKeyword = '"
& Keyword & "'"

'Please read Write Conflict Form Subform.pdf
If Me.Dirty Then Me.Dirty = False

CurrentDb.Execute SQL3, dbFailOnError

'Searchs the First occurrence that contains the Control
Source (Keyword)
'in the Tbl (MainExclude_Tbl) and Returns its ID, to ensure
that
'the DLookup function returns a unique value:

MyCuKeywordID = _
DLookup("[ID]", "MainExclude_Tbl", "[MyKeyword] = '" &
Keyword & "'")

'This SQL Statement INSERT INTO the Tbl (MyKeywords_Tbl) the
Control Source
'(Keyword) FROM the Tbl MainExclude_Tbl, WHERE "ONLY" INSERT
FROM Tbl
'MainExclude_Tbl The Variable MyCuKeywordID:

sQL4 = _
"INSERT INTO MyKeywords_Tbl ( MyKeyword ) " & _
"SELECT MainExclude_Tbl.MyKeyword " & _
"FROM MainExclude_Tbl " & _
"WHERE (((MainExclude_Tbl.ID) = " & MyCuKeywordID & " ))"

CurrentDb.Execute sQL4, dbFailOnError

------------------------ My Problem Here
--------------------------------------------------

'Counts ALL Records FROM Tbl MyKeywords_Tbl
MyToReCo_Keywords_Tlb = DCount("*", "MyKeywords_Tbl")

MsgBox "My Total Records are: " & MyToReCo_Keywords_Tlb

'Order my Records From Tbl MyKeywords_Tbl
strSelectSQL = "SELECT MyKeywords_Tbl.MyKeyword " & _
"FROM MyKeywords_Tbl " & _
"ORDER BY MyKeywords_Tbl.MyKeyword;"


Set db_Keyword = CurrentDb
Set rst_Keyword = db_Keyword.OpenRecordset(strSelectSQL)


DoCmd.OpenQuery strSelectSQL



rst_Keyword.Close


Set rst_Keyword = Nothing
Set db_Keyword = Nothing

-------------------------------------------------------------------------------------------------------------------------------

ElseIf Response1 = 7 Then
Keyword = ""
MsgBox "User Cancel", vbInformation, "Canceled
default Search"
End If

ElseIf Response = 7 Then 'User chose No.
MySelection = "No" 'Perform some action.
Keyword = ""
ElseIf Response = 2 Then 'User chose Cancel.
MySelection = Cancel 'Perform some action.
Keyword = ""
MsgBox "User Cancel", vbInformation, "Canceled default
Search"
End If
Else
Response2 = MsgBox(msg2, Style2, Title2)
Keyword = ""
Keyword.SetFocus
End If


'3075 Please enter a valid Keyword



End Sub

 

[John Vinson]

Dear All,

I do not undestand why this code is not working, I Only want to Sort
Ascending
The Field MyKeyword from the Tbl MyKeywords_Tbl

Are you trying to sort the TABLE?

If so, don't bother. Tables *have no order* in any usable sense.
They're unordered "bags" of records.

If you want to see records in a particular order, you must - no
options - use a Query based on the table, with an Order By clause in
the query.

Such a query can be used as the basis for a Form, a Report, an Export,
pretty much anything you can do with a table.

John W. Vinson[MVP]

 

[rebelscum0000]

What are you trying to do? If you are just trying to view the data,
you would be better off making and saving a query in the database
window. Then use the OpenQuery to run the query and see the data.
Open Query requires a pre-existing query.

Dear Jeff,

I am not trying to view the data, ,
I have a Table called MyKeywords.Tbl, Whit only one Field Name Called
MyKeyword which is growing each time, when the code INSERTs INTO a new
register (SQL5 Statement) ,
They way I enter the Keywords is not in order, I would like the
strSelectSQL Statement
order them in an Ascending way

In Other words I enter the keywords for example as follows:
Norton
Abacus
WinZip

I need the strSelectSQL Statement order them as follows:
Abacus
Norton
WinZip

Without Maaking and saving a query in my database, is this possible?

Thank in Advance

Regards,
Antonio Macias

 

[punjab_tom]

you shouldn't be scared of 'making queries and saving them'

queries are your friends

-Tom

 

[rebelscum0000]

Thanks to all for your help

but I am still lost

I did some modifications and now I will use like Tom said Queries as My
Friends

I have created a new Query called OrderMyKeywords_Query

SELECT MyKeywords_Tbl.ID, MyKeywords_Tbl.MyKeyword
FROM MyKeywords_Tbl
ORDER BY MyKeywords_Tbl.MyKeyword;

My data is in an Ascending way, No problem here,
Now How to Move back my new data From the Query To the Table
MyKeywords.Tbl Without create dups?

Thanks in Advance
Regards,
Antonio Macias

 

[John Vinson]

My data is in an Ascending way, No problem here,
Now How to Move back my new data From the Query To the Table
MyKeywords.Tbl Without create dups?

You don't.

Tables are used to STORE DATA.

Queries are used to SELECT, SORT, AND ARRANGE DATA.

You should not *care* what order the records are in the Table. The
query didn't "move data" out of the table, and you don't need to "move
data" back.

The query is just a way of viewing the data, in a particular sort
order in this case.

Could you explain *why* you feel that you need to "sort the table" or
"move the data back"?????

John W. Vinson[MVP]

 

[rebelscum0000]

Dear John,
Just because I had the no sense idea that sorting a table, having all
the records in order in all my tables, my database will become more
organized, and functional

Thank you very much for help me to understand my error

Regards,
Antonio Macias

 

[John Vinson]

Dear John,
Just because I had the no sense idea that sorting a table, having all
the records in order in all my tables, my database will become more
organized, and functional

Thank you very much for help me to understand my error

Regards,
Antonio Macias

It's hard to grasp until you get used to it! (And apologies for
"yelling" at you).

What will make your database more organized and functional is to have
all of the tables properly normalized, and to apply proper indexes to
the fields in the table. As a rough rule, any field used to sort or
search a table should have an Index; if the field value should occur
once and once only in the table, it should be a Unique Index.

John W. Vinson[MVP]

 

[Van T. Dinh]

See my comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

Dear All,

I do not undestand why this code is not working, I Only want to Sort
Ascending
The Field MyKeyword from the Tbl MyKeywords_Tbl

--->My Code:

'Counts ALL Records FROM Tbl MyKeywords_Tbl
MyToReCo_Keywords_Tlb = DCount("*", "MyKeywords_Tbl")

MsgBox "My Total Records are: " & MyToReCo_Keywords_Tlb

'Order my Records From Tbl MyKeywords_Tbl
strSelectSQL = "SELECT MyKeywords_Tbl.MyKeyword " & _
"FROM MyKeywords_Tbl " & _
"ORDER BY MyKeywords_Tbl.MyKeyword;"


CurrentDb.Execute strSelectSQL, dbFailOnError

rst_Keyword.Close


Set rst_Keyword = Nothing
Set db_Keyword = Nothing


---->I Get a Run-time error 3065*
"Cannot execute a select query."

I Found out The execute method only works with Action Queries (Update,
Append and Make
Table)

[VTD] Well-done ... The best way to learn is to do your own investigations
....

So I added and changed to my code:

Dim db_Keyword As Database 'Current Dbs
Table
Dim rst_Keyword As DAO.Recordset 'DAO.Recordset
Table
Dim strSelectSQL As String

Set db_Keyword = CurrentDb
Set rst_Keyword = db_Keyword.OpenRecordset(strSelectSQL)

DoCmd.RunSQL strSelectSQL

I Get a Run-time error 2342

"A RunSQL action requires an argument consisting of an an SQL
statementet"

[VTD] Yep. RunSQL (similar to Execute) can only run "Action" SQL, e.g.
Insert, Update, Delete and not a Select SQL.

-----> I did change :

DoCmd.RunSQL strSelectSQL to DoCmd.OpenQuery strSelectSQL

and I get another

Run-time error 7874

Microsof acces can't find the object 'SELECT
MyKeywords_Tbl.MyKeyword FROM Mykeywords_tbl ORDER BY
MyKeywords_Tbl.Mykeyword;.'

[VTD] The OpenQuery method expects a save Query Name and not an SQL String.

->>>>Please help me what I am doing wrong???

->>>>>@Dim db_Keyword As Database" is wrong? it shoul be "Dim
db_Keyword As DAO.Database"?

[VTD] Dim db_Keyword As Database

is fine. Only the DAO Library has the Database object so there is no
ambiguity here (unlike the Recordset object which exists in DAO Library and
ADO Library - DAO Recordsets and ADO Recordsets are not compatible and hence
the full reference like

Dim rst As DAO.Recordset

is preferred than simply:

Dim rst As Recordset

). Wether the full reference is actually required or not depends on the
References in your database and your code.

->>>>>>Also what is the diference between CurrentDb.Execute
strSelectSQL, dbFailOnError &
DoCmd.RunSQL strSelectSQL?

[VTD] In the context of the code you posted above, Execute is the DAO's
method and RunSQL is the Access' method. Basically, both Execute and RunSQL
performs the same function but they come from different components of what
we commonly know as "Access" software. Most programmers prefer the Execute
with dbFailOnError as this method provides trappable errors which can be
handled by (error-trapping) code. Note also that RunSQL is only available
in Access (Access VBA) while the DAO Library can be added to other
applications (e.g. VB 6, Excel ...) to access / use a JET Back-End so
Execute is available in "more" applications.

<the rest snipped>

 

[Jamie Collins]

Are you trying to sort the TABLE?

If so, don't bother. Tables *have no order* in any usable sense.
They're unordered "bags" of records.

If you want to see records in a particular order, you must - no
options - use a Query based on the table, with an Order By clause in
the query.

There's a few contradictions here.

A *logical* table is a set. Sets have no order by definition.

A *physical* table has an order, as determined by the physical storage.
Computers are notoriously bad when it comes to randomness and you will
get the same order each time, unless something changes in the interval.
The sort order on disk, and therefore the 'default' order when no order
is explicitly requested, can be specified by adding a PRIMARY KEY to
the table and compacting the file.

I too choose to disregard such physical characteristic of the mdb file
format and stick to the logical considerations. So I agree, tables have
no order.

Now, if by 'Query' you mean a stored Query object based on a
SELECT..FROM query, you would be talking about a VIEW or virtual table.
A virtual table should behave as a materialized table, therefore
logically a virtual table also has no order. Yet you seem to be
recommending putting an ORDER BY clause in a stored query object, being
a physical object.

In summary, you say a physical table should be considered logically
with no order and but that a virtual table should be physically stored
in such a way that it will have the effect of an inherent order!

Try something like this in ANSI-92 Query Mode (e.g. via an ADO
connection) on a Jet 4.0 database (e.g. Northwind):

CREATE VIEW DropView
AS
SELECT CompanyName
FROM Customers
ORDER BY CompanyName;

You get an error, 'Only simple SELECT queries are allowed in VIEWS,'
which sounds strange considering the trivial nature of the query but
what it is trying to tell you is that a VIEW (a virtual table) cannot
have an order. OK, you can do some 'physical' manipulation in the
Access UI to force a sort order but is this good practice?

Jamie.

--

 

[Rick Brandt]

There's a few contradictions here.

A *logical* table is a set. Sets have no order by definition.

A *physical* table has an order, as determined by the physical
storage. Computers are notoriously bad when it comes to randomness
and you will get the same order each time, unless something changes
in the interval. The sort order on disk, and therefore the 'default'
order when no order is explicitly requested, can be specified by
adding a PRIMARY KEY to the table and compacting the file.

I too choose to disregard such physical characteristic of the mdb file
format and stick to the logical considerations. So I agree, tables
have no order.

Now, if by 'Query' you mean a stored Query object based on a
SELECT..FROM query, you would be talking about a VIEW or virtual
table. A virtual table should behave as a materialized table,
therefore logically a virtual table also has no order. Yet you seem
to be recommending putting an ORDER BY clause in a stored query
object, being a physical object.

In summary, you say a physical table should be considered logically
with no order and but that a virtual table should be physically stored
in such a way that it will have the effect of an inherent order!

Try something like this in ANSI-92 Query Mode (e.g. via an ADO
connection) on a Jet 4.0 database (e.g. Northwind):

CREATE VIEW DropView
AS
SELECT CompanyName
FROM Customers
ORDER BY CompanyName;

You get an error, 'Only simple SELECT queries are allowed in VIEWS,'
which sounds strange considering the trivial nature of the query but
what it is trying to tell you is that a VIEW (a virtual table) cannot
have an order. OK, you can do some 'physical' manipulation in the
Access UI to force a sort order but is this good practice?

Jamie.

A stored Access query is *similar* to a VIEW, but that does not make it the
*same* as a VIEW. If the query is to be used in a process where a specific
order is required (or desired) then an order by clause should be put in the
query.

 

[Douglas J. Steele]

Now, if by 'Query' you mean a stored Query object based on a
SELECT..FROM query, you would be talking about a VIEW or virtual table.
A virtual table should behave as a materialized table, therefore
logically a virtual table also has no order. Yet you seem to be
recommending putting an ORDER BY clause in a stored query object, being
a physical object.

What's the contradiction? Just because you equate queries to views doesn't
mean they are the same.

Of course you can include ORDER BY clauses in stored queries in Access.

 

[Jamie Collins]

A stored Access query is *similar* to a VIEW, but that does not make it the
*same* as a VIEW. If the query is to be used in a process where a specific
order is required (or desired) then an order by clause should be put in the
query.

What about a base table? Is an Access base table the same as or merely
*similar* to a base table as described in the SQL standard?

I think an Access base table has a physical order by I prefer to think
logically and consider an Access base table the same as a standard SQL
table.

Jamie.

--

 

[Rick Brandt]

What about a base table? Is an Access base table the same as or merely
*similar* to a base table as described in the SQL standard?

I think an Access base table has a physical order by I prefer to think
logically and consider an Access base table the same as a standard SQL
table.

Jamie.

A saved query is not a table. Nor is it a View. It is what it is and
appropriate use is dictated by what it actually is, not what you would like
to treat it as.

 

[Jamie Collins]

What's the contradiction? Just because you equate queries to views doesn't
mean they are the same.

Terminology could be an issue here. Some definitions follow:

· "A table is either a base table, a viewed table, or a derived
table." (SQL-92 standard).

· A 'base table' is a persistent object (none of the other
definitions in the SQL-92 standard apply to Access/Jet).

· A 'view' is a virtual table defined by a query expression, as
described in the SQL-92 standard (which calls it a 'viewed table').

· An 'Access table' is a Table object as implemented in Access/Jet
SQL.

· A 'cursor' is "a data structure that describes the results returned
from a SQL SELECT statement."
(http://www.orafaq.com/glossary/faqglosc.htm), as distinct from a
'caret', being that blinking character indicating the insertion point
etc.

· The Information Schema described in the SQL-92 standard and
describes the persistent objects in the schema (for Access/Jet's
implementation, see: ADO 2.8 API Reference: SchemaEnum:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstschemaenum.asp).

A 'table' is a set. A set has no concept of order. Therefore, a 'base
table' has no inherent order and a 'virtual table' has no inherent
order. Consider the SQL term INSERT which adds a rows but with no
implication of their position in the set.

An 'Access table' has a physical order, determined by a number of
factors (PRIMARY KEY designation, date/time order of insertion of rows,
when the file was compacted, etc) and, provided none of those factors
change, the inherent order will be the same. Consider the Access term
'Append query', which implies the rows will be added at the every end,
which is exactly what happens with an Access table.

Of course you can include ORDER BY clauses in stored queries in Access.

A Jet SQL VIEW is an Access Query object, a fact that can be discovered
by executing a CREATE VIEW statement then opening the mdb in the Access
UI and examining the Query. So an Access Query object can be a 'virtual
table'.

A Jet SQL VIEW cannot have an ORDER BY clause; attempting to execute a
CREATE VIEW statement using a query expression that has an ORDER BY
clause results in an error (**see below).

I recognise that an Access Query object is not always a VIEW or
'virtual table'. Sometimes it contains a SQL DML statement (INSERT,
UPDATE, DELETE) as distinct from a SQL DML query (small q), being a
SELECT expression.

[Confusingly, the Access community refers to SQL DML statements as
'queries', hence terms such as 'delete query' are oxymoron to the wider
SQL community.]

A Query object can even contain SQL DDL and/or SQL DCL expressions e.g.
the following is valid syntax (yes, three CREATE PROCs in one statement
and yes, the result is worthless):

CREATE PROC Proc1
AS
CREATE PROC Proc1
AS
CREATE PROC Proc1
AS
BEGIN TRANSACTION;

So an Access Query objects can contain all kinds of funky things,
including a SELECT query with an ORDER BY clause.

My point: it's fine by me if you want to consider the physical
implementation aspects of objects such as 'table' and 'virtual' table,
in which case an Access table has an inherent order and an Access
Query-As-Virtual-Table (lacking a term here) may or may not have an
explicit order. I'd rather they were considered in pure SQL terms and
say that neither have inherent ordering (but then I am a 'purist'
myself <g>). What I would view as contradictory is picking and choosing
i.e. saying a 'table' has no order but a 'virtual table' can have an
order.

** from above:

Actually, that's not quite true. You can do this:

CREATE PROC DropView
AS
SELECT CompanyName
FROM Customers
ORDER BY CompanyName;

which will create an Access query object and expose it to the
Information Schema as a VIEW

As is often said round here, that something *can* be done does not mean
it *should* be done.

Jamie.

--

 

[Jamie Collins]

Of course you can include ORDER BY clauses in stored queries in Access.

He's something by Joe Celko from the Usenet archive on the topic of
ORDER BY in VIEWs. It's quite a large thread so I'll repost the
relevant here (with minor text corrections):

Is there is a chance in hell of getting [ORDER BY] added to the definition of a view? If so, where do I sign up for the campaign? <<

You can sign up for the ANSI Standards committee and write a proposal.
But you will need to defend it. And since we have looked at this issue

before, you will have to work at the level of a PhD to get around the
objections and proofs of contradiction that other people found in it.

Let's throw out the foundations of the Relational Model and assume
that we can write crap like

CREATE VIEW ...
AS
SELECT ..
FROM ..
WHERE ..
[GROUP BY ..]
[HAVING BY ..]
[ORDER BY ..];

If I have ordering on VIEWs, I must have ordering on TABLEs. Or you
have to change the RM to have both ordered and unordered tables with
all the rules for moving data between them?

A. What is the meaning of sorts by things not in the select clause?

Gee, I guess we need to carry extra data on each row for the sort, even

if it is not used.

SELECT a, b
FROM Foobar
ORDER BY x;

Even worse, what is the output of this? Remember that a GROUP BY
destroys the original table to produce a grouped table, so x does not
even exist anymore.

SELECT a, SUM(b)
FROM Foobar
GROUP BY a
ORDER BY x;

B. What is the meaning of sorts on nested subqueries? Which sort has
priority?

SELECT a, c
FROM (SELECT x, y
FROM Barfoo
ORDER BY x) AS XX(a, c)
ORDER BY c, a;

C. What is the meaning of sorts on UNION, INTERSECT and EXCEPT?

Remember that (A UNION B) = (B UNION A), or do you want to throw out
all of Set Theory along with the Relational Model?

SELECT a, b
FROM Foobar
ORDER BY a
UNION ALL
SELECT x, y
FROM Barfoo
ORDER BY x;

What if I add an ORDER BY to the whole thing?

SELECT r, s
FROM (SELECT a, b
FROM Foobar
ORDER BY a
UNION ALL
SELECT x, y
FROM Barfoo
ORDER BY y)
AS XX(r, s)
ORDER BY r, s;

D. What do UPDATE, INSERT and DELETE with ordered table mean? For
example:

INSERT INTO Foobar -- asc ordering
SELECT .. FROM Barfoo; -- desc ordering

E. Another problem here is that there are two kinds of sorts, stable
and non-stable. Now the Standard has to pick an implementation method.
We hate doing that.

Non-stable sorts are much faster than stable (aka "sequence
preserving"); look up QuickSort versus Bubble sort. With the current
cursor model, I have one and only one sort which can be done any way
the optimizer thinks will work best.

If you want non-stable sorting, then only the last sort applied to the
result table matters. But which one is it?? Well, now we need to
specify the absolute order of execution - no optimizer changes,
please.

I also cannot parallelize my code because it would not have an
ordering.

Why is this ordering worth destroying any possible way of having
parallelism or optimization? One of the best things about a VIEW is
that the optimizer can "cut & paste" the VIEW into a query and
fetch the rows in whatever order is required. Your model has to
materialize and then order the table, even if it screws up
optimization.

The advantage of a single ORDER BY in the cursor is that it can be
factored into the optimizer by looking at indexes, or saved for the
middle tier.

[unquote]

Source:
http://groups.google.com/group/comp...b993a592dd/a72022d50660b169?#a72022d50660b169

Jamie.

--

 

[rebelscum0000]

Dear Gurus,

I Started this Topic asking a no sense idea that I had,
well I have another for you

The true is I am learning, I am newbie, but with all these replies
I think I will become very soon a Guru like you

My new topic is MAKE A NEW TABLE SQL
AT
http://groups.google.com.mx/group/m...f322b/dc4c0446269fe5a8?hl=en#dc4c0446269fe5a8

Could you please help me again?

Thanks in advance

Regards,
Antonio Macias

 

[James A. Fortune]

What's the contradiction? Just because you equate queries to views doesn't
mean they are the same.

Terminology could be an issue here. Some definitions follow:

· "A table is either a base table, a viewed table, or a derived
table." (SQL-92 standard).

· A 'base table' is a persistent object (none of the other
definitions in the SQL-92 standard apply to Access/Jet).

· A 'view' is a virtual table defined by a query expression, as
described in the SQL-92 standard (which calls it a 'viewed table').

· An 'Access table' is a Table object as implemented in Access/Jet
SQL.

· A 'cursor' is "a data structure that describes the results returned
from a SQL SELECT statement."
(http://www.orafaq.com/glossary/faqglosc.htm), as distinct from a
'caret', being that blinking character indicating the insertion point
etc.

· The Information Schema described in the SQL-92 standard and
describes the persistent objects in the schema (for Access/Jet's
implementation, see: ADO 2.8 API Reference: SchemaEnum:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstschemaenum.asp).

A 'table' is a set. A set has no concept of order. Therefore, a 'base
table' has no inherent order and a 'virtual table' has no inherent
order. Consider the SQL term INSERT which adds a rows but with no
implication of their position in the set.

An 'Access table' has a physical order, determined by a number of
factors (PRIMARY KEY designation, date/time order of insertion of rows,
when the file was compacted, etc) and, provided none of those factors
change, the inherent order will be the same. Consider the Access term
'Append query', which implies the rows will be added at the every end,
which is exactly what happens with an Access table.

Of course you can include ORDER BY clauses in stored queries in Access.

A Jet SQL VIEW is an Access Query object, a fact that can be discovered
by executing a CREATE VIEW statement then opening the mdb in the Access
UI and examining the Query. So an Access Query object can be a 'virtual
table'.

A Jet SQL VIEW cannot have an ORDER BY clause; attempting to execute a
CREATE VIEW statement using a query expression that has an ORDER BY
clause results in an error (**see below).

I recognise that an Access Query object is not always a VIEW or
'virtual table'. Sometimes it contains a SQL DML statement (INSERT,
UPDATE, DELETE) as distinct from a SQL DML query (small q), being a
SELECT expression.

[Confusingly, the Access community refers to SQL DML statements as
'queries', hence terms such as 'delete query' are oxymoron to the wider
SQL community.]

A Query object can even contain SQL DDL and/or SQL DCL expressions e.g.
the following is valid syntax (yes, three CREATE PROCs in one statement
and yes, the result is worthless):

CREATE PROC Proc1
AS
CREATE PROC Proc1
AS
CREATE PROC Proc1
AS
BEGIN TRANSACTION;

So an Access Query objects can contain all kinds of funky things,
including a SELECT query with an ORDER BY clause.

My point: it's fine by me if you want to consider the physical
implementation aspects of objects such as 'table' and 'virtual' table,
in which case an Access table has an inherent order and an Access
Query-As-Virtual-Table (lacking a term here) may or may not have an
explicit order. I'd rather they were considered in pure SQL terms and
say that neither have inherent ordering (but then I am a 'purist'
myself <g>). What I would view as contradictory is picking and choosing
i.e. saying a 'table' has no order but a 'virtual table' can have an
order.

** from above:

Actually, that's not quite true. You can do this:

CREATE PROC DropView
AS
SELECT CompanyName
FROM Customers
ORDER BY CompanyName;

which will create an Access query object and expose it to the
Information Schema as a VIEW

As is often said round here, that something *can* be done does not mean
it *should* be done.

Jamie.

Jamie,

I try never to rely on either the default ordering or the physical
ordering of an Access table. It seems SQL purists see a view in the
same fashion. I didn't know that. You say that both tables and views
should be considered unordered until you get to a separate query that
contains an "ORDER BY" clause. The Celko thread you cite has posters
giving reasons of parallelism and efficiency as well as implementational
problems that orders in views bring. This means that a view is purely a
virtual table. Access programmers often use stacked queries. If a
query without order is used as the input of another query, how does the
input query differ from a view? If I can get all the data I need from a
view, should I apply a query at the end just to do the "ORDER BY?" I
may have missed your point due to my Access perspective of things. If
so, please clarify, supplying new terminology if necessary .

James A. Fortune
(e-mail address removed)

 

[Jamie Collins]

If a
query without order is used as the input of another query, how does the
input query differ from a view? If I can get all the data I need from a
view, should I apply a query at the end just to do the "ORDER BY?" I
may have missed your point due to my Access perspective of things.

Try this approach.

If you can use a Query object in the FROM clause of a SELECT query then
it's a table. If a Query object with an ORDER BY clause can be used in
the FROM clause of a SELECT query then we can say that Access/Jet
supports tables with an explicit order.

The strangest thing about the Jet engine is the total absence of a
specification that would tell us how a table with an explicit order is
supposed to behave. If the feature is in the SQL standard then we have
an idea. Even syntax that violates the standard but is well documented
and supported in the product can have unpredictable** results e.g.
UPDATE..JOIN syntax using an OUTER JOIN that doesn't use a key (** in
this example the results is predictable, because Access base tables
have a physical order, but not documented nor guaranteed).

Essentially, all we can do is a) experiment to try and guess the
feature designer's intention (which can be fun) or b) conclude that
such violations of standard SQL, relational theory and set theory are
probably worth avoiding (which can be a good business decision).

If you take the IMO dubious decision to work with tables that have
explicit orders, why then discriminate between virtual tables and base
tables?

Jamie.

--