OrderBy Property for a Query?

[MSAccessRookie]

I am having a problem with locking in my database when I am adding new contacts, and have been trying to determine which database objects access the table tblPeople by using the tool ReplaceWiz 2003.

I have determined that there is a query named qryPeopleAlphabeticRW, (which is currently supposed to be the only query in the application that writes to the table tblPeople), has a property called OrderBy that I have not seen before.

I have seen a property like this in Forms and Reports, but never in a query. I have been unable to identify this property and remove it, since the OrderBy Property is not the correct order. It is OrderBy PersonID, while the query qryPeopleAlphabeticRW is ordered by the name of the person.

Has anyone else encountered this?

Thanks in advance.

 

[The_Doc_Man]

I've seen "order by" appearing in SQL aggregate queries.

You "group by" the field being aggregated. You "order by" fields not participating in the aggregate. E.g. a query that shows repeat customers grouped by customer number (so you can get a count) and ordered by city/state. (That's a contrived example but I think you will see the point.)

 

[raskew]

Hi -

ORDERBY is a typo. Put a space in there, ORDER BY, and your query should sort correctly.

Added: And if ReplaceWiz 2003, which I'm not familiar with, is creating that, there's a problem with it.


Bob

 

[raskew]

I've seen "order by" appearing in SQL aggregate queries

Doc: "Order By" is the statement you see in virtually every query SQL (at least those that are sorted), representing the field(s) to sort by.

There's nothing mystical about it.

Are you having a bad day?

Bob

 

[MSAccessRookie]

My apologies for not being clear. I am very aware of the "ORDER BY" keyword for SQL. This is not my problem. The problem is not in the content of my query, but rather in what appears to be one of the properties of the query instead.

Attached is a jpg file that shows the results of the ReplaceWiz run. Notice that the query "qryPeopleAlphabeticRW" has two references to the table tblPeople.

1. The first in in the body of the query itself. You can also see the "ORDER BY" statement in the code
2. The second is in the "Property Name" and is "OrderBy"

I am trying to determine how to identify the "Property Name" for the query, so that I can remove it, or at least change its value.

 

[raskew]

Your problem, IMHO, is ReplaceWiz -- Order By 1 (?!) what is that? Do you have a field named 1? Count the parentheses, left and right. Do they match?

Not sure what you believe ReplaceWiz is doing for you, but I don't think it's doing you any favors.

Bob

 

[MSAccessRookie]

Your problem, IMHO, is ReplaceWiz

• ReplaceWiz is a tool used to substitute character patterns within an Access Project. It is able to span all types of objects and perform the replace. HOWEVER, that was not why I used it. I used it on a copy of my database for the sole purpose of locating and displaying the objects that used the table tblPeople.

Order By 1 (?!) what is that? Do you have a field named 1?

• When any query is run against a database, the results are stored in a temporary table. This table can be addressed either by the names of the columns, or the number of the columns according to the Select statement. The query is sorted according to the value in the first column (a calculated value), and calculated values need to use the formula in the Order By Statement. Order By 1 gives me the first column in the Select statement, and since the temporary table already had the calculated value, there was no need to calculate it again.

Count the parentheses, left and right. Do they match?

• The Syntax (parentheses, etc.) is correct and all match properly.

Not sure what you believe ReplaceWiz is doing for you, but I don't think it's doing you any favors.

The ReplaceWiz Report indicateds where the character string tblPeople is found in the query qryPeopleAlphabeticRW. I was only noting the reference to the "Property" called "OrderBy", because its value is "tblPeople.Person_ID"

 

[raskew]

Obviously it's not working for you, or you wouldn't be posting here. Doesn't it strike you as a little strange that it came up with this OrderBy business?

Good Luck - Bob

 

[CraigDolphin]

If you want to access the query properties, open the query in design view, right-click on the design grid background (the grey area where the source tables are shown), and select properties from the menu.

 

[MSAccessRookie]

Obviously it's not working for you, or you wouldn't be posting here. Doesn't it strike you as a little strange that it came up with this OrderBy business?

Good Luck - Bob

I have researched the problem over the past several days and so far, I have determined that OrderBy is a legitimate property for Reports and Forms, but I have found no similar reference regarding queries. Since the ReplaceWiz tool found the reference, I will assume it is valid until I can prove that it is an error. I was hoping that others might be aware of a query property called "OrderBy".

I will continue to search for the answer.

 

[raskew]

I take it back. Apparently, in A2003, OrderBy exists. See here http://msdn.microsoft.com/en-us/library/aa173175(office.11).aspx

Bob

 

[CraigDolphin]

Well, if you do as I suggested you would see that the Order By (OrderBy when refered to in vba) property does indeed exist for queries...in A2K also

 

[MSAccessRookie]

If you want to access the query properties, open the query in design view, right-click on the design grid background (the grey area where the source tables are shown), and select properties from the menu.

Thanks for the advice. I was aware of this, but I had forgotten to try it. When I viewed the properties, there was one labeled "Order By" and it was Blank. Somehow, I do not think this is the same one that was found by ReplaceWiz.

 

[CraigDolphin]

From Access help:

Note When a new object is created, it inherits the RecordSource, Filter, OrderBy, and OrderByOn properties of the table or query it was created from. For forms and reports, inherited filters aren't automatically applied when an object is opened.

I'm guessing this property is inherited implictly as a consequence of the query using tblPeople as its source, and the first field in that table being peopleID.

To change that, you would need to modify your sql.

 

[MSAccessRookie]

From Access help:

Note When a new object is created, it inherits the RecordSource, Filter, OrderBy, and OrderByOn properties of the table or query it was created from. For forms and reports, inherited filters aren't automatically applied when an object is opened.

I'm guessing this property is inherited implictly as a consequence of the query using tblPeople as its source, and the first field in that table being peopleID.

To change that, you would need to modify your sql.

I read that before, looked at the table definition, and found no "OrderBy" property among the table properties. The SQL code for the query qryPeopleAlphabeticRW is displayed below. Any additional comments would be appreciated.

SELECT IIf(IsNull(L_Name),"",L_Name) & 
IIf(IsNull(L_Name),"",IIf(IsNull(F_Name),"",", ")) & 
IIf(IsNull(F_Name),"",F_Name) AS Expr1, 
Person_ID, 
DateAdded, 
Title, 
F_Name, 
M_Initial, 
L_Name, 
email, 
Notes, 
IIf(IsNull(Title),"",Title & " ") & 
IIf(IsNull(F_Name),"",F_Name & " ") & 
L_Name AS FormalName, 
IIf(Not (IsNull(F_Name)),F_Name & " ",Title & " ") & 
L_Name AS SemiFormalName, 
CommonName, 
SSMA_TimeStamp
FROM tblPeople
ORDER BY 1;

 

[CraigDolphin]

Let me restate.

FROM tblPeople
ORDER BY 1;

Ok, that's the relevant sql of your query. This IMPLICITLY tells Access that the OrderBy property for the query depends on field 1 of tblPeople. I think it's just an unstated assumption on the part of access. Your diagnostic program is then EXPLICTILY telling you what Access is assuming to be the case.

Whatever problem you have with your query, I don't think that this is the cause.

What would be interesting to try is to define the OrderBy property explictly in the properties dialogue, and see if your report changes to match the new value. That would at least confirm that you're looking at the right property. I really doubt that there are two different OrderBy properties for queries though.

 

[Brianwarnock]

ORDER BY 1;

I think that is the first field of the query

Brian

 

[MSAccessRookie]

ORDER BY 1;

I think that is the first field of the query

Brian

I cannot entirely dispute what CraigDolphin said, but both Oracle and SQL Server interpret the command as "Use the value from the first column returned by the query". It has always been my belief that Access does the same thing, but I am not POSITIVE what Access will do with that command.

 

[Brianwarnock]

Testing on a simple query and table confirms that it is the first field in the select statement, if it wasn't related to the Select statement you would be able to sort on non selected fields and not on calculated fields , and we have not begun to discuss multi table queries.

I wonder if your software is refering to an inherited Order By but I can only guess.

Brian

 

[MSAccessRookie]

Testing on a simple query and table confirms that it is the first field in the select statement, if it wasn't related to the Select statement you would be able to sort on non selected fields and not on calculated fields , and we have not begun to discuss multi table queries.

I wonder if your software is refering to an inherited Order By but I can only guess.

Brian

I totally agree with your observation, and have been looking for an inherited OrderBy value all day, but have yet to find one. The table tblPeople in the FE has no such property. The table tblPeople on the SQL Server has no such property. I am not sure where else I can look.