Tricky Table Trouble

[Zaeed]

Ok, I have a table that contains a number of yes/no fields depending on location.

There are two main parts of my db that use this, one is users (for their departments) and the second is changes (for secondary locations)

I want to have two fields in the table used to reference a set of locations. Meaning i can reference the table and get locations where userID = 3 or alternativly where ChangeNumber = 6

Can somebody help me in making this work. Im currently having issues with duplicate values etc

 

[Guus2005]

Can you help me make understand what you mean? Could you post a sample database describing your problem?

 

[FoFa]

You say Departments refs. locations, and Changes references Locations. So eah of those tables has a foreign key pointing to Locations am I right?
So where does this table with two foreign keys into play then?
Need more information.

 

[Zaeed]

The table is used in two seperate situations.

One where it is giving the various locations a user has control of.

The other is what areas a Change affects

I have it setup atm so that there are two extra columns, apart from the locations. The first is a UserID column, and the second is a ChangeRef column

What i'm doing currently is when creating say a new user. I'm creating a location record with UserID set and ChangeRef = 0.
Then when extracting information, i'm just running a lookup with say WHERE ChangeNumber = 43

What i'm after is a better way of doing this.
After a nights sleep, im wondering if it would be better to create a single locationID column, and then use that unique ID as the reference.