Adding values when importing data (1 Viewer)

[rpocock]

Hi,

Can anyone help with the following please?

I have two tables –

Table 1 contains the number of employees at 3 companies -

Company Name	Total Employees
Company A	15
Company B	10
Company C	200

Table 2 has the number of new employees that have just started -

Company Name	New Employees
Company A	2
Company B	3
Company C	5

I want to update the table 1 with the combined value of both tables.

So, the table 1 would look like –

Company Name	Total Employees
Company A	17
Company B	13
Company C	205

If I use a standard update query, it will replace the value in table 1 with the value from the table 2. This is not what I want, I want to add the value from the 2nd table to the value in the 1st table.

Can anyone help please?

Thanks

Richard

 

[isladogs]

UPDATE Table1
INNER JOIN Table2 ON Table1.[Company Name] = Table2.[Company Name]
SET Table1.[Total Employees] = [Table1].[Total Employees] + [Table2].[New Employees];

 

[MajP]

If not familiar with using sql here is the designer.

 

[rpocock]

Thank you for your help. I went with the designer solution and it works great. Thank you very much.

 

[isladogs]

In case you aren't aware, both #2 & #3 are the same solution - the query designer is just displaying the query SQL visually.
If you right click on the query design window and select SQL View, you will see the SQL from post #2