Prev: UPDATE records by copying from same table?
Next: Averaging Hourly Data
From: Elaine_Moore on 17 May 2010 18:05
I have a table the has information about grant applications. I have another
table that has the names and addresses of the schools related to the
applications. When I do a querry it either shows only one record or all of
the information form one or the other depending on whetner I click 2 or 3 in
the properties window. How do I get it to show all of the records from all of
the columns that I have chosen from both tables?

From: PieterLinden via AccessMonster.com on 17 May 2010 19:12
Elaine_Moore wrote:
>I have a table the has information about grant applications. I have another
>table that has the names and addresses of the schools related to the
>applications. When I do a querry it either shows only one record or all of
>the information form one or the other depending on whetner I click 2 or 3 in
>the properties window. How do I get it to show all of the records from all of
>the columns that I have chosen from both tables?

Full outer joins require basically unioning a left join and a right join.
e.g.

SELECT <fieldlist>
FROM a LEFT JOIN b
ON a.fieldx = b.fieldy
UNION ALL
SELECT <fieldlist>
FROM a RIGHT JOIN b
ON a.fieldx = b.fieldy

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1

From: Elaine_Moore via AccessMonster.com on 18 May 2010 14:28
Hi Pieter,

Here is the SQL for my query. I am not sure what you are saying it should
look like. I am a beninner here, so please be patient with my ignorance.

SELECT tblM2010Excel.FileNumber, tblM2010Excel.[Teacher Name],
tblSchoolsExcel.ID, tblSchoolsExcel.SchoolName, tblSchoolsExcel.StreetAddress,
tblSchoolsExcel.City, tblSchoolsExcel.Province, tblSchoolsExcel.PostalCode
FROM tblSchoolsExcel INNER JOIN tblM2010Excel ON tblSchoolsExcel.[SchoolName]
= tblM2010Excel.[School Name];

PieterLinden wrote:
>>I have a table the has information about grant applications. I have another
>>table that has the names and addresses of the schools related to the
>>applications. When I do a querry it either shows only one record or all of
>>the information form one or the other depending on whetner I click 2 or 3 in
>>the properties window. How do I get it to show all of the records from all of
>>the columns that I have chosen from both tables?
>
>Full outer joins require basically unioning a left join and a right join.
>e.g.
>
>SELECT <fieldlist>
>FROM a LEFT JOIN b
>ON a.fieldx = b.fieldy
>UNION ALL
>SELECT <fieldlist>
>FROM a RIGHT JOIN b
>ON a.fieldx = b.fieldy

--
Message posted via http://www.accessmonster.com

 | 
Pages: 1
Prev: UPDATE records by copying from same table?
Next: Averaging Hourly Data