COMBINE UNRELATED TABLES

[jweisman]

I am setting up a preferences tablet in Access 2000. I have a table of users; tblUser only has one field, username. I also have a table of issues (tblIssue). This table also only have one field. Each record is unique in each table. I want to set up a third table that holds information about which issue each user wants to be able to view. This table would have 2 fields, user and issue. There would be multiple records for each user, depending on how many issues he would want to view. I created a query that has both tables in it, but I cannot update it because there is not a relationship between the two fields. I would like to be able to have the third table updated when a new user or issue is added. For example, if a new user is added. He would automatically be put in the third table with all of the issues assigned to him. Or if a new issue is added, then a record for each user would be added to the table for that issue. Any help would be greatly appreciated.

Thanks,

Jeff Weisman

 

[LittleSmudge]

I would like to be able to have the third table updated when a new user or issue is added. For example, if a new user is added. He would automatically be put in the third table with all of the issues assigned to him.

I don't think you do !

What you actually need is a Third table that has two fields

tblUserIssues
UserRef
IssueRef

UserRef is a Foreign key to the one field in the users table ( of the same type and size )
IssueRef is a Foreign key to the one field in the issues table ( of the same type and size )

They are both, together, a combined Primary Key for the tblUserIssues

Then when a New Issue is added to the Issue table you do not touch the tblUserIssues at all
when a New USER is added to the User table you do not touch the tblUserIssues at all

ONLY when you want to allocate an Issue to a User you you then add a record into tblUserIssues.

IF an issue no longer relates to a User you can delete that record from the table.


To list all Issues for a user:-
"SELECT IssueRef From tblUserIssues WHERE UserRef = " & UserNameVariable

To list all Users that have a particular Issue:-
"SELECT UserRef From tblUserIssues WHERE IssueRef = " & IssueNameVariable





'ope-that-'elps.

G LS