Adding values when importing data (2 Viewers)

[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

 

[The_Doc_Man]

OK, I'll be the one to toss the stink bomb. Your tables do not appear to be structurally correct. They are not normalized.

You should not have two tables of employees, one group new and the other not new. Instead, you should have ONE table of employees and they should each be flagged individually with a BOOLEAN / YN field to indicate NEW or NOT NEW. In fact, there should be more than that, because if you have a hire date and a time-limit after which you are not considered new, you could automate that decision. Then you can use selective queries to isolate the new/not new groups, or a non-selective query to group them regardless of age (i.e. total employees).