Solved

Problem using derived column name in Group By

Posted on 2006-06-29
6
744 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now