Problem with (almost) duplicate records, where not all fields are dups

[tokuzumi]

I have a table that is structured like this;

Table1 (
Id Int Not autonumber
RecordNumber Int autonumber Primary Key
AcctNumber Int
Quantity Int
Length float
Width float
Height float
Type int * determines the type of storage (bay, shelf, or floor)
MonthYear int * 92003 for sept 2003
datepreped smalldatetime
daterevised smalldatetime
)

The number of rows each Id can have is determined in another table that sets up how many of each storage type (bays, shelves, or floors) are available. To find out the number of rows are supposed to be with each Id, you could just do a (select count(*) as counter from setuptable where Id = @Id) and you would find out what the correct number of rows per acctnumber should be.

Now, onto the actual problem. We had to restructure the account numbers (acctnumber) to a new master list. Well, in some cases, two account numbers would roll up into one new account number. When this happened, we would get double the number of rows we needed for each acctnumber (old acctnumbers 123 and 234 had 17 rows, which rolled up to acctnumber 345, which now has 34 rows). What I need to do is write a query that determines how many acctnumbers have more than the number of rows they should have. The fields that will be dupliated will be the Id, acctnumber, length, width, height, and type. All other fields could be different.

I know this is a fun one, but I'm stumped. Any help would be appreciated.

 

[tokuzumi]

I was able to run the query with an active server page. Although, if anyone wants to help with encapsulating what I asked above into a stored procedure, or some type of view, that would be great, also.