how to use GROUP BY

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have unionized two tables and tried to use GROUP BY but it comes up with
syntex errors. What am I doing wrong

SELECT CashReceiptAmount AS "Cash","" AS "Sales"
FROM tblCashReceipts
Where Datepart("M",[CashReceiptDate])=[Month]
GROUP BY "Cash"
UNION select "", Extension
FROM qryInvoice
Where Datepart("M",[InvoiceDate])=[Month];
GROUP BY "Sales"
 
tomcat said:
I have unionized two tables and tried to use GROUP BY but it comes up with
syntex errors. What am I doing wrong

SELECT CashReceiptAmount AS "Cash","" AS "Sales"
FROM tblCashReceipts
Where Datepart("M",[CashReceiptDate])=[Month]
GROUP BY "Cash"
UNION select "", Extension
FROM qryInvoice
Where Datepart("M",[InvoiceDate])=[Month];
GROUP BY "Sales"
Click to expand...


Don't use an alias name in a separate clause.

SELECT CashReceiptAmount AS "Cash","" AS "Sales"
FROM tblCashReceipts
Where Datepart("M",[CashReceiptDate])=[Month]
GROUP BY CashReceiptAmount
UNION select "", Extension
FROM qryInvoice
Where Datepart("M",[InvoiceDate])=[Month];
GROUP BY Extension
 
There are a number of errors with your query.
1. field names are enclosed in quotes
2. a numeric field is being populated with "" which is a zero length string.
"" and Null are NOT equivalent nor are they interchangeable. The first is a
zero-length STRING and can only be used with text fields. The second is
Null which can be used with both numeric and text field types as well as
dates (which are numeric field types).
3. the queries include group by clauses but there is no aggregation and so
no reason to group.
4. I don't know what the where clause is supposed to do. Do you really have
a field named [Month]? or are you trying to use the current month as
criteria?
5. Assuming that you have more than one year of data in the table, selecting
by month will return data from multiple years which I doubt will be what you
want.
6. You have a clause (group by) that follows the semi-colon.
7. I added "All" because if by chance there are duplicate amounts, the union
would summarize them away.

SELECT CashReceiptAmount AS Cash, CCur(0) AS Sales
FROM tblCashReceipts
Where Format([CashReceiptDate],"yyyy/mm") = Format(Date(), "yyyy/mm")
UNION ALL select CCur(0) As Cash, Extension As Sales
FROM qryInvoice
Where Format([InvoiceDate],"yyyy/mm") = Format(Date(), "yyyy/mm");

In general, I think this query will not be useful at all. It will result in
a number of rows with an amount and a 0 and a number of rows with a 0 and an
amount. You could sum each column to get a total for Cash and a total for
Sales but that's about it.
 
Back
Top