Append only different OrderID's

  • Thread starter Thread starter Guest
  • Start date Start date
G

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"));
 
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>
 
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

Jeff Boyce said:
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>

Wind54Surfer said:
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
Click to expand...
Click to expand...
 
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>

Wind54Surfer said:
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

Jeff Boyce said:
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>

Wind54Surfer said:
Hi all,
I have an append query that I run daily and want to append only
Click to expand...
records
with
different OrderID.
Click to expand...
------------------------------------------------------------------------
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"));
Click to expand...
--------------------------------------------------------------------------
Any ideas?
Thanks in advance,
Emilio
Click to expand...
Click to expand...
Click to expand...
 
Back
Top