Ms-access – Updating a table with a not-joined query

ms access

I'm using Access and have a task that I want automated using an query.

I have a table (IncentiveAssignments) that lists out all of the Gift Card numbers (GiftCode, all are unique) I currently have along with the people I've given them to (PersonID, all are unique). Not all of the GiftCodes have been assigned a PersonID yet. There's also a primary key autonumber and some other irrelevant fields.

I also have a query (UnassignedPeople) that lists out all of the people (PersonID) that are currently not listed in IncentiveAssignments but need to be where there is an IncentiveAssignments.GiftCode with a null value in IncentiveAssignments.PersonID.

So this is my IncentiveAssignments table:

AutoNum  GiftCodes  PersonID
-------  ---------  --------
1        abcd       12
2        efgh       34
3        ijkl       .
4        mnop       .
5        qrst       .

And this is UnassignedPeople:

PersonID
--------
56
78

I want to create a query that will update IncentiveAssignments to be:

AutoNum  GiftCodes  PersonID
-------  ---------  --------
1        abcd       12
2        efgh       34
3        ijkl       56
4        mnop       78
5        qrst       .

Does anyone have any idea how I can do this? I'm currently just copy-pasting what's in the query into the table, but I'd much rather it be automated that way I don't worry about human error. It seems like my primary issue is that there's nothing to join the table and query together.

Much appreciated.

Best Answer

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;
Related Question