Left outer join

[Jade5]

Hello:

I have a crosstab query that has a left outer join relationship in 3 tables.
So, the first table is Corporation - left outer joined with, the second table
- institution which is left outer joined with, the third table- institution
type. This should display all the corporations and all the institutions, but
it doesn't. There is one institution that does not have an insitution type
associated with it and it is not displayed in the institution list. How can I
get this institution in the list of institutions. What am I doing wrong?
Thanks,
J.

 

[John Vinson]

Hello:

I have a crosstab query that has a left outer join relationship in 3 tables.
So, the first table is Corporation - left outer joined with, the second table
- institution which is left outer joined with, the third table- institution
type. This should display all the corporations and all the institutions, but
it doesn't. There is one institution that does not have an insitution type
associated with it and it is not displayed in the institution list. How can I
get this institution in the list of institutions. What am I doing wrong?
Thanks,
J.

Please post the SQL of the query. Do you have a criterion on any field
of the Institution Type table? If so it will insist on there being a
non-NULL value (e.g. the same as an Inner Join) unless you include OR
IS NULL in the criteria.

John W. Vinson[MVP]

 

[Jade5]

Yes, I did have a Not Null criteria. I put it there to avoid the <> column
name in the Institution list. I have used 0; in the column width field of the
properties dialog box to hide the <> column instead. Do you forsee any
problems with that?

Also, I have a query that counts the insitutions using Count. How do I
exclude insitutions that have no type and/or institutions that have a closed
date in another table called History.

SQL statement

SELECT Count([Institution table].[Institution ID]) AS [CountOfInstitution
ID], [Institution table].[P/T]
FROM [Corporation table] INNER JOIN [Institution table] ON [Corporation
table].[Corporation ID] = [Institution table].[Corporation ID]
GROUP BY [Institution table].[P/T];

Thanks,
J

 

[John Vinson]

Yes, I did have a Not Null criteria. I put it there to avoid the <> column
name in the Institution list. I have used 0; in the column width field of the
properties dialog box to hide the <> column instead. Do you forsee any
problems with that?

Yes, you will certainly have a problem. ANY criterion other than one
including

OR IS NULL

will compare the value in the field to the criterion. If the value in
the field is NULL - which it will be, if there is no record matching
the Join - then that comparison will return NULL, and the record will
be rejected and not displayed. This applies to equality, inequality,
*ANY* criterion other than that one specific one: IS NULL.

Also, I have a query that counts the insitutions using Count. How do I
exclude insitutions that have no type and/or institutions that have a closed
date in another table called History.

SQL statement

SELECT Count([Institution table].[Institution ID]) AS [CountOfInstitution
ID], [Institution table].[P/T]
FROM [Corporation table] INNER JOIN [Institution table] ON [Corporation
table].[Corporation ID] = [Institution table].[Corporation ID]
GROUP BY [Institution table].[P/T];

Ummm... this query doesn't reference a History table, and I have no
idea what your History table looks like or what it's related to. I
really don't understand the question. An INNER JOIN to the Type table
will exclude all records with no Type, or you could just use a
criterion

IS NOT NULL

on the Type field (assuming there is a Type field, which I also don't
see).

John W. Vinson[MVP]

 

[Jade5]

Hi John,
Have a look at this SQL statement

SELECT [Institution table].[Institution ID] AS [CountOfInstitution ID1],
[Institution table].[P/T]
FROM [Institution table] INNER JOIN [Institution type table] ON [Institution
table].[Institution ID] = [Institution type table].[Institution ID]
GROUP BY [Institution table].[Institution ID], [Institution table].[P/T];

This produces the correct result. It excludes institutions that do not have
an institution type. However, I want a count of the institutions by province
so

ON 1
NU 2
etc
When I put Count in the Total field of the query, I get incorrect results.
Thanks,
J.

Yes, I did have a Not Null criteria. I put it there to avoid the <> column
name in the Institution list. I have used 0; in the column width field of the
properties dialog box to hide the <> column instead. Do you forsee any
problems with that?

