Finding duplicate records in multiple queries/reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK, here is a somewhat difficult situation for me.

I have a table, call it "TABLE.INPUT" that consists of several hundred
records to which I apply multiple queries and reports, call them "RED_Q",
"BLUE_Q", "WHITE_Q", and "GREEN_Q." Here is a sample of the Table:

NAME COLOR_1 COLOR_2 COLOR_3
John Red Orange Yellow
Bill Aqua White Silver
Ann Blue Red Orange
Arthur Gold Silver Maroon
Agnes Green Blue Silver
.. . .

RED_Q results in John and Ann being seleted
BLUE_Q results in Ann and Agnes being selected
WHITE_Q results in Bill being selected
GREEN_Q results in Agnes being selected

Ann and Agnes are selected in two of the queries (Agnes in GREEN_Q and
BLUE_Q; and Ann in RED_Q and BLUE_Q)

I would like to compare the queries or reports so that Ann and Agnes only
show up in one place...preferably the lower color number. So Ann would be
in the BLUE_Q (since COLOR_1 is blue for Ann) but not in the RED_Q; and
Agnes would be in the GREEN_Q (since COLOR_1 is green for Agnes) and not in
the BLUE_Q.

Is this possible to do, either in a report comparison or a query comparison?
I hope I explained the problem well enough.
 
Hi,


First, normalize your table, or make a query that does:


SELECT [Name] As theName, Color_1 AS Color, 1 As Choice FROM tableName
UNION ALL
SELECT [Name], Color_2, 2 FROM tablename
UNION ALL
SELECT [Name], Color_3, 3 FROM tablename


I assume that table is Q1. Next, using technique 3 from
http://www.mvps.org/access/queries/qry0020.htm


SELECT Q.theName, First(Q.Color)
FROM Q1 As Q INNER JOIN Q1 As T
ON Q.theName=T.theName
GROUP BY Q.theName, Q.Color
HAVING Q.Choice= Min(T.Choice)



should return the desired result (you can use any of the four techniques
presented in the given reference, I just used the less known one, but being
possible to generate it entirely "graphically", in the query designer ).




Hoping it may help,
Vanderghast, Access MVP

message
news:YW50aWdvbmU=.96cc6b46b715958067cfb76e5ba813b1@1062133033.cotse.net...
 
Back
Top