CODEWITHSUNDEEP
sqlms-accessselectinsert-into
David Austin
David Austin

Reputation: 37

MS Access SQL Insert Into table where records do not exist

I am building an Access database for my work and I am a bit stuck with how to execute an INSERT INTO SQL query. I have a table (tblTempData) which has data imported from Excel. 2 of the columns contain the names of wards and consultants respectively. I have converted these to their correct WardID and ConsultantID by comparing their names to the stored names in my tblWard and tblConsultant tables.

At my work, a ward can have many consultants and a consultant can work on many wards so I have made a bridge table called tblWardConsultant which has a primary key and the FKs of WardID and ConsultantID. I now need to compare the records in tblTempData to tblWardConsultant and add any ward/consultant combinations that do not currently exist to tblWardConsultant.

E.g. 

 tblWard:  WardID: 1, WardName: Ward X
           WardID: 2, WardName: Ward Y

 tblConsultant: ConsultantID: 1, ConsultantName: Person A
                ConsultantID: 2, ConsultantName: Person B

As person A can work on ward X and Y and person B works on ward X, they are in the following bridge table:

 tblWardConsultant: WardConsultantID: 1, WardID: 1, ConsultantID: 1
                    WardConsultantID: 2, WardID: 2, ConsultantID: 1
                    WardConsultantID: 3, WardID: 1, ConsultantID: 2

If in my tblTempData, I find that person B also works on ward Y, I need to add the WardID and ConsultantID into tblWardConsultant as a new record as:

 tblWardConsultant: WardConsultantID: 4, WardID: 2, ConsultantID: 2

So the question is how to do this! I have tried to just make the select part of the query using the code below but this returns all ward/consultant combos regardless of whether they are in the tblWardConsultant table or not. Changing it to WHERE EXISTS returns no results:

SELECT tblTempData.WardID, tblTempData.ConsultantID
FROM tblTempData
WHERE NOT EXISTS (
SELECT tblWardConsultant.WardID, tblWardConsultant.ConsultantID
FROM tblWardConsultant)
AND tblTempData.WardID IS NOT NULL
AND tblTempData.ConsultantID IS NOT NULL;

Upvotes: 2

Views: 2598

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You need a correlated subquery, such as:

SELECT DISTINCT td.WardID, td.ConsultantID
FROM tblTempData as td
WHERE NOT EXISTS (SELECT twc.WardID, twc.ConsultantID
                  FROM tblWardConsultant as twc
                  WHERE twc.WardId = td.WardId AND twc.ConsultantId = tw.ConsultantId
                 ) AND 
      td.WardID IS NOT NULL AND
      td.ConsultantID IS NOT NULL;

Upvotes: 2

Parfait
Parfait

Reputation: 107567

As an alternative, consider using the LEFT JOIN...IS NULL query (avoids subquery and might be better in readability if not performance):

SELECT tblWardConsultant.WardID, tblWardConsultant.ConsultantID 
FROM tblWardConsultant 
LEFT JOIN tblTempData
       ON tblWardConsultant.WardID = tblTempData.WardID 
      AND tblWardConsultant.ConsultantID = tblTempData.ConsultantID
WHERE tblWardConsultant.WardID IS NULL
AND tblWardConsultant.ConsultantID IS NULL;

Upvotes: 1

Related Questions