stop duplicate in append query (1 Viewer)

[steve111]

hi

I have a append query that looks like this

[QUOTEINSERT INTO [all events] ( EventID, InstanceID, EventDate, EventDescrip, EventStart, RecurCount, PeriodFreq, Machinetype )
SELECT qryEventCartesian.EventID, qryEventCartesian.InstanceID, IIf([tblEventException].[EventID] Is Null,IIf(([qryEventCartesian].[PeriodTypeID] Is Null) Or ([qryEventCartesian].[PeriodFreq] Is Null) Or ([qryEventCartesian].[InstanceID] Is Null),[qryEventCartesian].[EventStart],DateAdd([qryEventCartesian].[PeriodTypeID],[qryEventCartesian].[InstanceID]*[qryEventCartesian].[PeriodFreq],[qryEventCartesian].[EventStart])),IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate])) AS EventDate, qryEventCartesian.EventDescrip, qryEventCartesian.EventStart, qryEventCartesian.RecurCount, qryEventCartesian.PeriodFreq, tblEvent.MachineTool
FROM (((qryEventCartesian LEFT JOIN tblEventException ON (qryEventCartesian.InstanceID = tblEventException.InstanceID) AND (qryEventCartesian.EventID = tblEventException.EventID)) LEFT JOIN ltPeriodType ON qryEventCartesian.PeriodTypeID = ltPeriodType.PeriodTypeId) LEFT JOIN Machinetool ON qryEventCartesian.EventID = Machinetool.MachineID) LEFT JOIN tblEvent ON Machinetool.MachineID = tblEvent.EventID
WHERE (((qryEventCartesian.EventID)=[ENTER EVENT ID]))
ORDER BY qryEventCartesian.EventID, qryEventCartesian.InstanceID;
][/QUOTE]

what can happen is that the user could append some of the data twice

is it possible to create a macro that would look at the " eventid " and the " eventdate" and not append any of the records that are the same

thanks
steve

 

[Cronk]

If you define duplicate data as the same occurrence of EventID and EventDate, create a unique index on the two fields. Search Access multi column indexes if you don't know how. Then your append query will simply not append subsequent occurences.

 

[steve111]

hi,
I open the table in design view and then selected indexes , I then selected both fields and then saved the table
I then run the query and it would only put in one record

thanks
steve

 

[steve111]

hi ,

I have attached my database for you to look at

if you run the query you are asked for the eventid which is 1
the records will appear in the subform albeit less
if you look at eventid 2 you will see the dates are the same . this will not work
I would like it that any eventid will run except when the dates are the same for that event id only

steve

 

[Cronk]

Re-read my previous response c a r e f u l l y.
I did NOT say put a unique index on the EventDate.

 

[steve111]

hi,

still having a problem so not sure if I am setting it up correctly

if I put no unique ref on any field I can duplicate
if I put a unique ref on the date I cannot duplicate the dates in event 1 or event 2 of which I want to be able to on event 2 or 3 etc
if I put a unique ref on the date I can add more dates to event1 and it will only copy the extra dates. which seems to be correct .
if I put a unique ref just on the eventid I can only get one record

thanks for your help

steve

 

[arnelgp]

as cronk has said, put and index on EventID+Date (No duplicate).

 

[steve111]

hi ,

i have tried that. but it then don't allow any dates or eventid to duplicate

i would like to duplicate the eventid if the date is different
i would like to duplicate the date if the event id is different

i want to end up with something like this
"eventid 1" maybe 500 records with different dates if i try to re-append it will only put in new dates for eventid 1 maybe another 50 records
so i now have 550 records showing eventid 1 with different dates

if i append eventid 2 with the same dates as eventid 1 it will let it do it
if i try to re append eventid2 with some of the same dates and some with different dates it will only re-append the dates that are different

so not sure if i am setting it up wrong

thanks for all your help
steve

 

[Cronk]

Did you look up details on multi column indexes and how to create them? Doesn't appear so.

However, open your table in design mode and create the new unique index Date_ID as a composite of EventID and EventDate. See image. Goodbye.