Solved

Problem using derived column name in Group By

Posted on 2006-06-29
6
751 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
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.

 

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

Independent Software Vendors: 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

Suggested Solutions

Title # Comments Views Activity
Mimic UNC drive 10 47
IIF syntax in MS Access 1 59
Find unused columns in a table 12 68
Runtime Error -2147467259 (80004005) 7 42
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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 …

740 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