Is there a method, without having to export and manipulate data externally, to select records based on how many of something the record has?

In MySQL, I'd use something like this for selecting records that have more than one of a given attribute:

SELECT attr, COUNT(attr) AS cnt FROM table GROUP BY attr HAVING cnt > 1

But it appears that if I base a query on an attribue, it gives me fixed options (blank, not blank, contains, ..), but no real power to query it further than that.

Thanks,
Pete 

If you have a certain maintenance level on RE (I think it's advantage professional), you can get an unlock code for RE:Open, which is read-only database access.  This gives you the ability to write much more advanced queries (in MSSQL syntax) to meet your needs.  The regular query module has some ability to query on count but only for specific items, like total number of gifts. Hope this helps!

Could you use pivot reports for this?  If you build your query and create a pivot report you should be able to get a count from there. Hope this helps!

Charlotte

Brilliant, thanks for getting back to me.

The only downside I can see to the pivot report option is that it won't let me use that data within, say, Mail.

I'll look into both of these suggestions.

Thanks again :)
 

Fire up MS-Query Analyzer or SQL Server Management Studio to write a query,

You would need to reference to your table to the following tables:

to find what you are looking for.

Mark.

Hi Mark,

Thanks for your input on this, and I do have access to the SQL Server console, but it still leaves me unable to perform functions natively within RE and pass the results through to other parts of RE. Mail being the simplest example.

I'll keep plugging away :)
 

If you have the VBA module enabled you can use the VBA User Field of Query or Export. See KB article BB59638 for an example in Export (change the parameter IBBExportRow to IBBQueryRow for this to work in Query). Then you can use ADO or similar to query the SQL database directly from VBA and pass the result back into RE.

Regards
Wayne

Excellent, thanks guys.

I think I've arranged to have access to RE:Open now, which ought to help with what I need.

Do you know if the VBA bundle comes along with RE:Open? 

P 

No

RE:Open you can get for free with certain maintainance agreements.  The VBA module will run you into the 5 figures for cost.