Prevent duplicates in append query

[Leesha]

Hi,

I have a query that appends quarterly data to a table. I need to be sure that if the info for the quarter has already been appended to the table, the user is alerted and the query is stopped unless they decide to proceed and add additional data. What would be the best approach?

Thanks,
Leesha

[Carol W.]

Do you mean that:

• 1 - there would be duplicates within the quarterly data source

or

• 2 - there would already be a record in the destination table that is the same as a record coming in in the quarterly data source?

[Leesha]

Hi Carol,

There would already be a set of records in the database for the particular quarter and I don't want someone to enter more.

Leesha

[Carol W.]

Leesha,

Let me first say that I'm sure Hans would come up with a much more elegant solution but he is on a well deserved vacation.

I've put together a macro, mcr-find dups, that does the following (See attached):

1 - Creates a temp table from the existing quarterly data table
2- Appends the input source onto the temp table
3 - Creates an expression that concatenates year and quarter into expr1.
4- Finds expr1 duplicates in temp table

If this macro is run before your append query, the final query, qry-Find duplicates for qry-create year-qtr, will show the duplicate expr1's and the number of each. The user can then take appropriate action before the "real" append query is run.

There is a way of doing this in VBA, I'm sure. Perhaps someone else can come up with a VBA solution.

Hope this is helpful to you.

[Mark L]

Hi,

I have a query that appends quarterly data to a table. I need to be sure that if the info for the quarter has already been appended to the table, the user is alerted and the query is stopped unless they decide to proceed and add additional data. What would be the best approach?

Thanks,
Leesha

Just create a query that joins this quarter's info with the info in the table. The query will return any duplicates; if it doesn't return any records, there are no duplicates.

[Leesha]

Thank you everyone for the responses. I'm finally getting back to this. I will try out both approaches.

Thanks!
Leesha