Stumped

[GingGangGoo]

I have a unique organizational challenge in setting up a database:
In my organization I have three distinct groups - Staff, Volunteers, Clients
The service we provide links two volunteers for a specific length of time with one client. It is possible for a person to be a volunteer and a client, and for a staff person to also be a volunteer.

My goal is to be able to generate reports that show:
A) Contact list of Staff and Volunteers only
B) Contact list of Clients only
C) Team Report by Client with assigned Volunteers, start date of team, etc
D) Report by Facilitator, showing all his/her clients

I'm pretty familiar with the basics of Access, but still need some hand-holding in setting up relationships, join tables etc. I've made some stabs at this, but I'm not ending up with the results I need.
Any help would be appreciated, step by step if at all possible. Thanks in advance

 

[John Big Booty]

You could have as part of your client details, three Yes/No fields one for each possible role a person could play in the organisation. That would make generating your reports and list pretty straight forward.

 

[John Big Booty]

...and welcome to the forum

 

[GingGangGoo]

You could have as part of your client details, three Yes/No fields one for each possible role a person could play in the organisation. That would make generating your reports and list pretty straight forward.

Thanks for the welcome.
Could it really be that simple? Am I over-thinking this? So you suggest that I but everyone in one table, and designate their roles? Guess I'll give that a whirl and see what happens. Thank you.

 

[boblarson]

You could have as part of your client details, three Yes/No fields one for each possible role a person could play in the organisation. That would make generating your reports and list pretty straight forward.

Or you could normalize it and have a PersonsRoles table which you can then assign whatever combination without having specific fields hard coded in. The question always comes up - what if another role needs to be created? It may not be likely but it is possible. Therefore if you properly normalize it, you won't be caught off-guard if they all of a sudden need a fourth role type. And none of your forms, reports, etc. would have to change - you just add it to the types table and then assign it.

 

[GingGangGoo]

Or you could normalize it and have a PersonsRoles table which you can then assign whatever combination without having specific fields hard coded in. The question always comes up - what if another role needs to be created? It may not be likely but it is possible. Therefore if you properly normalize it, you won't be caught off-guard if they all of a sudden need a fourth role type. And none of your forms, reports, etc. would have to change - you just add it to the types table and then assign it.

Normalize? This is a completely new term to me. Sorry to be such a neophyte.
How do I do this?

 

[boblarson]

Normalize? This is a completely new term to me. Sorry to be such a neophyte.
How do I do this?

First up - read this to familiarize yourself with the principles of Normalization.

Second, you would have a RoleTypes Table:

tblRoleTypes
RoleTypeID - Autonumber (Primary Key)
RoleTypeDescription - Text

in that table you would currently have 3 records
Staff
Volunteer
Client


And then in the PersonsRoles Table

tblPersonsRoles
PersonRoleID - Autonumber (Primary Key)
PersonID - Long Integer (Foreign Key - the ID number from your Persons table)
RoleTypeID - Long Integer (Foreign Key)

So there, using a subform, is where you would assign the particular roles of a person.

 

[GingGangGoo]

[/QUOTE]Second, you would have a RoleTypes Table:

tblRoleTypes
RoleTypeID - Autonumber (Primary Key)
RoleTypeDescription - Text

in that table you would currently have 3 records
Staff
Volunteer
Client


And then in the PersonsRoles Table

tblPersonsRoles
PersonRoleID - Autonumber (Primary Key)
PersonID - Long Integer (Foreign Key - the ID number from your Persons table)
RoleTypeID - Long Integer (Foreign Key)

So there, using a subform, is where you would assign the particular roles of a person.[/QUOTE]

Thanks for your help. So far I've created the tables as you suggested and related them. I've got a form for entering each persons contact information.
Now for the Roles subform, I'm guessing I want to use the "tblPersonsRoles" as the record source for this? Should I use a combo box for assigning each persons role? Is that the best way to set it up?

 

[John Big Booty]

...

I've got a form for entering each persons contact information.
Now for the Roles subform, I'm guessing I want to use the "tblPersonsRoles" as the record source for this? Should I use a combo box for assigning each persons role? Is that the best way to set it up?

Correct. If you use the Sub forms wizard it will step you through the process and set up the link between your main table and the sub table (tblPersonsRoles).

 

[boblarson]

Should I use a combo box for assigning each persons role? Is that the best way to set it up?

Yes, that is the way.

 

[GingGangGoo]

