flag duplicates based on duplicate values

[dleger00]

I have a table with duplicate values. They are transactions on a same serial number of a machine. I want to change a field (or add a new one) on which group they belong to.

Some of these records have an assigned group... Some dont. The rule is, if the last 2 transactions are of the same or different group, the serial number is assigned to that group... And it has to be two consecutive transactions... The latest transactions. If there is one transaction that is the last but the previous two would make it assign to a group, it would stay with that group.

Any thoughts on how I can make that happen?



I have to admit, i dont know sql that well. The ideal would be in query builder... But I can always tinker with sql if I had a bit of a road map.

[ItsMe]

...I want to change a field (or add a new one)...

Are you trying to populate an existing field with data or are you trying to change the design of a table by adding a new column? You can add a field by creating a new record, (A row of fields)...

To locate the last two transactions you can use the SQL keyword TOP and use ORDER BY DESC

something like
SELECT TOP 2 MyFieldName
...
ORDER BY MyFieldName DESC


However, I do not believe this is a complete solution to your problem.

[dleger00]

I have both.. An existing field and a spare, empty field also.

The data is already sorted by serial number and then by last transaction date.

thx for the help so far.

[ItsMe]

Normally, the second (spare) field would be stored in a separate table. So, in another table there would be two records/rows (in cases where both fields are populated) or one record/row (in cases where only one of the original two fields is populated). This is a fundamental rule of Normalization.

If, no matter what, there will Never EVER be a need for a third or fourth field, you could probably get by with having the extra column in your one table. However, I do not understand your data and workflow. There may be other obstacles encountered not having the second table I mentioned (Like SELECT TOP would not be of any use).

If you wish, you can upload your DB here and I will take a look when I have a chance. Others may wish to take a look and chime in also. Make sure to remove private data, run a Compact and Repair, and Zip the file down before uploading.

In all honesty, this is probably heading towards recommendations that you study up on relational database design. Starting with something like this.
https://www.accessforums.net/access/...tml#post235956