Append records that are not duplicates

[louise]

Hello,
I have a query to append records to a table. Later, I need to append only the new records produced by that query.

I cannot figure out how to do this -- I thought of a yes/no field for "has been appended" or a "date appended" field that would be the basis of a criteria in the query, but I can't figure out where to put these fields -- the receiving table? the individuals' record?

I was trying to use an IIF( statement to create the field but can't see how to add a date only if a previous date doesn't already exist.
When I use the Date() field, it changes the date of the old ones as well as the new ones.

thanks for any help!

[ranman256]

if the table is keyed then you can add all records and the duplicate keys will fall out.

If you cant key the table, then if you have a table of records to add, ex: tRecs2Add
have a field in tRecs2Add call [Mark]
join this table to the target table (the one you dont want dupes)
run an update query and mark the [MARK] field . These are the dupes. dont add them.
now run an append query where [mark] = ""

[louise]

if the table is keyed then you can add all records and the duplicate keys will fall out.

If you cant key the table, then if you have a table of records to add, ex: tRecs2Add
have a field in tRecs2Add call [Mark]
join this table to the target table (the one you dont want dupes)
run an update query and mark the [MARK] field . These are the dupes. dont add them.
now run an append query where [mark] = ""

Hello,
Thanks for your prompt response.
Here is a further issue: An individual may actually be appended to the target table more than once, e.g., appended again when the individual selects another course, so another field differs but the Primary key variable is the same. I did try your first suggestion, making the MembInfo the primary key in the target table, but it would not append that person the second time when necessary.

Can there ever be a composite primary key, comprised of the Member ID and the CourseID, created as the records are appended?

For your second suggestion, I am unclear about where to put the field [mark] since the fields to be appended to the target table are generated by a query based on several fields, so there is no single table to join to the target table. It is a target table of individuals, so should I add that field to the individual info table ....but then could you tell me the type of relationship between the tables?

Thanks for any further help!

[mindea]

You must have a source table from which your query takes records and adds them to the target table. Add a Yes/No field to the source table called "Added" or "Appended" or something like that with a default value of No. When you want to append records to the source table, run two queries in sequence. The first one appends any "No" records from the source to the target table. The second query changes the No to Yes in the source table.

[louise]

Thank you! I poked around a bit more on your first suggestion and learned about unique composite indexes. And this worked. I'm glad to also how the second suggestion would also work. I can't see how to mark this as "solved", but it is. Thanks!