Ok, I've gotten some excellent help here, and I think I've made good progress.
I have a working form for entering contact info, frmContacts and a subform for assigning roles. All of that is working great.

I'm now working setting up and storing data related to each unique team. I have a tblTeams and a frmTeams. I even figured out how to use the lookup wizard, so that I can choose 2 volunteers for each team and have them in one field, ie. volunteers - Bill,Bob.

Now, my next challenge When I enter a new contact, or update an old one IF and only IF they are a client, I would like to open a form or subform and enter unique team info. How do I relate the record in frmTeam to the record in frmContacts? I assume I need to make an event to happen so that this only happens when I choose, i.e. when I am entering/updating a contact who is a client.

Hopefully that all makes sense. I sure am learning a lot as I wade through this.

TIA for any help

 

[boblarson]

I even figured out how to use the lookup wizard, so that I can choose 2 volunteers for each team and have them in one field, ie. volunteers - Bill,Bob.

no, No, NO, NO! Do NOT use lookups at table level (see here for why) and do NOT store more than one piece of data in the same field. That will make your life a living hell when you go to retrieve data (and it violates the rules of normalization and best practices of relational database design). The data should be ATOMIC - at its lowest level.

As for the other question - how do you specify that they are a client?

 

[GingGangGoo]

no, No, NO, NO! Do NOT use lookups at table level and do NOT store more than one piece of data in the same field. That will make your life a living hell when you go to retrieve data (and it violates the rules of normalization and best practices of relational database design). The data should be ATOMIC - at its lowest level.

As for the other question - how do you specify that they are a client?

Ok, that makes sense. I was actually just thinking through this and realized that when I need to run a report by volunteer, this would be a problem. I'll fix that.

I specify that a contact is a client in the frmcontacts by selecting the contacts roles. These values are stored in The ContactPositions table. I have a relationship set up that has Contacts, ContactsPostions, and Positions tables connected.

 

[GingGangGoo]

Here is the problem I'm running into now.
Everyone in the contact list now has a position, and some have two. I can query by position of course, but how do I make a report, say, a contact list that only has Staff and Volunteer information and doesn't put someone in twice, if they are both? Also, I have one case of two volunteers being married, I don't need their contact info listed separately, though I have it stored so, because they each have different teams.
I sure hope this isn't sounding too complicated.

 

[boblarson]

Here is the problem I'm running into now.
Everyone in the contact list now has a position, and some have two. I can query by position of course, but how do I make a report, say, a contact list that only has Staff and Volunteer information and doesn't put someone in twice, if they are both? Also, I have one case of two volunteers being married, I don't need their contact info listed separately, though I have it stored so, because they each have different teams.
I sure hope this isn't sounding too complicated.

The positions should be in a junction table where you have the personID and the positionID there. Then you can either use a report which groups on the person (so it will show them once) and shows their positions or you can use a subreport to show all of their positions, but only show them once.

 

[GingGangGoo]

The positions should be in a junction table where you have the personID and the positionID there. Then you can either use a report which groups on the person (so it will show them once) and shows their positions or you can use a subreport to show all of their positions, but only show them once.

I have this: tblContacts has ContactID, tblPositions has PositionID, and the table in the middle of the relationship, tblPositionContact, brings both of these together. Is this correct?
I sure appreciate the help.

 

[boblarson]

I have this: tblContacts has ContactID, tblPositions has PositionID, and the table in the middle of the relationship, tblPositionContact, brings both of these together. Is this correct?
I sure appreciate the help.

Yes, that appears to be appropriate.

 

[GingGangGoo]

Yes, that appears to be appropriate.

Great! I have now messed with building a contact list report and it's looking pretty good. Now two more things I think will be helpful to do, if you don't mind.

1) In the query my report is based on I have brought together the address parts into one field, just for the report, called Mailing Address. Now, I would like to leave this field blank if certain of the individual address fields are empty, i.e. no street address. Do I use ifNull for this? and if so, do I put it in as part of the string - somewhere in this, for example - Mailing Address:[Address]&","&[City]...

2) Is there a way to combine contacts who share the same address? This is another one of those "I bet the solution is super simple" things.

Thanks again

 

[boblarson]

for #1 -

You can look into using the + operator for null propagation. I always have to experiment with it to get it just the right way but essentially it is something like this:

http://office.microsoft.com/en-us/a...alues-by-using-an-expression-HA010235851.aspx

 

[boblarson]

Oh, and #2 -

You would have to group on address first before contact name.