Re: Novice getting error on query - please critique

Tech-Archive recommends: Fix windows errors by optimizing your registry

First, I'd like to say thanks.

Secondly, I'm basically getting that same old error again:
You tried to execute a query that does not include the specified
expression 'Year(INVC.DETAIL.INVC_DATE)' as part of an aggregate
function.

If the past holds true for the future, it is going to give this error
for each item used in ORDER BY.
What's the best way to address this?

-Mo

On Mar 12, 9:51 pm, "Van T. Dinh" <VanThien.D...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Not sure as I lost track of the thread but try:

SELECT Format(INVC_DETAIL.INVOICE_DATE, "mmm,\ yy"),
CONSIGNMENT_CODES.CONSIGNMENT_CODE,
sum(INVC_DETAIL.QTY_SHIP*INVC_DETAIL.UNIT_PRICE)
FROM INVC_DETAIL, CONSIGNMENT_CODES
WHERE INVC_DETAIL.CNC_AUTO_KEY=CONSIGNMENT_CODES.CNC_AUTO_KEY
GROUP BY Format(INVC_DETAIL.INVOICE_DATE, "mmm,\ yy"),
CONSIGNMENT_CODES.CONSIGNMENT_CODE
ORDER BY Year(INVC_DETAIL.INVOICE_DATE) ASC,
Month(INVC_DETAIL.INVOICE_DATE) ASC;

However, that may not be the most efficient SQL as functions Format(),
Month() and Year() needs to be called for each Record ...

--
HTH
Van T. Dinh
MVP (Access)

<m...@xxxxxxxxxxxxxxxxxxxxx> wrote in message

news:1173721736.923882.152070@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



Well, if more support is available, I could actually use some help
tweaking it a little.

This is the code I have now:
SELECT INVC_DETAIL.INVOICE_DATE, CONSIGNMENT_CODES.CONSIGNMENT_CODE,
sum(INVC_DETAIL.QTY_SHIP*INVC_DETAIL.UNIT_PRICE)
FROM INVC_DETAIL, CONSIGNMENT_CODES
WHERE INVC_DETAIL.CNC_AUTO_KEY=CONSIGNMENT_CODES.CNC_AUTO_KEY
GROUP BY month(INVC_DETAIL.INVOICE_DATE),
CONSIGNMENT_CODES.CONSIGNMENT_CODE, INVC_DETAIL.INVOICE_DATE;

The results I REALLY want would be as such:
<u>Month Code Amt</u>
Jan, 06 B-001 $200.00
Jan, 06 B-015 $150.00
Feb, 06 B-001 $75.00
Feb, 06 C-017 $500.00

Right now, though, it is returning a record per date.

Any advise?
-Mo- Hide quoted text -

- Show quoted text -


.

Relevant Pages

  • Novice getting error on query - please critique
    ... ORDER BY YearASC, ... You tried to execute a query that does not include the specified ... expression 'Year' as part of an aggregate ...
    (microsoft.public.access.queries)
  • Novice rcvg error on query - please critique
    ... ORDER BY YearASC, ... You tried to execute a query that does not include the specified ... expression 'Year' as part of an aggregate ...
    (microsoft.public.access.queries)
  • LAST agregate function !!!
    ... By invoice_date asc") ... You tried to execute a query that does not include the specified expression ...
    (microsoft.public.inetserver.asp.general)
  • Preparing and executing a query with IN (?)
    ... This is my querry ... item_id ASC ... I have the query prepared. ... Now I want to execute it. ...
    (perl.dbi.users)
  • Re: Finally which ORM tool?
    ... manipulate the linq query IF you're executing it at that moment. ... simply because the declaration construction was with 'CHOPS'. ... implement IEnumerablebut had an Execute() method which gave back ...
    (microsoft.public.dotnet.languages.csharp)