Record (A, B) is the same as (B, A): how to prevent this kind of duplicates (1 Viewer)

stopher

AWF VIP
Local time
Today, 15:15
Joined
Feb 1, 2006
Messages
2,396
Hi,
Does this mean that I need to create a query and run it every time I want to check for duplicates? I still don't understand how to run the check automatically each time I update the table, because as far as I've learnt up until this point, running a query is a manual task.
The solution I provided implements the required constraint on the table. You only need to run the SQL once because when you run it the constraint will be implemented in the table and will remain there. Once implemented, you won't be able to add reversed words regardless of how you add them to the table. You can remove the constraint later if you wish. This is the normal way to implement such constraints in server based solutions.

See attached example. When you open the database you can add reversed words to the table i.e. no constraint has been implemented.

If you run the query called qry_Add_Constraint, this implements the constraint. You can check if it has worked by trying to add reversed words.

You can remove the constraint using the query qry_Drop_Constraint.

To make your own query versions of what I have done you just need to create a new query then switch to SQL view.

Unfortunately in Access it is not obvious that such constraints are implemented so you really need to remember they are there. But at least you won't have to worry about applying some of the other methods offered each time you design a new form.

The point about ANSI 92 is important. It changes the SQL syntax used from Access to a more standard SQL syntax. For the most part you won't notice but you will need to be mindful. An example would be LIKE "*" in Access would need to be LIKE "%" under ANSI 92. But in any case, once you have implemented the constraint you can switch off ANSI 92 and not worry about it!
 

Attachments

  • constraint.mdb
    284 KB · Views: 99

stopher

AWF VIP
Local time
Today, 15:15
Joined
Feb 1, 2006
Messages
2,396
Uhm, thanks. I kind of know that, but I'm more familiar with Excel and still love the ability to see everything in a spreadsheet and move quickly between columns/rows when inputting data.
I agree, Access is sadly lacking in a datagrid view (akin to Excel view). The datasheet view doesn't even come close. Some applications are well suited to grid style view for user interaction, even if behind the scenes the data is stored in normalised tables.
 

Users who are viewing this thread

Top Bottom