Solved

Problem using derived column name in Group By

Posted on 2006-06-29
6
749 Views
Last Modified: 2012-08-13
Running Access 2K as a back end using SQL via ODBC and having difficulty using a derived column name in a Group By clause.
Statement is:
select count(*) as cnt, counselorid, mid(shortdate,6,2) + "/" + mid(shortdate,1,4)  as newdate  from meetings group by newdate, counselorid;

Error msg is:
You tried to execute a query that does not include the specified expression 'mid(shortdate,6,2)+[/]+mid(shortdate,1,4)' as part of an aggregate function.

Can't figure out what's wrong.
0
Comment
Question by:lennyh
  • 4
  • 2
6 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
ID: 17013497
Try:

select count(*) as cnt, counselorid, mid(shortdate,6,2) + "/" + mid(shortdate,1,4)  as newdate  from meetings group by mid(shortdate,6,2) + "/" + mid(shortdate,1,4), counselorid;

Nic;o)
0
 

Author Comment

by:lennyh
ID: 17013707
nico5038,

Thank you for your speedy response.  Your suggested SQL works great when executed in native Access, but unfortunately does not work via odbc. Seems like there is something about obdc that requires a slightly different syntax -- quotes or parens or something.  I've tried a few different things, but so far, no success.

Any further ideas.

Lenny
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17013749
Hmm, strange, try:

select count(*) as cnt, counselorid, mid(shortdate,6,2) & "/" & mid(shortdate,1,4)  as newdate  from meetings group by mid(shortdate,6,2) & "/" & mid(shortdate,1,4), counselorid;

when the backend database is in MSAccess, otherwise it's more efficient to use a "Passthrough" query in the native SQL of the backend database. When you need more info on that check the helpfile to see the merits :-)

Nic;o)
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:lennyh
ID: 17014031
Thanks for the further suggestion. Unfortunately, still doesn't work.

The simpler case,
select count(*) as cnt, counselorid, shortdate as newdate  from meetings group by shortdate, counselorid;
works fine.  Unfortunately, of course, it doesn't produce the result I want.

I'm a bit confused by your last paragraph.  I'm currently accessing the Access mdb on a remote server via a browser page that includes a php-like scripting language to manipulate the db. The server connects to the db via odbc, and I suspect that is where the problem is occcuring.  Don't know how that relates to a "Passthrough" query.

In any case, it appears my problem is either that odbc is demanding a special syntax for the derived column that we haven't hit upon, or it doesn't even support it.  I suspect the former.

Any other hints welcome.

Thanks.

Lenny
0
 

Author Comment

by:lennyh
ID: 17014681
Fixed:

Replacing the double quotes (around the slash) with single quotes fixed it.  It then worked using either the plus (+) or ambersand (&) as the concatenating operator.

Just kept trying things until I got it to work.

0
 

Author Comment

by:lennyh
ID: 17014691
Awarded points to Nico for the hint of reusing the concatenated columns rather than the dervied column name in the Group By.

Thank you.

Lenny
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question