Finding duplicate information within a record

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

All,

I have a table with the following info:

Mon Tue Wed Thur
1 1 1 2 3
2 2 3 4 5
3 5 3 1 3
4 1 1 3 2
5 1 1 2 3

I need a query which will show those records in which the
value in the days are the same. The query would produce
on the above table:


Mon Tue Wed Thur
1 1 1 2 3 ( Mon and Tues are same)
3 5 3 1 3 ( Tue and Thur are same)
4 1 1 3 2 ( Mon and Tues are same)
5 1 1 2 3 ( Mon and Tues are same)


I have tried the Find Duplicates Query, but that gives me
the entire records that are the same. That Query produces:

Mon Tue Wed Thur
1 1 1 2 3
5 1 1 2 3

Not what I need.

Any ideas or suggestions?

thank you,
Tom
 
Is it possible to change/normalize your table structure? Mon, Tue,
Wed,...etc are data values not field names. A better structure would be

ID DOW Qty
1 2 1
1 3 1
1 4 2
1 5 3

each value/qty is recorded in its own record.
 
I have a table with the following info:

Mon Tue Wed Thur
1 1 1 2 3
2 2 3 4 5
3 5 3 1 3
4 1 1 3 2
5 1 1 2 3
Click to expand...

You have a non-normalized table. This would be a lot easier with a
"tall thin" design, in which you do not store data in fieldnames:

ID Day Value
1 Mon 1
1 Tue 1
1 Wed 2
1 Thu 3
2 Mon 2
2 Tue 2

and so on.

That said...
I need a query which will show those records in which the
value in the days are the same.
Click to expand...

To get any record where any day's value is repeated, paste this SQL
into the SQL view of a new query:

SELECT * FROM yourtable
WHERE Mon = Tue OR Mon = Wed OR Mon = Thu OR Tue = Wed OR Tue = Thu OR
Wed = Thu;
 
Thank you for your replies. The SQL query that John
provided did the trick.

I will try to redo my tables as you both have suggested.

Thank you.
Tom
 
Back
Top