I have raw data imported with columns containing a Training ID (Item ID), a Trainer (Column 1), an Evaluator (Column 2), and an identifier for the combination of those 3 values (Column 3).
When importing this data, a query will append a table called People with both Trainers and Evaluators. Another will append a table called Trainings. I have a third query for appending a table called Surveys which will store all 4 of the values listed here, but I want it to refer to the People and Trainings tables, rather than duplicate the values directly from the import.
I have the following SQL query written, but when I try to save it, Access says, "Syntax error (missing operator)". Where is the syntax error, and is there a better way to write this query?
INSERT INTO Surveys (Trainer, Evaluator, Training, Combo)
SELECT
Tr.PersonName As Trainer,
Ev.PersonName As Evaluator,
Tn.TrainingID As Training,
Import.Column3 As Combo
FROM(
SELECT DISTINCT Column3
FROM Import
) AS DistinctCombo
INNER JOIN Import On DistinctCombo.Column3 = Import.Column3
INNER JOIN People As Tr ON Import.Column1 = Tr.PersonName
INNER JOIN People AS Ev ON Import.Column2 = Ev.PersonName
INNER JOIN Trainings As Tn ON Import.[Item ID] = Tn.TrainingID;
Thanks to the comments above on use of parentheses and having the query designer help a little, I was able to get it to work using the following:
INSERT INTO Surveys ( Trainer, Evaluator, Training, Combo )
SELECT DISTINCT p1.ID AS TrainerID, p2.ID AS TraineeID, t.ID AS TrainingD,
s.Column3 AS Expr1
FROM ((Import AS s LEFT JOIN People AS p1 ON s.Column1 = p1.PersonName)
LEFT JOIN
People AS p2 ON s.Column2 = p2.PersonName)
LEFT JOIN Trainings AS t ON s.[Item
ID] = t.TrainingID;