Splitting the results of a query?

[Adam7]

Hi,

I have a query with approx 700,000 results. The records in this query all need to be emailed.

I need to split the query results up in to separate queries containing 100,000 or so records so they can be uploaded to the mailing website.

I'm not sure if I need to make a new table for it, or I'm missing something.



Any help is much appreciated.

Thanks,

Adam.

[Adam7]

Just thought maybe my best option would be to export the results in alphabetical order to excel, every week before they get emailed and split it on there?

Then atleast I would know people aren't getting more than 1 email.

All I can think of

[Rod]

There is SQL syntax to restrict the number of records in a query's result set to a certain number of records. This satisfies your requirement for the first tranche but what to do for the remaining tranches? The only way I can think is to have an attribute on your original tables that contains something like an email run number. You can then update those who have been extracted with the latest run number and exclude them from subsequent retrievals.

Alternately why not make your query a Make Table query? Then it is easy to extract the first n records, the second n records, und so weiter.

[Adam7]

There is SQL syntax to restrict the number of records in a query's result set to a certain number of records. This satisfies your requirement for the first tranche but what to do for the remaining tranches? The only way I can think is to have an attribute on your original tables that contains something like an email run number. You can then update those who have been extracted with the latest run number and exclude them from subsequent retrievals.

Alternately why not make your query a Make Table query? Then it is easy to extract the first n records, the second n records, und so weiter.

Thanks for your reply,

I have changed it to a make table query, could you explain how I would extract the 1st n records etc?

[Rod]

I can think of a number of ways (well basically a number of flavours of the same way) to do this. Every row on a DAO table opened as a dynaset or snapshot (other modes?) contains a property named AbsolutePosition (base 0) by which the rows may be uniquely identified. All my ideas need an 'outside' controlling mechanism to manage and 'remember' what has been sent.

1. Have your query create an extra column in the resulting table and then use a small VBA routine to populate this column with a sequential row number (i.e. the absolute position). The managing routine divides the total number of rows into managable tranches and retrieves and sends one at a time.
2. Similar to 1 but don't bother with the extra column. For this solution to be reliable the table must remain exactly as it was created, no additions, no deletions and no changes in sequence.
3. A variant of either 1 or 2 above: delete the sent rows from the table after each tranche.
4. A solution that does not involve absolute line numbers is if there is a useable value on your table by which the rows may be divided into tranches. For example maybe an alphabetic value could be used, A-C being the first tranche, D-F the secon, and so on.

I have though about your requirement and am concerned with the volumes. First, manipulating 700,000 records takes time and every operation saved is time gained. Second, Access DBs are of a limited size; to blindly create additional tables of 700,000 rows may not be a good idea. (Space could be saved by exporting the data to another automation application - e.g. Excel - but this seems more and more complex. It is however a serious suggestion in that the exporting routine could divide the 700,000 rows among a number of spreadsheets or workbooks.)

So if you want to keep this thread alive, I have some questions:

1. What do you mean by 'uploaded to the emailing website?' In what form and how do you send these records?
2. Could the intermediate table be eliminated by operating on the source table(s)?
3. Do the source tables contain a unique identifier in any of the columns?
4. Is there any value amongst the source table columns that could be used for dividing the extract into tranches?

In other words I need to know much more detail about what you are trying to do.