Populate field from one table into another based on query result

[nrichel]

Hello - thanks in advance for your assistance with my question! The data I'm using comes from several Excel files - I'm using Access to simplify the data. Just wanted to mention that upfront.

I have four tables:
FY11
FY12
FY13
Available

All four tables have the same primary key - SSN. I'd like to update the Available table by combining the data from the RR field for each record in the FY tables. Each FY table has the same RR column with different results based on each record. The same SSN may appear in all three tables (even with the same RR) that's ok, I want to be able to see that a record has been identified for each FY. Here's an example:

FY11 Table

SSN	Name	RR	State
111-11-1111	Doe Jane	PXR	MD
999-11-1111	Smith Bob	CXR	PA

FY12 Table

SSN	Name	RR	State
111-11-1111	Doe Jane	PXR	MD
222-11-1111	Pine Mike	CXR	DC

FY13 Table

SSN	Name	RR	State
333-11-1111	Cedar Larry	PXR	CA
222-11-1111	Pine Mike	CXR	DC

Available Table

SSN	Name	FY11 RR	FY12 RR	FY13 RR
111-11-1111	Doe Jane	PXR	PXR	Null
999-11-1111	Smith Bob	CXR	Null	Null

and so on...

Is there a way to do this using a nested IIf statement perhaps? I'm lost as to how to get this all into one table.

[ssanfu]

The current "Available" table is a bad design. You have it laid out like a spreadsheet.
Also, using "SSN" a PK is really bad for several reasons....

Consider a table like
Available
-------------
Available_PK (Autonumber)
SSN (Text)
FullName (Text) Note: this should really be two fields FName and LName
RR (Text)
ST(Text) ("State" is a reserved word )
FY (Text)

This is a more normalized table structure. Again, it would be better to split the name into two fields.

Import FY11 data using an append query, then update the FY field to "FY11"
Import FY12 data using an append query, then update the FY field to "FY12"
Import FY13 data using an append query, then update the FY field to "FY13"

[Dal Jeanis]

It sounds like these tables - for three different financial years, maybe? - really need to be imported and merged. ssanfu has the overall advice right on the money.