sql in MS Access and syntax critique 1

[netbumbler]

Could anyone critique this syntax for me?

SELECT dpch.pname, dpch.pnum, (select count(*) from proquick where proquick.rph=dpch.pname) as expr1 from dpch;

Anyone see any problem with this? I'm trying to get a count of proquick instances per pname's in dpch.
Thanks,
Chris

 

[PHV]

Why not simply an aggregate query ?
SELECT dpch.pname, dpch.pnum, COUNT(*) AS expr1
FROM dpch INNER JOIN proquick ON dpch.pname=proquick.rph GROUP BY dpch.pname, dpch.pnum

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netbumbler]

PHV,
Thanks for the response - but that code gives me an aggregation error with PNAME. Only problem I'm really having at this point is getting the order by to work. - using ORDER BY "Expr1" ASC;
but it's not ordering the results...
Thanks,
Chris

 

[PHV]

ORDER BY 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netbumbler]

Hmmm... for some reason it doesn't work. I works if I use order by 2 but not for order by 3... thoughts?
Thanks,
Chris

 

[PHV]

Any chance you could post your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netbumbler]

It's the code I posted above (on the first post). I launch the query directly to view the data - and I can sort by column 3 - and it remembers how I sorted the 'view'. I can change the SQL to say sort by 1 and the launching SQL directly still bring up the previous view settings. Thoughts?
Thanks,
Chris

 

[netbumbler]

Please note - when I say I sort by column three - I mean I right click on the column, select sort by ascending... and it sorts.

 

[PHV]

aggregation error with PNAME
Is by chance PNAME a memo ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netbumbler]

No - it's not a memo - just a standard text field.
I'm stumped...

 

[r937]

what about this --

SELECT pname
     , pnum
     , expr1 
  from (   
       SELECT dpch.pname
            , dpch.pnum
            , ( select count(*) 
                  from proquick 
                 where proquick.rph
                     = dpch.pname) as expr1 
        from dpch
       ) as data
order by expr1

r937.com | rudy.ca

 

[netbumbler]

r937,
That works great! It never ceases to amaze me how seemingly little innocuous syntax variations can have such a profound effect on a project! Is the key to this subquery the "expr1 from " component?
Thanks,
Chris