Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problem using derived column name in Group By

Posted on 2006-06-29
6
Medium Priority
?
760 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 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

916 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