Query Table (or report?) to pull information from multiple tables

[csmmsc]

Hello,

I'm new to Access, so please go easy on me

I am trying to create a simple database to help us plan our folders and permissions on a new file server at work. I have 3 tables: Users, Groups and Folders. The way it works is I give different Groups permissions to different Folders, then add Users to the Groups.

This all works, but I need a Query (or Report?) that shows me a list of Users and all the Folders they have permission to. If anyone can help me how to do it, or point me to a tutorial, I'd greatly appreciate it.

See the format below I want it in... this data is taken from the example file below.

Query Table Format

User	Read Permissions	Write Permissions
Chris C	S:\	J:\Design, O:\

Link to Example File


https://skydrive.live.com/redir?resid=37B09CB4A1C7773A!12179&authkey=!AJwrlf qzV81JsKE

Thanks in advance!

[June7]

Usually, I would refer you to: http://allenbrowne.com/func-concat.html

However, your db is not normalized because you are using multi-value fields. Allen's code can handle multi-value field if that is the field that needs to be concatenated. BUT, your multi-value fields are the fields with linking values needed to join tables in query. This greatly complicates what you want to do.

I NEVER use multi-value fields.

To normalize structure, since Folders and Users can associate with more than one group, each should be split to two tables. Consider:

tblFolders
ID
Location
Description

tblGroups
GroupName
Description

tblUsers
ID
UserLast
UserFirst

tblUsersGroups
UserID
Group

tblFoldersGroups
Group
FolderID
AccessCode (R, W)

[csmmsc]

Thanks June7. Unfortunately my Access knowledge is pretty limited so I don't know the best practices yet...

A couple of questions with your suggestion...
1. Users can be assigned to multiple groups, so do i need another tblUsersGroups table similar to tblFoldersGroups?
2. How do i create a table that will allow me to choose the Groups that can access the locations? Rather than having to go to tblFoldersGroups and doing it manually?

[June7]

1. You must have read my post before I edited it. Look again and I think you have answer.

2. You mean you want a way to search/filter tblFoldersGroups records to find a specified group? Use queries that join the tables. Apply filter/sort criteria in the query. This is basic access functionality.

Are you building this db for only you or will there be other users? Users should not work directly with tables and queries. Use forms and reports as user interface. Even if for yourself, you will probably appreciate the versatility of forms and reports. Can have code behind them.

[csmmsc]

Sorry... as I said I'm a novice on Access...

I understand the reasoning behind normalizing data, but don't know how to translate that back into a nice UI where I can have a multi-value lookup field that will populate tblUsersGroups and tblFoldersGroups on the background.

I am building this just for myself, but want to learn how to do it properly as future db's I build will be multi user.

[csmmsc]

See attached mock-up in Excel of how I would like it to work. This would be the main front end table working with all the back end tables suggested by June7. Note the comments describing each column.

This is a bit different format to the original post, but thinking about it more I think this would work better.

Sorry, some of this is probably basic Access but I just havent had the experience. If someone could do some quick changes to my original db to set me on the right path I'd appreciate it...

Thanks

[June7]

If you understand value of data normalization then you should know that multi-value fields do not comply with data normalization. My suggestion is to avoid multi-value fields. That Excel sample shows multi-value fields. Multi-value fields are mostly a source of frustration and experienced database developers do not use them.

The table structure I offered is a 'quick change' to your original db.

Maybe this site will help http://www.rogersaccesslibrary.com/

[csmmsc]

What I meant by the multivalue field in my example was I need that functionality in the user interface, but in the backend it would be populating the other tabled (in this case tblFoldersGroups)

Is this possible?

Sorry the link you provided doesn't help as they're all '97 files that seem to give errors in '13. A pity cos it looks like theres some great examples in there...

[June7]

Use combobox to select a value from a restricted list of choices. However, only a single value will be saved into the field. Instead of one record with multiple values in a single field, there will be multiple records with a single value in the field.