Select Top (Subquery)

I worked with some developers awhile back for a company and recently found a problem with an update button, which caused data to be copied as many as 250 times. A table (measurements) holding data should have around 25,000 data points now has well over 500,000.

One table [Tasks] contains info about a block to be tested, such as an ID,desc, quantity to test.
(taskID,Name,description,dimension1,quantity1, dim2,q2,d3,q3)

There is a linking table [Pieces] which contains the fields [taskID] and [pieceID], because each taskID might have multiple pieces tested with many data points per test.

Measurements table contains: pieceID, measurementID(AutoNum to make unique),dimNum, dataPoint.

Each block can be tested in 3 dimensions thus there are 3 different quantity to test fields, but generally its just on the first, so I'm assuming the query just gets joined to another for each dimension.

The question
Basically I want to Select Top (quantity to test from tasks) for each pieceID in Measurements. Select All from measurements.

Is this enough info/clear enough?
Thanks in advance

Perhaps something like this would work?

The problem I have is that there are "duplicates" in the measurements table. Like I should have 6 measurements. 6 were taken, then an error in my code caused copies to be made. So now I have a series of 6 data points repeating every 6 rows. The data points MIGHT be the same though (I might have 2 of the same data, but are not copies). So Top 6 would work, but sometimes its 10,12,24 measurements. The number of measurements is stored in the tasks.quantity1 field, but I can't get how to get that dynamic number out, I think in a subquery?

so you're more interested in deleting the wrong data, than displaying the right data...

I assume you don't have a backup of the database?
or a script to re-write the codes that is in the database?

If you're using a programming language to interact with the database, you might want to make a simple program to interact with database. Perhaps display every row in the table, with chekcboxes, then you can quickly check the fields you want deleted...

depending on your database system, you might already have a program that will connect and present data in this fashion already.



On second thought, are you positive there's 10,000+ measurements in the table?
Perhaps you just aren't joining the tables properly...

try a simple SELECT COUNT(*) FROM measurements [and each other table]...

If you were to use multiple tablse in the FROM clause, but not join the tables proplery in the WHERE clause, it will display a LOT more results than you expect.

• I have full access to the database. I can make backup copies. (the problem wasn't discovered for a few weeks when the database went from 2meg to 10meg. The problem with the program that adds data was fixed, but its not possible to restore a backup copy of the database)
• The database is in MSAccess2003.
• Yes, I want to delete the bad data ( I was going to make a query just like the table, so I can empty the table, then copy the query into it, that way I can see and make sure the query didn't delete anything incorrectly)
• I was hoping to just write the query in Access, but I have VB6 and All the Office2003 programs at my disposal(might be fun to do it though Excel)
• I just did a count, and theres 378,492 records in the measurements table(no joining here required). I would over-estimate there should be no more then 10,000 at this point.
• I can actually see the data points, in most cases there should be 6 measurements per pieceID, and theres between 350-500.

I wish I could be more clear on my explaination of the tables and relationships, I'm trying to condense the problem and consequently leaving out info.

Would a relationship chart help?

I'm not sure if this will help you, but from what I understand your problem to be, this is the approach I would take to resolve it.

• Back up the database
• Write some code (perhaps a macro inside Access would be easiest) that does the following:
  • Creates a multi-dimensioned array for storing the details of each Measurement. It would need to be of size 10,000 x 3 for each of the 'unique' records. The 3 is to store the pieceID, dimNum and dataPoint for each record.
  • Reads through every record in the Measurement table.
  • For each record in the Measurement table, checks to see if it is present in the array (ie. compare pieceID, dimNum and dataPoint, not measurementID as this is your primary key, with what is currently in your array). If not, add it to the array. If it is present, ignore it and then move to your next record. When you have finished looping through every Measurement record you will be left with an array of all the unique records that you should have in the database without the duplicates.
  • Deletes every record in the Measurement table.
  • Loops through your array adding every record to your Measurement table. This will populate your Measurement table with all of the 'correct' records.
• Run the code and this should update your database with the 'correct' data.

The only problem I can potentially see with my solution is if the MeasurementID is referenced by any of your other tables. My proposed solution will involve generating new measurementID's when you rewrite the correct records back to the table. Thus, if any other tables reference the original measurementID's then these references will be invalid and you should approach the problem differently.


I hope this helps.

I thought about running code to solve the problem, I was just know that a query would do the trick so nicely. I just don't know how to get the subquery to check how many measurements to take for each pieceID and then to run that query for the SELECT TOP for each record in the tasks.

I finally figured it out. There is a help file in Access relating to "Delete Duplicates" that explains how to perfectly. I just never realized that my timestamp was the key to finding the duplicates. I didn't need much of a query at all, except for a simple append query mentioned in the help file. Its nice when F1 works.