Compare several hundred rows against other rows

[ErikRP]

Not sure if what I want to do is possible, or at least possible the way things are set up.

I have a massive table - c. 6 million rows. It contains data along these lines:

Plan#, Item, Price, Description, Colour, Value, Location, etc.

The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).

To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.

Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).

I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.

Any help or suggestions would be much appreciated.

Thanks!!!

 

[Len Boorman]

If I have understood you correctly
Try a query along these lines.

Select all fields and then select the last field again.

make it a Summation query by selecting the sigma from the menu bar

On the field you selected twice set the summation to count and criteria to >1

This will find all rows that are precise duplications and tell you how many duplications there are.

If not exactly what you want it may give you some clues how to extract the data you are looking for

Len

 

[ErikRP]

I must be missing something or doing something wrong - I'm not getting any results back. Here's what I have:

SELECT Plan, Item, Price, Description, Colour, Value, Warn1, Warn2, Count(Warn2) AS Expr1
FROM Main
GROUP BY Plan, Item, Price, Description, Colour, Value, Warn1, Warn2
HAVING (Count(Warn2)>1);

I changed the "SUM" to "COUNT" as the last field (as are all the fields, text fields). Would this cause no data to be returned?

 

[Len Boorman]

Looks as if yopu are not finding and duplicates.

To check amend the query to include only say Plan and Plan again with a count.
If this returns results with a count uopu can add fields one at a time and check the record numbers as you go.

It will give you a better feel of the results

Len