C
Chip
I have a database with 3 tables that need to be part of a query. TABLE
1 has company id and process id fields. These two fields are used to
find records in TABLE 2 which is the process table. This is a one to
many relationship.
TABLE 2 has process id, company id, Item A and Item B fields.
TABLE3 has an item key and description. This table is used to find
TABLE 2 Item B descriptions.
TABLE 1 Records are:
Row# Process_id Company_id
-------- --------------- -----------------
1 173 222
2 1015 222
3 1180 222
4 1200 222
TABLE 2 Records are:
Row# Process_id Company_id Item_A Item_B
-------- --------------- ----------------- ---------
----------
1 173 222 111 null
2 1015 222 222 null
3 1015 222 null 333
4 1180 222 444 null
5 1200 222 666 null
6 1200 222 777 null
TABLE 3 Records are:
Row# Item_No Description
-------- ---------- -----------------------------
1 333 Item 333 description
2 555 Item 555 description
The query does an inner join on TABLES 1 and 2 on company_id and
process_id fields. It also has a left join on TABLE 2 Item_B and TABLE
3 Item_No fields
When I run the query it returns all 6 records along with the
Description field from TABLE 3 as for Row 3 records
This is good.
But I want to exclude records where TABLE 2 has duplicate process_id
numbers and only use the record that has the Item_B field key present
(not nulll). So in TABLE 2 above I want to drop Row 2 record and
accept the Row 3 record.
Records that are not duplicate (on process_id) but have either Item_A
value or Item_B value should be selected. Rows 1, 4, 5, 6 are to be
returned.
If there is a duplicate process_id pair and neither has an Item_B then
both should be selected. If Rows 5 and 6 are returned as although they
have duplicate process_id's, neither has Item_B values.
Bottom line, I want TABLE 2 Rows 1,3,4,5,6 to be the final result
set.
Any suggestions? Can this be accomplished with a query or must code be
written to make this happen? The query is used to populate a combo box
that displays 2 columns. The second column is to be blank for records
that don't have an Item_B value.
Thanks
1 has company id and process id fields. These two fields are used to
find records in TABLE 2 which is the process table. This is a one to
many relationship.
TABLE 2 has process id, company id, Item A and Item B fields.
TABLE3 has an item key and description. This table is used to find
TABLE 2 Item B descriptions.
TABLE 1 Records are:
Row# Process_id Company_id
-------- --------------- -----------------
1 173 222
2 1015 222
3 1180 222
4 1200 222
TABLE 2 Records are:
Row# Process_id Company_id Item_A Item_B
-------- --------------- ----------------- ---------
----------
1 173 222 111 null
2 1015 222 222 null
3 1015 222 null 333
4 1180 222 444 null
5 1200 222 666 null
6 1200 222 777 null
TABLE 3 Records are:
Row# Item_No Description
-------- ---------- -----------------------------
1 333 Item 333 description
2 555 Item 555 description
The query does an inner join on TABLES 1 and 2 on company_id and
process_id fields. It also has a left join on TABLE 2 Item_B and TABLE
3 Item_No fields
When I run the query it returns all 6 records along with the
Description field from TABLE 3 as for Row 3 records
This is good.
But I want to exclude records where TABLE 2 has duplicate process_id
numbers and only use the record that has the Item_B field key present
(not nulll). So in TABLE 2 above I want to drop Row 2 record and
accept the Row 3 record.
Records that are not duplicate (on process_id) but have either Item_A
value or Item_B value should be selected. Rows 1, 4, 5, 6 are to be
returned.
If there is a duplicate process_id pair and neither has an Item_B then
both should be selected. If Rows 5 and 6 are returned as although they
have duplicate process_id's, neither has Item_B values.
Bottom line, I want TABLE 2 Rows 1,3,4,5,6 to be the final result
set.
Any suggestions? Can this be accomplished with a query or must code be
written to make this happen? The query is used to populate a combo box
that displays 2 columns. The second column is to be blank for records
that don't have an Item_B value.
Thanks