[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Prevent Append of Multiple/Duplicate records

Posted on 2004-11-15
6
Medium Priority
?
229 Views
Last Modified: 2012-06-27
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.?
0
Comment
Question by:bluetornado_666
  • 3
4 Comments
 
LVL 8

Accepted Solution

by:
SimonLarsen earned 100 total points
ID: 12588787
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
 
LVL 8

Expert Comment

by:SimonLarsen
ID: 12588792
Oh play with it as a select statement and when you are happy change it to an append / insert query.
0
 

Author Comment

by:bluetornado_666
ID: 12765032
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
 
LVL 8

Expert Comment

by:SimonLarsen
ID: 12767215
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question