S
sheela
Hi, I have posted the following question last week. And I
have got the following response, which has worked fine,
that time.
But after entering some records to the table2, this query
doesn't work any more. It is not giving all the 81
records. It is giving more records than an inner join, but
not all the records from table1.
I have spent almost yester day and half day to day, but
didn't have any luck so far. Could some one please look
into this problem?
I really appreciate any help. Please let me know if I
didn't write my question clearly. I am using MS ACCESS
2002 version.
Thanks so much,
Sheela.
----------------------------
I am having problem with outer join. I am querying from
two tables. The table1 has only one field with values from
1 through 81. (81 rows)
It is a parameter query, once the user gives the correct
values to the query prompts, the query will result only
one record from table2.
But I need the results in 81 rows, (all the rows, from
table1, null values for the table 2 fields, for the
remaining records).
I am using the following query. This query should result
81 records, but it is giving only one record. For some
reason it is using inner join, not outer join.
What am I doing wrong here? I appreciate the help ASAP.
SELECT table1.PositionNum, table2.Field1, table2.Position
FROM Table1 LEFT JOIN table2 ON Table1.PositionNum =
table2.Position
WHERE (((table2.Field1)=[Please enter the ..]));
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You're asking for specific records in Table2. If there is
only 1 record
in table2 that fits your criteria then only one record
will be returned
by the query. If you want all 81 records from Table1 your
criteria
should be for column values in Table2 that don't match the
join column
values in Table1 (1), or have an OR clause for Table2 that
includes the
possibility that the criteria for a record will fail (2).
(1) WHERE table2.Position Is Null
[since your joining on Position/PositionNum]
(2) WHERE table2.Field1=[Please enter the ..]
OR table2.Field1 Is Null
have got the following response, which has worked fine,
that time.
But after entering some records to the table2, this query
doesn't work any more. It is not giving all the 81
records. It is giving more records than an inner join, but
not all the records from table1.
I have spent almost yester day and half day to day, but
didn't have any luck so far. Could some one please look
into this problem?
I really appreciate any help. Please let me know if I
didn't write my question clearly. I am using MS ACCESS
2002 version.
Thanks so much,
Sheela.
----------------------------
I am having problem with outer join. I am querying from
two tables. The table1 has only one field with values from
1 through 81. (81 rows)
It is a parameter query, once the user gives the correct
values to the query prompts, the query will result only
one record from table2.
But I need the results in 81 rows, (all the rows, from
table1, null values for the table 2 fields, for the
remaining records).
I am using the following query. This query should result
81 records, but it is giving only one record. For some
reason it is using inner join, not outer join.
What am I doing wrong here? I appreciate the help ASAP.
SELECT table1.PositionNum, table2.Field1, table2.Position
FROM Table1 LEFT JOIN table2 ON Table1.PositionNum =
table2.Position
WHERE (((table2.Field1)=[Please enter the ..]));
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You're asking for specific records in Table2. If there is
only 1 record
in table2 that fits your criteria then only one record
will be returned
by the query. If you want all 81 records from Table1 your
criteria
should be for column values in Table2 that don't match the
join column
values in Table1 (1), or have an OR clause for Table2 that
includes the
possibility that the criteria for a record will fail (2).
(1) WHERE table2.Position Is Null
[since your joining on Position/PositionNum]
(2) WHERE table2.Field1=[Please enter the ..]
OR table2.Field1 Is Null