Append only different OrderID's

[Guest]

Hi all,
I have an append query that I run daily and want to append only records with
different OrderID.
------------------------------------------------------------------------
INSERT INTO tblConfirmSchedule ( OrderID, JobCode, JobDate, Status, [Home#],
[Work #], Ext, [Other#], Ext2 )
SELECT Orders.OrderID, [Schedule Info].JobCode, JobSchedule.JobDate,
JobSchedule.Status, CustNew.[Home#], CustNew.[Work #], CustNew.Ext,
CustNew.[Other#], CustNew.Ext2
FROM (CustNew INNER JOIN Orders ON CustNew.CustomerID=Orders.CustomerID)
INNER JOIN ([Schedule Info] INNER JOIN JobSchedule ON [Schedule
Info].JobCode=JobSchedule.Scheduled) ON Orders.OrderID=[Schedule Info].OrderID
WHERE (((JobSchedule.JobDate)>Now()+3) And
((JobSchedule.Status)="Scheduled"));

 

[Jeff Boyce]

Emilio

If your tblConfirmSchedule has the Indexed property set to Yes, No
Duplicates on the OrderID field, Access will not accept "duplicate"
OrderIDs.

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Thanks for your response Jeff,

But if I do that I keep getting an error.
(is there a way to suppress the error?)

I also tried Select "Distinct", but doesn't work.

Thanks again,
Emilio

Emilio

If your tblConfirmSchedule has the Indexed property set to Yes, No
Duplicates on the OrderID field, Access will not accept "duplicate"
OrderIDs.

Good luck

Jeff Boyce
<Access MVP>

Hi all,
I have an append query that I run daily and want to append only records with
different OrderID.
------------------------------------------------------------------------
INSERT INTO tblConfirmSchedule ( OrderID, JobCode, JobDate, Status, [Home#],
[Work #], Ext, [Other#], Ext2 )
SELECT Orders.OrderID, [Schedule Info].JobCode, JobSchedule.JobDate,
JobSchedule.Status, CustNew.[Home#], CustNew.[Work #], CustNew.Ext,
CustNew.[Other#], CustNew.Ext2
FROM (CustNew INNER JOIN Orders ON CustNew.CustomerID=Orders.CustomerID)
INNER JOIN ([Schedule Info] INNER JOIN JobSchedule ON [Schedule
Info].JobCode=JobSchedule.Scheduled) ON Orders.OrderID=[Schedule Info].OrderID
WHERE (((JobSchedule.JobDate)>Now()+3) And
((JobSchedule.Status)="Scheduled"));
--------------------------------------------------------------------------

Any ideas?
Thanks in advance,
Emilio

 

[Jeff Boyce]

Emilio

By "error" I assume you mean the message that Access will not append some of
the records because of duplicates. If so, that implies that you have an
index that won't permit appending duplicates.

If you provided the error message, it would help...

Good luck

Jeff Boyce
<Access MVP>

Thanks for your response Jeff,

But if I do that I keep getting an error.
(is there a way to suppress the error?)

I also tried Select "Distinct", but doesn't work.

Thanks again,
Emilio

Emilio

If your tblConfirmSchedule has the Indexed property set to Yes, No
Duplicates on the OrderID field, Access will not accept "duplicate"
OrderIDs.

Good luck

Jeff Boyce
<Access MVP>

Hi all,
I have an append query that I run daily and want to append only

records
with

different OrderID.

------------------------------------------------------------------------

INSERT INTO tblConfirmSchedule ( OrderID, JobCode, JobDate, Status, [Home#],
[Work #], Ext, [Other#], Ext2 )
SELECT Orders.OrderID, [Schedule Info].JobCode, JobSchedule.JobDate,
JobSchedule.Status, CustNew.[Home#], CustNew.[Work #], CustNew.Ext,
CustNew.[Other#], CustNew.Ext2
FROM (CustNew INNER JOIN Orders ON CustNew.CustomerID=Orders.CustomerID)
INNER JOIN ([Schedule Info] INNER JOIN JobSchedule ON [Schedule
Info].JobCode=JobSchedule.Scheduled) ON Orders.OrderID=[Schedule Info].OrderID
WHERE (((JobSchedule.JobDate)>Now()+3) And
((JobSchedule.Status)="Scheduled"));

--------------------------------------------------------------------------

Any ideas?
Thanks in advance,
Emilio