how do i eliminate duplicate rows?

[dnstapes]

I'm wrting a query to combine two tables, and I need a way to eliminate
redundant entries. Both tables have the same primary key and the rest
of the columns are also the same. The problem that I'm having is that it's possible for both tables to have the same value for the primary key, and sometimes I want the value from table A and other times I want the value from table B. The value
that I need is the row that has the most values filled in. For example,
say my table contains the following columns: Part (primary key), team,
process, date. Each table can have the same value for part, let's call
the value "part 1". The row from table A could have a value for part,
process, and date, but not team. The row from table B could have just
part and process filled in. In this case, I would want the row from
table A, because it has more columns filled in.

Any ideas? I know this has to be a problem that other people have had.

Thanks,
Dana

 

[Maquis]

Hmmm, how about using an update query before you combine the tables. The update query would join Table A to Table B on the primary key and could update table A with table B's info if the field in table A is empty.

After running this query you are assured that for all your duplicate entries Table A has the most info. Therefore you can take all your info from table A and only pull the entries from table B that aren't in table A. Maq
<insert witty signature here>

 

[ldandy]

You could use the IIF function for one or more columns in your query, like this:

IIF(TABLE_A_COLUMN IS NULL, TABLE_B_COLUMN, TABLE_A_COLUMN) AS YourCombinedColumn