I would like to export a table to a tab-delimited text file, but with the ability to automatically add a blank line after each group of records.
Mr Muggs<tab> 10
Mr Muggs<tab> 25
Mrs Muggins <tab> 15
Mrs Muggins <tab> 18.
(The accounting software I want to upload these records into requires this blank line to delimit each invoice)
The solution could be in the export, or in a query to append a blank line to an intermediate table after change of key.
This blank could be either before or after the key change. Multiple blanks are permitted and ignored.
0. Empty temp table
1. Extract all detail records, and append them into a temp table, with all the required fields, plus an additional extra field, I called KeyName, containing the key (ie. in my case Client Name). (eg. the file is having two fields with the same name data)
2. Using this same table, run a SUM query based on KeyName, and with APPEND the records into itself with all fields blank except the KeyName field.
3. Sort the table by KeyName, ClientName
4. Export this temp table, excluding the KeyName field
(3 is really only just a select query that is used by 4)
and whalla, it's as good as it getz,
Maybe someone can come up with a more basic approach