Append multiplying generated records

[mikke3141]

Hi,

I'm trying to insert into my headcount table new persons with the following code.

INSERT INTO HeadCountTbl (HeadCount, EmpID, Month_ID )
SELECT 1, HeadCountTbl.EmpID, HeadCountTbl.Month_ID
FROM MonthTbl INNER JOIN HeadCountTbl ON MonthTbl.ID = HeadCountTbl.Month_ID
WHERE (((HeadCountTbl.EmpID)=123) AND ((MonthTbl.Month)=201905));

The month information is coming from another linked table. Now each time I run the code it doubles the amount of records appended. First time it adds one person, second time the same person twice and third time four times. Is there a way to fix the SQL so that it generates only one line to the table?

 

[theDBguy]

Hi. What happens if you don't join the month table with the head count table?

 

[mikke3141]

Hi,

Then the Month_ID field in the headcount table becomes blank.

Mikke

 

[theDBguy]

Hi,

Then the Month_ID field in the headcount table becomes blank.

Mikke

Hi. Probably not the best way but try using a DLookup() for it.