|
 |
access thread: Query problem (left join)
Message #1 by "wambui njoroge" <kagdw189@b...> on Fri, 25 Oct 2002 02:49:21
|
|
Hi all,
I have a form based on a query. I have two queries with identical fields
but one has a left join. If I put the recordsource of the form to be the
query with the left join, I get a blank form. The query with the inner join
works fine, i.e. shows all records. Does anybody know what the problem is?
Thanks.
Wambui
Message #2 by "Carnley, Dave" <dcarnley@a...> on Fri, 25 Oct 2002 09:35:57 -0500
|
|
Look at the query datasheet views and compare them. Does the left-join
query have any rows that have lots of null fields? This may be appropriate
behavior by Access depending on your data.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Thursday, October 24, 2002 9:49 PM
To: Access
Subject: [access] Query problem (left join)
Hi all,
I have a form based on a query. I have two queries with identical fields
but one has a left join. If I put the recordsource of the form to be the
query with the left join, I get a blank form. The query with the inner join
works fine, i.e. shows all records. Does anybody know what the problem is?
Thanks.
Wambui
Message #3 by "wambui njoroge" <kagdw189@b...> on Fri, 25 Oct 2002 17:37:35
|
|
You're right. The query with the left join has many null fields. Is there
a way around this problem?
Wambui
> Look at the query datasheet views and compare them. Does the left-join
query have any rows that have lots of null fields? This may be appropriate
behavior by Access depending on your data.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Thursday, October 24, 2002 9:49 PM
To: Access
Subject: [access] Query problem (left join)
Hi all,
I have a form based on a query. I have two queries with identical fields
but one has a left join. If I put the recordsource of the form to be the
query with the left join, I get a blank form. The query with the inner join
works fine, i.e. shows all records. Does anybody know what the problem is?
Thanks.
Wambui
Message #4 by "Carnley, Dave" <dcarnley@a...> on Fri, 25 Oct 2002 12:09:17 -0500
|
|
No, not really, and its probably not even really a "problem" exactly... A
left join (or any Outer Join) is intended to work that way. Lets say you
have a query that joins a table called FATHERS with a table called SONS,
retrieving values from both tables into one record with a left join on
FamilyID : FamilyID, FatherName, SonName, SonBirthday.
For any matching pair of records in FATHER and SON you will see a result set
record with all the fields populated. If there were a FATHER record that
had no matching SON record, you will still get a record in the result set
for that FATHER record, but the SON fields will be blank. All FATHER
records will be accounted for in the result set.
In a typical join (inner join), in the same situation, FATHERS who had no
SONS would not appear at all in the recordset. If a FATHER had no matching
SONS then that FATHER is not included in the query results at all.
So you need to determine which type of join is appropriate for your
application.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Friday, October 25, 2002 12:38 PM
To: Access
Subject: [access] RE: Query problem (left join)
You're right. The query with the left join has many null fields. Is there
a way around this problem?
Wambui
> Look at the query datasheet views and compare them. Does the left-join
query have any rows that have lots of null fields? This may be appropriate
behavior by Access depending on your data.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Thursday, October 24, 2002 9:49 PM
To: Access
Subject: [access] Query problem (left join)
Hi all,
I have a form based on a query. I have two queries with identical fields
but one has a left join. If I put the recordsource of the form to be the
query with the left join, I get a blank form. The query with the inner join
works fine, i.e. shows all records. Does anybody know what the problem is?
Thanks.
Wambui
Message #5 by "wambui njoroge" <kagdw189@b...> on Fri, 25 Oct 2002 20:03:07
|
|
I am working on student database. I have applicants information in one
table. Sometimes, an applicant is assigned an advisor immediately and this
info is put in the advisor table. I need to go back to the listing of all
applicants and see who has not been assigned an advisor, that's why I need
the left join for the applicants table and the advisor table.
Any more ideas?
> No, not really, and its probably not even really a "problem" exactly...
A
left join (or any Outer Join) is intended to work that way. Lets say you
have a query that joins a table called FATHERS with a table called SONS,
retrieving values from both tables into one record with a left join on
FamilyID : FamilyID, FatherName, SonName, SonBirthday.
For any matching pair of records in FATHER and SON you will see a result
set
record with all the fields populated. If there were a FATHER record that
had no matching SON record, you will still get a record in the result set
for that FATHER record, but the SON fields will be blank. All FATHER
records will be accounted for in the result set.
In a typical join (inner join), in the same situation, FATHERS who had no
SONS would not appear at all in the recordset. If a FATHER had no matching
SONS then that FATHER is not included in the query results at all.
So you need to determine which type of join is appropriate for your
application.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Friday, October 25, 2002 12:38 PM
To: Access
Subject: [access] RE: Query problem (left join)
You're right. The query with the left join has many null fields. Is there
a way around this problem?
Wambui
> Look at the query datasheet views and compare them. Does the left-join
query have any rows that have lots of null fields? This may be appropriate
behavior by Access depending on your data.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Thursday, October 24, 2002 9:49 PM
To: Access
Subject: [access] Query problem (left join)
Hi all,
I have a form based on a query. I have two queries with identical fields
but one has a left join. If I put the recordsource of the form to be the
query with the left join, I get a blank form. The query with the inner join
works fine, i.e. shows all records. Does anybody know what the problem is?
Thanks.
Wambui
Message #6 by "Carnley, Dave" <dcarnley@a...> on Fri, 25 Oct 2002 14:08:17 -0500
|
|
this is a great example of a situation where an outer join is applicable,
you just need to decide how you want your application to behave when a
student has no advisor - it's a question of design choices, and pleasing
your users.
if you are looking for students who do not have advisors, you could use the
"find unmatched query wizard" to build a query that locates just those
records.
if you include student information on the form (and in the query of course)
then those fields will appear and the advisor fields will be blank, this
might look less like an error. Again, it depends on how you use the forms
and what data is on them.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Friday, October 25, 2002 3:03 PM
To: Access
Subject: [access] RE: Query problem (left join)
I am working on student database. I have applicants information in one
table. Sometimes, an applicant is assigned an advisor immediately and this
info is put in the advisor table. I need to go back to the listing of all
applicants and see who has not been assigned an advisor, that's why I need
the left join for the applicants table and the advisor table.
Any more ideas?
> No, not really, and its probably not even really a "problem" exactly...
A
left join (or any Outer Join) is intended to work that way. Lets say you
have a query that joins a table called FATHERS with a table called SONS,
retrieving values from both tables into one record with a left join on
FamilyID : FamilyID, FatherName, SonName, SonBirthday.
For any matching pair of records in FATHER and SON you will see a result
set
record with all the fields populated. If there were a FATHER record that
had no matching SON record, you will still get a record in the result set
for that FATHER record, but the SON fields will be blank. All FATHER
records will be accounted for in the result set.
In a typical join (inner join), in the same situation, FATHERS who had no
SONS would not appear at all in the recordset. If a FATHER had no matching
SONS then that FATHER is not included in the query results at all.
So you need to determine which type of join is appropriate for your
application.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Friday, October 25, 2002 12:38 PM
To: Access
Subject: [access] RE: Query problem (left join)
You're right. The query with the left join has many null fields. Is there
a way around this problem?
Wambui
> Look at the query datasheet views and compare them. Does the left-join
query have any rows that have lots of null fields? This may be appropriate
behavior by Access depending on your data.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Thursday, October 24, 2002 9:49 PM
To: Access
Subject: [access] Query problem (left join)
Hi all,
I have a form based on a query. I have two queries with identical fields
but one has a left join. If I put the recordsource of the form to be the
query with the left join, I get a blank form. The query with the inner join
works fine, i.e. shows all records. Does anybody know what the problem is?
Thanks.
Wambui
Message #7 by "wambui njoroge" <kagdw189@b...> on Fri, 25 Oct 2002 20:52:39
|
|
I have other fields on the form too but the records still do not appear.
I'll try the query wizard and see how that works.
Thanks.
> this is a great example of a situation where an outer join is applicable,
you just need to decide how you want your application to behave when a
student has no advisor - it's a question of design choices, and pleasing
your users.
if you are looking for students who do not have advisors, you could use the
"find unmatched query wizard" to build a query that locates just those
records.
if you include student information on the form (and in the query of course)
then those fields will appear and the advisor fields will be blank, this
might look less like an error. Again, it depends on how you use the forms
and what data is on them.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Friday, October 25, 2002 3:03 PM
To: Access
Subject: [access] RE: Query problem (left join)
I am working on student database. I have applicants information in one
table. Sometimes, an applicant is assigned an advisor immediately and this
info is put in the advisor table. I need to go back to the listing of all
applicants and see who has not been assigned an advisor, that's why I need
the left join for the applicants table and the advisor table.
Any more ideas?
> No, not really, and its probably not even really a "problem" exactly...
A
left join (or any Outer Join) is intended to work that way. Lets say you
have a query that joins a table called FATHERS with a table called SONS,
retrieving values from both tables into one record with a left join on
FamilyID : FamilyID, FatherName, SonName, SonBirthday.
For any matching pair of records in FATHER and SON you will see a result
set
record with all the fields populated. If there were a FATHER record that
had no matching SON record, you will still get a record in the result set
for that FATHER record, but the SON fields will be blank. All FATHER
records will be accounted for in the result set.
In a typical join (inner join), in the same situation, FATHERS who had no
SONS would not appear at all in the recordset. If a FATHER had no matching
SONS then that FATHER is not included in the query results at all.
So you need to determine which type of join is appropriate for your
application.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Friday, October 25, 2002 12:38 PM
To: Access
Subject: [access] RE: Query problem (left join)
You're right. The query with the left join has many null fields. Is there
a way around this problem?
Wambui
> Look at the query datasheet views and compare them. Does the left-join
query have any rows that have lots of null fields? This may be appropriate
behavior by Access depending on your data.
-----Original Message-----
From: wambui njoroge [mailto:kagdw189@b...]
Sent: Thursday, October 24, 2002 9:49 PM
To: Access
Subject: [access] Query problem (left join)
Hi all,
I have a form based on a query. I have two queries with identical fields
but one has a left join. If I put the recordsource of the form to be the
query with the left join, I get a blank form. The query with the inner join
works fine, i.e. shows all records. Does anybody know what the problem is?
Thanks.
Wambui
|
|
|
View Cart
