This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(76.8, 72%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERG%3C/text%3E%3C/svg%3E)
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?
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 32%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMT%3C/text%3E%3C/svg%3E)
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
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?
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.