Join for multiple tables?

[Etownguy]

Newbie here.
I need to join multiple tables in Access 2010, but am having trouble doing so properly. I have tried multiple ways to doing so, but have failed miserably.

An example that explains what I want to do:

TableA
Site Year


1 2009
1 2010
2 2009
2 2010
3 2009
3 2010
4 2009
4 2010


TableB
Site Year X1 X2
1 2009 0.1 A
1 2010 0.2 B
3 2009 A

TableC
Site Year X3
1 2010 1.1
2 2009 2.2
4 2009 1.3
4 2010 3.4

At a minimum, I want to join these tables on Site and Year to produce:

TableA.Site TableB.Year TableB.X1 TableB.X2 TableC.X3
1 2009 0.1 A
1 2010 0.2 B 1.1
2 2009 2.2
2 2010
3 2009 A
3 2010
4 2009 1.3
4 2010 3.4

Ideally, I want to remove rows with no data for X1, X2, or X3. and replace empty cells with a missing value code such as "NA":

TableA.Site TableB.Year TableB.X1 TableB.X2 TableC.X3
1 2009 0.1 A NA
1 2010 0.2 B 1.1
2 2009 NA NA 2.2
3 2009 NA A NA
4 2009 NA NA 1.3
4 2010 NA NA 3.4

Can anyone help? What would be the appropriate SQL code for Access?

[jzwp11]

You first have to create a query that does a left join between table A and table B. Then create another query that does a left join between table A and table C. Then create a final query that used the 2 previous queries and join by both site and year. You will need to nest the X1, X2, and X3 within separate IIF() functions to return an "NA" when the fields are null. I've attached an example.

BTW, the word "year" is a reserved word in Access, so it should not be used as a field or table name.

Additionally, your tables are not structured properly, I would guess that the tables should look more like this (based on the limited info you have presented about your application)

tblSites
-pkSiteID primary key, autonumber
-SiteNo

tblDataTypes (holds 3 records: X1, X2, X3)
-pkDataTypeID primary key, autonumber
-txtDataType

tblSiteData
-pkSiteDataID primary key, autonumber
-fkSiteID foreign key to tblSites
-fkDataTypeID foreign key to tblDataTypes
-myYear
-myValue (the actual values of X1, X2, X3 etc.)

[Etownguy]

Thanks very much for your help. I think this will solve most, if not all, of my problems. Cheers!

[jzwp11]

You're welcome; good luck with your project.