Best way to sort and reduce data in an Access MDB table

Question

Best way to sort and reduce data in an Access MDB table

Rich Goldberg 166

Looking for some advice on handling a table. I’ve got an Access mdb database. The VB program that drives it puts data into two columns of a table, and for each row it puts a numeric reason code in the third column. The data pairs in the first two columns may be duplicated many times, but each occurrence will have a unique reason code. I need to end up with a single row for each data pair (sorted by column A/column B), with only the lowest reason code remaining.

For example, the program may write the data in the following order:

Column A Column B`` ``Reason Code

aaa bbb 4

aaa bbb 2

bbb ccc 1

bbb ccc 3

aaa bbb 0

The result I need is:

Column A Column B`` ``Reason Code

aaa bbb 0

bbb ccc 1

Can the table be defined so that the table automatically ends up in the order I need? Or do I need to populate the table first, then use a query to sort the rows and eliminate the duplicates?

Viorel 126.7K Reputation points

2024-07-29T06:00:53.4533333+00:00

After removing the unneeded rows and keeping the lowest Reason Code only, the next time new data arrive you can check if the pair with greater Reason Code already exists, and update it (modify the Reason Code), or insert a new row, or ignore. This probably needs several Access SQL statements.

Answer accepted by question author

0 additional answers

Your answer

Viorel 126.7K Reputation points

2024-07-29T06:00:53.4533333+00:00

After removing the unneeded rows and keeping the lowest Reason Code only, the next time new data arrive you can check if the pair with greater Reason Code already exists, and update it (modify the Reason Code), or insert a new row, or ignore. This probably needs several Access SQL statements.

Answer 1

Michael Taylor 61,191

You need to store the data otherwise you'll lose it. You'll use a query to group the data to what you want.

SELECT ColumnA, ColumnB, MIN(Reason Code)
FROM table1
GROUP BY ColumnA, ColumnB

Rich Goldberg 166 Reputation points

2024-07-29T02:59:30.9466667+00:00

So there's no way to define the table to do those operations automatically, right? Ultimately, when the data is sorted and reduced, I don't want the original data anymore; I only want that result, and I see no need to process the query every time I want the reduced data. So you say (1) populate the table with all the data, (2) run a query one time to sort and reduce the data, (3) store the query result as the table data, right?
Michael Taylor 61,191 Reputation points

2024-07-29T04:15:33.1666667+00:00

If you don't need the original data once you're done then I would recommend that you create a new table that contains the final version of the data and only stores what you need. Of course something needs to run that query calculation and store the resultant data into the new table and then wipe whatever table you're using now. Personally it sounds like the VB app should be updated to do the calculation in memory and then just store the data once but if you have to use a staging table to get it done then you can.

If you absolutely don't need the temp data then this is the best option. If you need to be able to add additional data later and recalculate then you probably don't want to get rid of the original data as you would then potentially lose data when you refreshed it. If you were using SQL Server then you could do the calculation in a view but Access doesn't have that concept and you can't calculate column values by aggregating all the rows in a table. You could create a report I guess but you'd still need to keep the original data around.

Share via

Best way to sort and reduce data in an Access MDB table

0 additional answers

Your answer