How can I combine these two columns in access

Question

I am attempting to combine two columns (from separate tables) in an access query based on the value of 2 other columns in the tables (semester and student_name in this instance), for instance if I had the following:

and

how could I make a query to get the final result of:

where the third and fourth semester (only the third and fourth) sections of some_data have been combined based on student_name (there may be some names that are in one table, but not in another, etc, although that is not shown in these examples). I am not very experienced with access queries at this point in time, so I am having a difficult time accomplishing this. Any help is much appreciated, thanks!

Answer 1

UPDATE Sheet1 INNER JOIN Sheet2 
ON Sheet1.semester = Sheet2.semester
And  Sheet1.Student_name = Sheet2.Student_name
SET Sheet2.Some_data = Sheet1.Some_Data
Where Sheet1.semester in (3,4)

If you just want to see the results before they are applied you can click the Datasheet View button instead of the Run button.

If you want to leave your two tables intact and just have a view you can do this:

Select Sheet2.semester
, Sheet2.student_name
, Nz(Sheet2.some_data,Sheet1.some_data)
From Sheet1 INNER JOIN Sheet2 
ON Sheet1.semester = Sheet2.semester
And  Sheet1.Student_name = Sheet2.Student_name

with an optional where clause

Where Sheet1.semester in (3,4)

The Nz function here uses data from the first parameter unless that parameter is null, in which case it uses the value from the second parameter.

Answer 2

something like

select semester, student_name, max(some_data) from
(select semester, student_name, some_data from sheet1
union all
select semester, student_name, some_data from sheet2)
group by semester, student_name