Insert data from two tables into one

advertisements

My question is:

I have 3 tables

Workers_Day, Workers_Night, Total

In workers_Day table:

ID, Name, Day_one, Day_two

In workers_Night table:

ID, Name, night_one, night_two

the fields will be filled with 1 or 0 (0 being absent and 1 being working)

and TOTAL

ID, Name, Total_days_working, Total_nights_Working, Total

I want to insert data from the table 1 and 2 to the table 3.. I made a queries like this but it doesn't work

insert into total (Id, name, Total_days_working, Total_nights_working, Total)
Select id,name,
IIf([day_one]>0,1,0)+IIf([day_two]>0,1,0) as day_workings,
IIf([night_one]>0,1,0)+IIf([night_two]>0,1,0) as Total_nights_working,
Total_days_working+Total_nights_working AS Total
From Workers_day,Workers_night
Where Workers_day.id = Workers_night.id

the condition doesnt always is true..

sometimes there are different workers in the tables but the problem start when the worker "John" with ID "3" is in both tables (Day ,night)


there are 2 mistake in your code

the correct is:

insert into total (Id, name, Total_days_working, Total_nights_working, Total)
Select Workers_day.id,Workers_day.name,
IIf([day_one]>0,1,0)+IIf([day_two]>0,1,0) as Total_days_working,
IIf([night_one]>0,1,0)+IIf([night_two]>0,1,0) as Total_nights_working,
Total_days_working+Total_nights_working AS Total
From Workers_day,Workers_night
Where Workers_day.id = Workers_night.id