Include Random Memo Field Not in Group By 1

[jcbirch]

Hi,

I know it's bad form, but I want to run a SQL statement with a GROUP BY wherein not all my data is in the GROUP BY clause nor aggregated. I just want to randomly pick a few fields for inclusion. Long story as to why... but trust me it's fine.

Fields are as follows:
sender
domain(s)
date
incidents
violations
policy
description

I want to group by sender, domain(s), and date... sum incidents and violations, and randomly pick policy and description. Here's what my statement looks like:

db.Execute "SELECT sender, [domain(s)], date, SUM(incidents) AS incidents1, SUM(violations) AS violations1, MAX(policy) AS policy1, MAX(description) AS desc1 INTO data_tmp FROM data GROUP BY LOB, sender, [domain(s)], date"

MAX-ing the policy works fine, b/c it's a text field. However this does not work for description, which is a memo.

The statement above yields the error message: "Cannot have memo, OLE or Hyperlink Object fields in aggregate argument (description)."

I do not want to change the datatype, and don't care which description field is ultimately retained. I just want to populate the report with something from the original records (for the correct sender/domain/date) without too much trouble or code. Any ideas?

 

[lameid]

Instead of Max, try first or last. Just don't expect to be able to convert your query to a non-Access backend.

Also note that none of these is a random result. You will get the same result for the same data.

Also worth mentioning is that first and last should be faster than min or max.

 

[jcbirch]

You're a genius! That worked perfectly. Thanks so much.