Advice on Database format

[Dowsey1977]

Hi,

I have a database which has currently got just 2 tables and 2 forms in it. 1 table/form has person data in it, the other table/form has information about access rights in it.

What I want is a subform that I can put on the person form to show what groups they belong to for access rights. There are 3 main groups for access rights witha bunch of more specific access levels within each group.

What I am asking for is advice on how to setup the database to be able to do that. Currently I have nothing that relates a person to a group, so really the setup is from scratch.

Any ideas?

 

[Ziggy]

In your group table put in new field for "Person" make it a number type.

Now take your form for Persons in design view shrink the window so you can se the DB window, click and drag the Group table to the detail area of the person form. This will activate the subform wizard, here (define your own) and select the personID from the person table and the Person field from the Group table.

I re-read the post a few times and I keep changing my mind, so the above will help you create a subform but also consider some changes, not sure if other will agree but lets see.

It seems like you should have an "Access Rights" table and an Access level table and also a USERDetail table. The user detail will look up the records from the other 2 so you can select the Access rights and the levels as you say that they can have more than one level.

The User table will have a Person field as I indicated above (also its own ID for the details).

Tables like:

AccessRightID
RightDecrip


AccessLevelID
AccessDescrp


UserDetailID
AccessLevel (lookup)
AccessRight (lookup)
Person (numberType)


PersonID
Person DetailsEtc...


Hope it makes sense

 

[Dowsey1977]

Fair enough - wasn't too sure on the answer, but moved on now.

I have managed to build a sub-form based on 1 query, so the form only shows access rights for the user on the record shown. However, I have 2 queries that I want to be shown in the 1 subform. Can this be done?

Also, there are several text boxes in the subform and they are not all used for every record. Is there anyway to get the 2nd query to populate from the next blank text box on the subform?

OR

Is there anyway to do a drop down box that will give the 2 area, i.e. Comms or ProgLib, and then the relevant subform is shown (i.e. from the 2 queries)

Any help would be great!

 

[Ziggy]

I sent you my e-mail in a PM if you send me an e-mail I will send back a sample DB. I can't attach it here.


You can use Join Queries to combine tables/queries by linking their related fields.

I use Access 2000 if you have 2000 or newer you can open it. if you have 97 le me kow and I will make it up in 97.


Ziggy