update select fields

[Guest]

First - I'm a newbie to Access. I have 4 files (representing 4 FY quarters)
that I am trying to integrate into a single master file. I need to
selectively update some fields without replacing the entire record. I tried
using 'coalesce' in the sql view of an update query but Access didn't
recognize that function. Sample of files 1 & 2 and desired outcome below:
File1
ID Lname Fname FullName Q1Pay Q2Pay Q3Pay Q4Pay
23 Card Robert Robert Card 10.00
56 G. Pres. George Bush 40.00
67 Sen. Al Gore 40.00
106 Jolie Ms. A. Jolie 70.00

File2
ID Lname Fname FullName Q1Pay Q2Pay Q3Pay Q4Pay
23 Card Robert Robert Card 20.00
56 Bush George 10.00
67 Gore Jr. Al Gore 10.00
106 Jolie Angelina 70.00

Desired merge of File1 and File2
ID Lname Fname FullName Q1Pay Q2Pay Q3Pay Q4Pay
23 Card Robert Robert Card 10.00 20.00
56 Bush G. Pres. George Bush 40.00 10.00
67 Gore Jr. Sen. Al Gore 40.00 10.00
106 Jolie Angelina Ms. A. Jolie 70.00 70.00

Many thanks in advance for any assistance!

 

[Tom Ellison]

Dear RCard:

If you want to have any hope of easily performing any serious analysis on
this, it should not be put into one table.

A separate table should contain the quarterly amounts, keyed by ID and
quarter.

Besides the ability to aggregate this for analysis, this has the advantage
of being designable to handle multiple years, not just the 4 quarters, but
many more. It would then be keyed by ID, Quarter, and Year. Each row would
store just one quarterly value.

If you leave things as they are, you can aggregate the totals like this:

SELECT ID, Lname, Fname, FullName,
Sum(Q1Pay) AS Q1, Sum(Q2Pay) AS Q2,
Sum(Q3Pay) AS Q3, Sum(Q4Pay) AS Q4
(SELECT * FROM Qtr1
UNION ALL
SELECT * FROM Qtr2
UNION ALL
SELECT * FROM Qtr3
UNION ALL
SELECT * FROM Qtr4) X
GROUP BY ID, Lname, Fname, FullName

Put in your actual table names, and check the column names I've used.

Tom Ellison

 

[Duane Hookom]

I expect you can use Nz() in place of the Coalesce() function.

Have you considered normalizing your table structure?