Solved

Problem using derived column name in Group By

Posted on 2006-06-29
6
752 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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