Data Type and queries

[Guest]

I have a query with an expression in it but I'm getting an error message
"Can't group on Memo". One of the tables that I'm running the query off of
has "memo" as a data type. Is there any other way to make this query work
other then changing the data type to text?


Thanks
PJ

 

[Allen Browne]

In the Total row under your Memo field, try:
First
Access will then return the first thing it finds in the memo field once all
the other grouping matches have been made.

As an example, picture a query with 2 tables:
- tblInvoice, with InvoiceID, CustomerID, and Comments (memo);
- tblInvoiceDetail, with ProductID, Amount, etc.
If you are grouping by InvoiceID and summing Amount (to get the total for
each invoice), then there can actually be only one value returned for the
Comments field. Using First is the ideal solution for this case.