Ms-access – Separating a date column in Access into Month, Day and Year while keeping the new columns ordered by a seperate column(primary key)

ALTER TABLE table ADD COLUMN Start_Date TEXT(6)
ALTER TABLE table ADD COLUMN End_Date TEXT(6)

update table
set Start_Date = Left(column,6),
End_Date = Right(column,6)

You can either run the following as many times as it takes to get zero rows updated:

UPDATE IncentiveAssignments SET
  PersonID = (SELECT MIN(PersonID) FROM UnassignedPeople up WHERE NOT EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = up.PersonID)
WHERE AutoNum = (
  SELECT MIN(AutoNum)
  FROM IncentiveAssignments
  WHERE PersonID IS NULL);

And then run the following to clear all assigned persons:

DELETE FROM UnassignedPeople up
WHERE EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = up.PersonID);

Alternatively, you can use a couple of transient tables:

CREATE TABLE TmpAssignments (
  TmpID AUTOINCREMENT,
  AutoNum int);

CREATE TABLE TmpPersons (
  TmpID AUTOINCREMENT,
  PersonID int);

INSERT INTO TmpAssignments (AutoNum)
SELECT AutoNum
FROM IncentiveAssignments
WHERE PersonID IS NULL;

INSERT INTO TmpPersons (PersonID)
SELECT PersonID
FROM UnassignedPeople up
WHERE PersonID NOT EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = up.PersonID);

UPDATE IncentiveAssignments SET
  PersonID = (SELECT ta.AutoNum, tp.PersonID
              FROM TmpAssignments ta
              JOIN TmpPersons tp ON (tp.TmpID = ta.TmpID)
              WHERE ta.AutoNum = IncentiveAssignments.AutoNum)
WHERE PersonID IS NULL;

DELETE FROM IncentiveAssignments ia
WHERE EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = ia.PersonID);

DROP TABLE TmpPersons;
DROP TABLE TmpAssignments;