Multi Forms and Multi Tables all linked together

[kbrooks]

I'm not really sure where to start with this, and don't want to attempt something only to find out I'm completely wrong.

I'm trying to set up a database to show the functions users have access to on the multiple applications we have here. I'll have one table for general user info...name, department, etc. Then I'll have one table for ApplicationA, one for ApplicationB, etc.

What I'd like to happen, is to have all the applications listed on the general info form, and the user would check the applications they use. At that point I'd like the form for each application to open up....possibly resulting in several open forms. On each of these forms would list the functions for that application, and the user would check what they have access to.

I assume somehow I'd have to tie each of the pop-up forms/tables to the general info form/table, also. And I'm not quite sure how to do that.

I know this is long and involved. Many thanks for any help you can give me!

 

[Pat Hartman]

Drop the idea of a separate table for each application. That would not conform to good table design and would be horrible to maintain. You need 4 tables.
1. tblApplication
ApplicationID (autonum primary key)
ApplicationName
other information
2. tblApplicationFunctions
FunctionID (autonum primary key)
ApplicationID (foreign key to tbl1)
FunctionName
other information
3. tblUser
UserID (autonumber primary key - unless you have some other internal ID you can use instead. Don't create a new one just for this purpose)
UserName
other info
4. tblUserApplicationFunctions
UserID (pk field1)
ApplicationID (pk field2)
FunctionID (pk field3)

tblApplication has a 1-to-many relationship with tblApplicationFunctions. tblUserApplicationFunctions also has a 1-to-many relationship with tblUserApplicationFunctions.

You need a main form (for tblApplication) with a subform (for tblApplicationFunctions) to add and change data for those tables. You need a main form (for tblUser) and a subform (for tblUserApplicationFunctions) to maintain user information and to maintain the lists of Functions he has access to.

 

[kbrooks]

Thanks, Pat...I'm glad I asked before I got too deep into it, or I probably would've had a mess.

Couple questions for you. You had noted to make my ApplicationID and FunctionID a autonumber. Would it make a difference if I used a 2 character field? All the applications have an ID unique to them...AR, CC, HO, etc. And the functions also...AR001, CC015, etc.

Also you made reference to a "foreign key" and I'm not sure I quite understand that. Does it just mean that within that 2nd table, I could have 10 different functions, all with the same application?

Thanks again for your help!

 

[kbrooks]

Thought of another question as I was drawing this out on paper. I originally had planned to put somewhere in the user profile whether or not the user had email, network logon, etc. Where is the best place to do this....treat them as another application?

Thanks!