bluetornado_666
asked on
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.?
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.?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh play with it as a select statement and when you are happy change it to an append / insert query.
ASKER
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
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
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
w.IDColumn
y.WeeksIDColumn