full join in access query

Aiysha
Aiysha used Ask the Experts™
How do I implement full join in access. I want to see data from both table joined by two fields.

SELECT SWD_NDIC.*, SWD_SCADA_Daily.*, SWD_NDIC.[Well Name]
FROM SWD_NDIC full JOIN SWD_SCADA_Daily ON (SWD_NDIC.[Well Name] = SWD_SCADA_Daily.[Well Name]) AND (SWD_NDIC.Date = SWD_SCADA_Daily.[Time/Date])
WHERE (((SWD_SCADA_Daily.[Well Name])="Test") AND ((SWD_NDIC.[Well Name])="Test"));
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Anders Ebro (Microsoft MVP)Microsoft Developer

Commented:
I suggest you take a look at this tutorial on a workaround for a full outer join in access:
Access Full Outer Join
Software & Systems Engineer
Commented: 
SELECT SWD_NDIC.*
	,SWD_SCADA_Daily.*
	,SWD_NDIC.[Well Name]
FROM SWD_NDIC
LEFT JOIN SWD_SCADA_Daily ON (SWD_NDIC.[Well Name] = SWD_SCADA_Daily.[Well Name])
	AND (SWD_NDIC.DATE = SWD_SCADA_Daily.[Time/Date])
WHERE (
		((SWD_SCADA_Daily.[Well Name]) = "Test")
		AND ((SWD_NDIC.[Well Name]) = "Test")
		)
UNION
SELECT SWD_NDIC.*
	,SWD_SCADA_Daily.*
	,SWD_NDIC.[Well Name]
FROM SWD_NDIC
RIGHT JOIN SWD_SCADA_Daily ON (SWD_NDIC.[Well Name] = SWD_SCADA_Daily.[Well Name])
	AND (SWD_NDIC.DATE = SWD_SCADA_Daily.[Time/Date])
WHERE (
		((SWD_SCADA_Daily.[Well Name]) = "Test")
		AND ((SWD_NDIC.[Well Name]) = "Test")
		);

Open in new window

Aiysha

Author

Commented:
John,

Thank you so much for the details. Actually following worked for me:

SELECT SWD_NDIC.*, SWD_SCADA_Daily.*
FROM SWD_NDIC RIGHT JOIN SWD_SCADA_Daily ON (SWD_NDIC.[Well Name] = SWD_SCADA_Daily.[Well Name]) AND (SWD_NDIC.Date = SWD_SCADA_Daily.[Time/Date])
UNION SELECT SWD_NDIC.*, SWD_SCADA_Daily.*
FROM SWD_NDIC left JOIN SWD_SCADA_Daily ON (SWD_NDIC.[Well Name] = SWD_SCADA_Daily.[Well Name]) AND (SWD_NDIC.Date = SWD_SCADA_Daily.[Time/Date])
where swd_ndic.[Well NAme]="test" and SWD_SCADA_Daily.[Well Name]= "test";

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial