Prevent Append of Multiple/Duplicate records

Hi,

I have a table called 'Year' in which I have a field called the 'WeekNo'.I append the records into this table every week & flood the weekno field with a number for that particular weekno(which are in sequence week after week).Actually,the append is to be done automatically with the help of a macro which would run the append Query everytime I open the Database.

ex.if I open the database this week,I would flood the 'WeekNo' field with No=1.next week when I open the database & append the new records,I would flood the 'WeekNo' field with No=2 for those new records...so on & so forth..

For instance,the append operation for the records is done every Monday.

My question is if I open the database more than once anyday in middle of a week,...I need a check that would tell me that the records for that particular week have already been appended..

How would a go about doing it..Do i write a query or a trigger function.?
bluetornado_666Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonLarsenCommented:
Use a left join:

insert into Year (Column1, Column2 etc)
Select w.Column1, w.Column2 etc)
from tblWeek w left join
Year y
on w.IDColumn = y.WeeksIDColumn
Where y.WeeksIDColumn is null

In the GUI, do the link by drag and drop then get the properties on the line linking the table, ensure you select all from the week table and matching from Year

In the criteria column put the Year DI column and specify Is Null.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SimonLarsenCommented:
Oh play with it as a select statement and when you are happy change it to an append / insert query.
0
bluetornado_666Author Commented:
Hi Simon,

The 'Is Null' clause did work for a few queries on cetain tables, but the computer system almost hangs up (for many tables which have large no of records),while validating this criteria.

Also if my query has a lot many criterias in it,then also the 'Is NULL' clause does not work.

Is there any other way of doing it..anything effecient & faster as well...instead of using "Is NULL"

Thanks
0
SimonLarsenCommented:
Nope if you have criteria is null is going to as fast as any other way I can think of in a dynamic way. try putting indices on these columns:

w.IDColumn
y.WeeksIDColumn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.