Table Duplicate Records Question

[shoelesscraig]

I have a table with 3 fields. I manually enter new records into this table. I know how to prevent duplicates in a single field...but my question is a little more complex (I think).



I will enter info such as "A" in Field1 and "B" in Field2 for the first record. The next record might be "A" in Field1 and "C" in Field2. I would like to prevent duplicate records from being entered "either direction". What I mean by that, is that I consider "A" in Field1 and "B" in Field2 the same as "B" in Field1 and "A" in Field2. Does this make sense? If so, can this be done?

[ranman256]

in the form have 2 unbound text boxes,
user enters the 2 fields, this runs a Dcount on the data ,

if record count > 0 show, they exist,
if 0 records show, you can add them using an append query.

create a union query to find the 2 possibles and save it as : qnFindExisting

select * from table where field1=forms!myForm!txtBox1 and field2=forms!myForm!txtBox2

union

select * from table where field2=forms!myForm!txtBox1 and field1=forms!myForm!txtBox2

sub btnGo_click()
if Dcount("*","qnFindExisting") >0 then
   msgbox "record exists"
else
   
  docmd.openquery "qaAdd1Rec"
endif

end sub

[shoelesscraig]

I did the above, but aren't quite there yet. Should I literally have the * symbol in the union query you posted above? Or should I be replacing the * with something else?

When I run it, if I try to add something that already exists, it DOES say "Record Exists". However, when I try to add something that I know doesn't exist, it runs the append query, but says that it is appending 0 records. It's like it's not picking up the text that I've entered into txtBox1 and txtBox2...so it doesn't know what to add to the table.

[Micron]

Something seems not right besides the fact that it doesn't work so far. If you can arbitrarily enter data in 2 fields, swapping sides as you go, that seems highly unusual to me. Each field ought to be for specific attributes for the table (entity), like
CATS | DOGS
calico | spaniel
persian | poodle

That you could swap either of these is very unusual. Maybe all your values belong in one field if they could appear in either?

[shoelesscraig]

I've actually gotten it work now (after my last post above), so I appreciate the help...

I see what you're saying, and would normally agree with you...but just trust me...I have a reason for doing such. For context, I'll explain why below:

I track how far I drive for work. I already use this database for a completely different set of tasks, so I thought adding this mileage tracker would be convenient. So what I'm doing is going to a form and putting in 2 locations (for example "Home" and "Work") and the distance between them. As time goes on, the number of different combinations of locations will grow and likely be a LOT. So, I wanted a way to put in the distance from Home to Work and be sure that I'm not ALSO putting in Work to Home since that distance would be repetitive. I don't really care what order things are in. I might have Office to Customer A as an entry...but might accidentally try to also add Customer A to Office as well. I want to avoid the duplicate, but at the same time, don't care which way it is entered since, as stated before, the distance is the same either direction. So, if I try to enter something that already exists (either direction) it will pop up and tell me I can't.

Later on, I'm using this data in a report and a different Form to basically log mileage for a given day.

Without a doubt someone who really knows how to program could do this cleaner and better. I'm not a master at access, but I'm pretty decent (given enough time on this forum, lol) to figure it out. I used to program with VBA a good bit as well in a former life, so I've got some pretty handy code in this database for other reasons as well. Again, for now, it looks like I've got what I need. It's simple enough to do what I need, which is just track mileage every month in a convenient place (ie, the same database I'm already using for something else every day of the week).

Thanks all.....

[isladogs]

For the benefit of anyone reading this thread in the future it would help them if you explained/showed how you got it to work

[ArviLaanemets]

Like Micron did write, something WAS wrong with your setup. Had you registered tracks - like "Home-work" (and optionally defined end points in 2 additional fields), there wouldn't be any problems to start with.
An Example:
tblTracks: TrackID, TrackName, [Place1], [Place2], Distance