Hi,

So I'll probably explain this wrong but here goes. I have a database which captures the login info of a user, I would like to assign that user to a group with a group code. For example shabbaranks logs in he's in the group "Access dumbo" there for his group code is "ACD". Can anyone hold my hand and guide me through this please? Im on a steep learning curve (IMG:style_emoticons/default/smile.gif)

Thanks in advance

This post has been edited by shabbaranks: Oct 26 2011, 11:01 AM

(IMG:style_emoticons/default/welcome2UA.gif)

One question to resolve first is... can a User belong to more than one group? For example in my church-membership database I can be simultaneously a member of the Deacons, the Choir, the Ushers and so on; or you might have the simpler situation where every User belongs to one and only one group (at a time, and you don't want to keep the history).

If it's only one group, then all you need to do is create a Groups table; it should have a Primary Key (the GroupID, which might be an autonumber or a short, stable text code) and then perhaps some other fields for information about the group as a thing in its own right (a descriptive GroupName text field for example). You would then simply include a GroupID field of the same size and datatype as this Primary Key in the Users table.

You would then use a Form based on Users (never use table datasheets for routine interaction with data, always use Forms) and put a Combo Box control on the form to choose the group.

Post back if it's a "many to many" (one User can be in zero, one, or multiple Groups and each Group can have zero, one or many Users); this is very common but requires a third table and a bit more work.

Hi John,

Thanks for your reply. Yes there are multiple groups which each contain multiple members - so as you say it will be a many to many relationship. With regards to building these table relationships, Im trying to get to an end produst whereby you can work out the group code based on the user logged in and the subject code based on the activity. Obviously only one person can do one activity at one time which should result in the relevant code - hope this makes sense?

Thanks

Ive made slight progress, and was wondering if someone could look at my DB please? Ive made a relationship, but (and I dont know a great deal about Access) I feel I should have a relationship between the main table which the form inputs into and the other tables which hold the usernames etc. Could someone spare me a couple of minutes to see if Im going A about T on this one please?

Thanks

Unfortunately my A2007 machine is currently really inconvenient to use (I need a new UPS) so I won't be able to look at it right away; maybe someone else can comment!

that isn't quite what john was asking - what he was asking was whether a single member can be in more than 1 group at the same time.

ie - if shabba ranks logs in, can he be in group ACD and group BCD, or just one only.

if so, does he need to pick which group he wants at this point (ie ACD or BCD) , or does he get (say) the combined total of both of his groups.

--------
also, how does your program allow access to the different groups anyway.


all of these questions will affect the way you set this up.

I think I have set up the relationships between the users and groups, its a many to many relationship as you quite rightly said a group can contain many members and a user can be a member of many groups.

As the "how does your program allow access to the different groups anyway" the program doesn't restrict access at the moment, what I need to do (and hopefully with some help and google) is set up some sort of logic where based on your username which is in a group you can either view (which might be difficult to achieve) or select (which is probably easier?) a certain range of activities.

As I say I am new to Access (using it properly) so this is all work in progress and learning at the moment - Im just grateful for anyone's time assisting me.

Thanks (IMG:style_emoticons/default/smile.gif)

Normally in a many to many relationship you would use three tables: Users; Groups; and UserGroups let's say. The table structures would be like

Users
UserID <primary key>
LastName
FirstName
<other individual info, nothing about group membership though>

Groups
GroupID <primary key>
GroupName
<other info about the group as a whole, nothing about users>

UserGroups
UserGroupID <autonumber primary key>
UserID <link to Users, which user are we talking about>
GroupID <link to Groups, which group is s/he in>
<any information about this user in this group>

For data display or entry, you could use a Form based on the Users table, with the user-specific fields displayed in textboxes or other controls; on the form you would have a Subform based on UserGroups, typically with a Combo Box control bound to the GroupID field but displaying the GroupName. This will let you both display and edit or enter the groups for a particular person.

You can do it the other way instead (or in addition) - have a Form based on Groups, with a subform based on UserGroups, with a combo box displaying the user name bound to the UserID.

Soooo Im making slight progress in other areas of my database but this is the one part which is holding me up. I have setup a many to many relationship between the users and the groups. So basically my form looks like (IMG:http://imgur.com/fXdEN.gif) as the image shows a textbox is populated by the current user who is logged in and that user is a member of a group. This group would be displayed in the usergroup textbox. Based on the user group and the activity selected the cost code should get populated within the costcode - the cost code is made up of a matrix of different combinations of usergroup and activity and is stored within its own table. The sub project code is the code of the activity within the activity table.

What method would I use to get the costcode displayed based on the result within the matrix of the groupid and activity selected? And how would I populate the subproject code which is a seperate column of the activity table?

Help is appreciated - thanks (IMG:style_emoticons/default/smile.gif)