Order by Alias Field Name?

[Guest]

I have a query that poulates a field with the count of child records from
another table. The query works perfectly, but, I can't order it by the count
of the child records.

The query Looks basically like this and works great!

SELECT T1.Name, (SELECT COUNT(table2.tbl1$id) as childRecords from table2
where table2.tbl1$id = T1.ID) as NumberOfChildRecords
FROM Table1 as T1;

Now I need to order it. I've tried:

Order By NumberOfChildRecords
(that results in a prompt for a paramater????)
And
Order By (SELECT COUNT(table2.tbl1$id) as childRecords from table2 where
table2.tbl1$id = T1.ID)
(that results in a syntax error?????)

What do I do here? I'm stumped.

 

[Ken Snell \(MVP\)]

It'll be slower, but you could use a DCount function in place of the
subquery:

SELECT T1.Name, (DCount("tbl1$id", "table2", "tbl1$id = " & T1.ID))
as NumberOfChildRecords
FROM Table1 as T1
ORDER BY (DCount("tbl1$id", "table2", "tbl1$id = " & T1.ID));


Or you could save the subquery as a separate query, and then join it to the
Table1 table in a query; then you could sort on the field as desired.

 

[Guest]

Thanks Ken.

Is there no way to refer to the Alias? I kind of need to be able to do this

 

[Duane Hookom]

You can try:
SELECT T1.Name, (SELECT COUNT(table2.tbl1$id) as childRecords from table2
where table2.tbl1$id = T1.ID) as NumberOfChildRecords
FROM Table1 as T1
ORDER BY 2;

 

[Guest]

Thanks for the response Duane,
Tried that. Didn't work. It didn't throw and error, it just didn't sort it.

 

[Ken Snell \(MVP\)]

I do not know of a way to use the Alias the way you wish in an ORDER BY
clause, sorry.

I did a search via Google, and did find one other alternative (posted by Van
T. Dinh, ACCESS MVP). You can create a new query that uses your current
query as the sole table. Then do the ORDER BY (using the "alias" field) in
the new query. It's a variation on the idea that I'd suggested (two
queries), but it allows you to keep the current query as it's structured,
whereas my suggestion would require a rewrite of the current query.

 

[Guest]

Ok, thanks. That is all very helpful.

Followup.
I took your advice and worked on using my original query and adding it to a
second query and sorting there. It works great..... exept....

The parent table is big.... 106K records. I have three sub queries setup
as fields working to do the same thing... get child record counts for every
parent.

If I request all records, it bombs around 83k records every time. If I
select top 80000 or less it works. What is the limit for Access?

Thanks,
B

 

[Ken Snell \(MVP\)]

There is not a "specific" limit for Jet/ACCESS to my knowledge, but it will
get slower as the number of records increase. The fact that you're sorting
on a non-indexed field will slow it down more.

You may need to look for ways to get subsets of the data, and then combine
them back.

Or, you could export the data from the first query into a temporary table,
with an index on the field that is to be sorted in that table, and then do
the query from the table. You'd need to run a small VBA procedure to do
this.

 

[Michel Walsh]

Hi,


Indeed, Jet does not allow an ORDER BY on alias ... EXCEPT on UNION query...

SELECT whatever_with_alias FROM somewhere
UNION ALL
SELECT samenumber_of_columns FROM somewhere WHERE true=false
ORDER BY someAlias_in_the_first_SELECT


should work, and should not be terribly much slower than the non-union query
(well, the ORDER BY is not free in itself).

I doubt Microsoft would ever issue a hot fix about it, but you can ask
them... (you know where, on their home page...)

Hoping it may help,
Vanderghast, Access MVP