Group By Query With Memo Fields - make memo field an expression?

[kingcarol]

In Microsoft Access, I can do an aggregate query 'group by' query that includes memo fields. Since grouping by a memo field truncates the memo (which makes sense for efficiency), I can select a 'First' function as an expression for the memo field and include it in the query without it truncating. (since I don't want to group by the memo fields, but need them in the query) Here is a short video demonstrating this in Access: http://screencast.com/t/QJ4sMquq4c

I am trying to make myself use the Alpha query builder to write queries for grids or reports instead of being dependent on Access views. However, I can't find a feature like the 'First' function for memo fields when I want to do an aggregate query with group by for some fields and not for memo fields. I was thinking that I could create some expression with my memo field so that I could select Expression for it, but have not been successful. Any ideas?

(I've sent a message to Selwyn asking about this, but thought I'd throw it out here in case he doesn't have time.)

[frankbicknell]

Re: Group By Query With Memo Fields - make memo field an expression?

Carol,

Include all of the fields you want in the select query but just include the fields you want to group on in the the group by clause.

select fielda, fieldb, fieldc, memofield1 from table group by fielda, fieldb, fieldc

[kingcarol]

Re: Group By Query With Memo Fields - make memo field an expression?

Thanks Frank - I've tried that but then the Alpha query builder tells the memo field to group by 'Expression', which my back end Access database does not like and running the Alpha query shows 'SQL has error.' (screenshot of Alpha query builder: http://screencast.com/t/rbsFO1g6jQ). 'Expression' is one of the choices in Access query design, too, but if I just select 'Expression' in the Access query on a field that is not an expression, Access will return a message that says the 'query does not include the specified expression as part of an aggregate function'. I'm sure that's what's happening when I try to run the query from Alpha.

In Access, I quite often create a first query that groups things in order to get sums I need. Then I will open a second query that brings in the first query that is already grouped. In the second query I add the table with the memo fields and do not need to do the Group By. However, I don't see a way to do this in Alpha. Seems like I have to build the whole query in one piece. Can I nest one query inside another one somehow?

[frankbicknell]

Re: Group By Query With Memo Fields - make memo field an expression?

Carol, you may have to just write your own query. I am not sure about access but I use this type of query in mysql and ms sql. Use the builder then just modify the select statement as needed.

[kingcarol]

Re: Group By Query With Memo Fields - make memo field an expression?

Frank - Thanks. I am working on that now. Per Selwyn, he does not think there is an equivalent to 'First' in portable SQL and suggested I use Native instead. So I'm going to use the query I built, then switch to Native and modify the query to include the MS Access 'First' function.

For anyone reading this... this is how I would need to modify Frank's example (using Native and NOT Portable).....
select fielda, fieldb, fieldc, First(MemoField1) as FirstOfMemoField1 from table group by fielda, fieldb, fieldc

With a brief test, it appears Access is recognizing this now... since I'm speaking its "native" language :)