Yes, you will certainly have a problem. ANY criterion other than one
including

OR IS NULL

will compare the value in the field to the criterion. If the value in
the field is NULL - which it will be, if there is no record matching
the Join - then that comparison will return NULL, and the record will
be rejected and not displayed. This applies to equality, inequality,
*ANY* criterion other than that one specific one: IS NULL.

Also, I have a query that counts the insitutions using Count. How do I
exclude insitutions that have no type and/or institutions that have a closed
date in another table called History.

SQL statement

SELECT Count([Institution table].[Institution ID]) AS [CountOfInstitution
ID], [Institution table].[P/T]
FROM [Corporation table] INNER JOIN [Institution table] ON [Corporation
table].[Corporation ID] = [Institution table].[Corporation ID]
GROUP BY [Institution table].[P/T];

Ummm... this query doesn't reference a History table, and I have no
idea what your History table looks like or what it's related to. I
really don't understand the question. An INNER JOIN to the Type table
will exclude all records with no Type, or you could just use a
criterion

IS NOT NULL

on the Type field (assuming there is a Type field, which I also don't
see).

John W. Vinson[MVP]

 

[John Vinson]

Hi John,
Have a look at this SQL statement

SELECT [Institution table].[Institution ID] AS [CountOfInstitution ID1],
[Institution table].[P/T]
FROM [Institution table] INNER JOIN [Institution type table] ON [Institution
table].[Institution ID] = [Institution type table].[Institution ID]
GROUP BY [Institution table].[Institution ID], [Institution table].[P/T];

This produces the correct result. It excludes institutions that do not have
an institution type. However, I want a count of the institutions by province
so

ON 1
NU 2
etc
When I put Count in the Total field of the query, I get incorrect results.
Thanks,
J.

I don't understand.

Do you want to exclude institutions with no Type, or not?

How are the Institution Table and the Institution Type Table related?
It looks backwards! Does one institution have many Types, but each
Type has only one Institution? That's what appears to be the case from
your join clause.

And, you say it gets the correct result and that it gets incorrect
results. Please explain.

John W. Vinson[MVP]

 

[Jade5]

Hi John:
Yes, I want to exclude institutions that have not type. There is a 1 to many
relationship between institutions and types. The SQL statement below produces
the correct result in the sense that it returns all the instutions that have
no type. So, if the table has 4 institutions with IDs 1234, 1235, 1236 and
1237 and all instutions are in ON except 1234 which is in NU and 1237 has no
type then thel result would be which is correct.

Inst ID Province
1234 NU
1235 ON
1236 ON
If I want to ouput the Province and the number of Institutions for the
province how do I do that?
Output would be
Province Number of institutions
NU 1
ON 2

Right now in design view I have the Institution ID field and the Province
field and I am grouping by Province to get the 1st results above. I tried
putting Count in the total field of the Insitution ID in query view I am not
getting the correct totals.

Hope that helps.
Thanks,
J.

Hi John,
Have a look at this SQL statement

SELECT [Institution table].[Institution ID] AS [CountOfInstitution ID1],
[Institution table].[P/T]
FROM [Institution table] INNER JOIN [Institution type table] ON [Institution
table].[Institution ID] = [Institution type table].[Institution ID]
GROUP BY [Institution table].[Institution ID], [Institution table].[P/T];

This produces the correct result. It excludes institutions that do not have
an institution type. However, I want a count of the institutions by province
so

ON 1
NU 2
etc
When I put Count in the Total field of the query, I get incorrect results.
Thanks,
J.

I don't understand.

Do you want to exclude institutions with no Type, or not?

How are the Institution Table and the Institution Type Table related?
It looks backwards! Does one institution have many Types, but each
Type has only one Institution? That's what appears to be the case from
your join clause.

And, you say it gets the correct result and that it gets incorrect
results. Please explain.

John W. Vinson[MVP]

 

[Jade5]

