Missing autonumbers in table after append query update

[mos]

Hi, I have a database with a list of scheduled tasks that need to be completed. I have made an append query that releases a number of schedules based on a start and end date range, usually one month in advance. (This is to help keep the large volume of live tasks manageable) The append query updates a table with indexes set to no duplicates. Each task is given a unique number (autonumber) so the history can be tracked. Everything works fine other than if I run the append query on or over some of the previously used date ranges it will generate duplicates, the table index effectively deletes these once they are assigned. The problem is that this leaves large amounts gaps in the autonumber sequence. Is there a way to delete the duplicates when appending data before the autonumbers are assigned within the table? I have searched high and low, any help would be greatly appreciated.

[June7]

Build the append query so that it doesn't include records if there are already corresponding entries in the table.

Why are gaps in autonumber sequence a concern?

[mos]

Build the append query so that it doesn't include records if there are already corresponding entries in the table.

Why are gaps in autonumber sequence a concern?

Hi June7, thanks for your assistance.
That sounds exactly what I need, having little experience of append queries could you explain how to append without including corresponding entries in the target table please? I’m new to both access and vba so please explain in simplistic terms.
Regarding why are the gaps in the autonumber a concern; I have 1000’s of schedules each with potentially 1000’s of tasks. If I have already released 100’s of tasks for the following month(s) and then add another schedule which has required tasks within the already released time period and then need to release tasks within a date range again, I could have gaps within my sequential numbers 1000’s. The autonumbers wouldn’t be a true indicator of the volume of work; also if somebody deleted an entry it wouldn’t be obvious due to the gaps in the numbering system.

[orange]

A query that Counts Tasks within a Date range will satisfy you need. You should not depend on autonumbers to be sequential nor always positive.
In general, what if you "released" as Task and subsequently - for whatever reason - had to delete it. Would you back and renumber Tasks that were "released" after that Task was "released" and before it was deleted?

[June7]

I don't know your data structure so can't be specific. I expect one approach would involve building a SELECT query by joining appropriate tables (Tasks and ScheduledTasks) and retrieving records where TaskID in ScheduledTasks is null and/or no date within the given date range then use that query as the source for records to append.

Another approach uses domain aggregate functions such as DLookup or DCount in a query.

As Orange indicated, don't depend on the ID numbering to determine volume, use query method or DCount() function to count records. A sequence number can also easily be generated in a report with the RunningSum property of textbox.