Dropping duplicate records based on content

[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

 

[KARL DEWEY]

From your narrative I do not see how 5 & 6 are to be returned. Row 5 & 6 has
duplicate process_id numbers and have no Item_B field present. These queries
do not return them.
Chip_1 ---
SELECT [Table 2].Process_id, [Table 2].Company_id, Count([Table
2].Process_id) AS CountOfProcess_id
FROM [Table 2]
GROUP BY [Table 2].Process_id, [Table 2].Company_id
HAVING (((Count([Table 2].Process_id))<2));

SELECT [Table 1].Process_id, [Table 1].Company_id, [Table 2].Item_A, [Table
2].Item_B, [Table 3].Description
FROM ([Table 1] LEFT JOIN Chip_1 ON ([Table 1].Company_id =
Chip_1.Company_id) AND ([Table 1].Process_id = Chip_1.Process_id)) LEFT JOIN
([Table 2] LEFT JOIN [Table 3] ON [Table 2].Item_B = [Table 3].Item_No) ON
([Table 1].Company_id = [Table 2].Company_id) AND ([Table 1].Process_id =
[Table 2].Process_id)
WHERE ((([Table 2].Process_id)=[Chip_1].[Process_id]) AND (([Table
2].Company_id)=[Chip_1].[Company_id]) AND (([Table 2].Item_B) Is Not Null))
OR ((([Table 2].Process_id)=[Chip_1].[Process_id]) AND (([Table
2].Company_id)=[Chip_1].[Company_id]) AND (([Table 2].Item_A) Is Not Null))
OR ((([Table 1].Process_id) Like "*") AND (([Table 2].Item_B) Is Not Null))
ORDER BY [Table 2].Process_id;

 

[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

The solution involves doing a separate "in (select...)" statement that
essentially does a find dups query to drop out the unwanted duplicate
row and performs another that finds all rows that do not have
duplicates. This is a logical OR function within the query (use two
lines). By limiting the searches to the desired key field the query is
very fast. It can be done and with one query!