Hi John:
Yes, I want to exclude institutions that have no type. There is a 1 to many
relationship between institutions and types. The SQL statement below produces
the correct result in the sense that it returns all the instutions that have
no type. So, if the table has 4 institutions with IDs 1234, 1235, 1236 and
1237 and all instutions are in ON except 1234 which is in NU and 1237 has no
type then the result would be.

Inst ID Province
1234 NU
1235 ON
1236 ON

This is correct.

If I want to ouput the Province and the number of Institutions for the
province how do I do that?
Output would be
Province Number of institutions
NU 1
ON 2

Right now in design view I have the Institution ID field and the Province
field and I am grouping by Province to get the 1st results above. I tried
putting Count in the total field of the Insitution ID in query view I am not
getting the correct totals.

Hope that helps.
Thanks,
J.

Hi John,
Have a look at this SQL statement

SELECT [Institution table].[Institution ID] AS [CountOfInstitution ID1],
[Institution table].[P/T]
FROM [Institution table] INNER JOIN [Institution type table] ON [Institution
table].[Institution ID] = [Institution type table].[Institution ID]
GROUP BY [Institution table].[Institution ID], [Institution table].[P/T];

This produces the correct result. It excludes institutions that do not have
an institution type. However, I want a count of the institutions by province
so

ON 1
NU 2
etc
When I put Count in the Total field of the query, I get incorrect results.
Thanks,
J.

I don't understand.

Do you want to exclude institutions with no Type, or not?

How are the Institution Table and the Institution Type Table related?
It looks backwards! Does one institution have many Types, but each
Type has only one Institution? That's what appears to be the case from
your join clause.

And, you say it gets the correct result and that it gets incorrect
results. Please explain.

John W. Vinson[MVP]

 

[Jade5]

aaaaghh, ok this one has all the corrections.



Hi John:
Yes, I want to exclude institutions that have no type. There is a 1 to many
relationship between institutions and types. The SQL statement below
produces the correct result in the sense that it returns all the instutions
that have
no type. So, if the table has 4 institutions with IDs 1234, 1235, 1236 and
1237 and all instutions are in ON except 1234 which is in NU and 1237 has no
type then the result would be.

Inst ID Province
1234 NU
1235 ON
1236 ON
This is correct.
If I want to ouput the Province and the number of Institutions for the
province how do I do that?
Output would be
Province Number of institutions
NU 1
ON 2

Right now in design view I have the Institution ID field and the Province
field and I am grouping by Province to get the 1st results above. I tried
putting Count in the total field of the Insitution ID in query view I am not
getting the correct totals.

Hope that helps.
Thanks,
J.

Hi John,
Have a look at this SQL statement

SELECT [Institution table].[Institution ID] AS [CountOfInstitution ID1],
[Institution table].[P/T]
FROM [Institution table] INNER JOIN [Institution type table] ON [Institution
table].[Institution ID] = [Institution type table].[Institution ID]
GROUP BY [Institution table].[Institution ID], [Institution table].[P/T];

This produces the correct result. It excludes institutions that do not have
an institution type. However, I want a count of the institutions by province
so

ON 1
NU 2
etc
When I put Count in the Total field of the query, I get incorrect results.
Thanks,
J.

I don't understand.

Do you want to exclude institutions with no Type, or not?

How are the Institution Table and the Institution Type Table related?
It looks backwards! Does one institution have many Types, but each
Type has only one Institution? That's what appears to be the case from
your join clause.

And, you say it gets the correct result and that it gets incorrect
results. Please explain.

John W. Vinson[MVP]

 

[John Vinson]

Right now in design view I have the Institution ID field and the Province
field and I am grouping by Province to get the 1st results above. I tried
putting Count in the total field of the Insitution ID in query view I am not
getting the correct totals.

Please post the SQL of the query and indicate what the correct and
incorrect totals might be.

Do you have perhaps more than one Type for each institution, in some
cases??

John W. Vinson[MVP]

 

[Jade5]

Hi John:
I was using the type field in the query instead of an expression so I was
getting the wrong values. I am now using an expression and the Is Not Null
criterion you suggested and it is fine now. Thanks for your help.
J.