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

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.

...and welcome to the forum :)

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.

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.

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?

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 (http://support.microsoft.com/kb/283878).

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]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?

...

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).

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.

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

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 (http://www.mvps.org/access/lookupfields.htm)) 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?

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.

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.:eek:

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.:eek:

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.

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.

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.

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

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/access-help/combine-text-values-by-using-an-expression-HA010235851.aspx

Oh, and #2 -

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

Oh, and #2 -

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

Happy Dance :D Thank you soooo much! The plus instead of & sign worked perfectly (with some experimenting of course).

I'm really getting this, and my report is looking fantastic. I still have the volunteers who are a couple as two separate listings, though one is right above the other. Is that as good as it gets? I would love to be able to merge those into one listing as in: Barney & Betty Flintstone, yada, yada...but if this would be too complicated, I'm pretty satisfied.

Couples would be a bit of a pain to try to turn into one record. It is probably possible but isn't necessarily simple.

Ok, that's kind of what I thought. Thanks again :)

Ok, one more question if you don't mind. As I look at my contact list report, I'm thinking I would like to have it show the volunteer info separate from the staff info. Normally this would be a simple group on fix, but I have at least 4 contacts who are both volunteers and staff and when I try grouping on position, I end up with those 4 contacts listed twice. Is this fixable? As the report is right now, I have it grouped by Name, then address, and each contact has all positions included.
So: Joe Shmoe, Blah-Blah St, Blah-Blah town, 123-4567 phone #, Volunteer, Staff

If you want to show the records separated by contact type, you will not be able to keep it so those who are in both show up twice. However, now this may be a bit more complex but you could have the main report data source only select those who do NOT belong to more than one contact type. And then have a sub report for those that are and have its record source only bring in those who do have more than one contact type. Then put this one into the report footer so it shows up at the end of the report. Just an idea.

If you want to show the records separated by contact type, you will not be able to keep it so those who are in both show up twice. However, now this may be a bit more complex but you could have the main report data source only select those who do NOT belong to more than one contact type. And then have a sub report for those that are and have its record source only bring in those who do have more than one contact type. Then put this one into the report footer so it shows up at the end of the report. Just an idea.

Hmm.. that is a bit confusing. I don't want duplicate entries on my report, that's what I meant, and I think, what you meant to say.

I'm thinking through the steps now, and looking at the query on which my report is based, I have "vol" or "stf" in the criteria of the Position field (because I don't want clients in this report)
From here I'm a lot confused as to what to change or do. I understand that I will be needing a sub report to get the job done, but am lost as to how to set the query criteria, or whether to make another query all together?
Sorry to be so dense.

If you want to implement what I suggested the last bit, you would have to create a new field in your query using a DCount which counts the number of entries in the tblPositionContact table limited by the person's id. And you would have criteria on that field of < 2.

So in the FIELD are of the query grid you would put:

PositionCount: DCount("*", "tblPositionContact", "[IDFieldNameHere] = " & [IDFieldNameAgainHere])


And then under criteria for that field:
< 2

And then you would create an exact same query for your subreport for those who have more than one contact type so that would have the same DCount field but the criteria would be

> 1

I hope that helps.

If you want to implement what I suggested the last bit, you would have to create a new field in your query using a DCount which counts the number of entries in the tblPositionContact table limited by the person's id. And you would have criteria on that field of < 2.

So in the FIELD are of the query grid you would put:

PositionCount: DCount("*", "tblPositionContact", "[IDFieldNameHere] = " & [IDFieldNameAgainHere])


And then under criteria for that field:
< 2

And then you would create an exact same query for your subreport for those who have more than one contact type so that would have the same DCount field but the criteria would be

> 1

I hope that helps.

It makes sense. I keep getting a syntax error when I put it in as you wrote it only changing, of course, my fieldnames and such. Not sure what I'm doing wrong. I'll keep whacking at it and let you know how it comes out.

It makes sense. I keep getting a syntax error when I put it in as you wrote it only changing, of course, my fieldnames and such. Not sure what I'm doing wrong. I'll keep whacking at it and let you know how it comes out.

Post what you have (with your replaced name changes).

Post what you have (with your replaced name changes).

Hope you had a nice holiday -

Ok, back at it again. I fixed the syntax problem. :) However I'm still not getting quite the results I want. Applying your solution, I can eliminate, end up with just the staff who have dual roles, but I still end up with staff who only has one role left in with those volunteers who only have one.
I'm wondering about simply having a query for each group and bringing the two I need into my report - again with some kind of rule out for duplicates. Does that make sense.

If you want, you could post your database (with bogus data, of course) and I can take a look and see if I can get it to do what you want.

If you want, you could post your database (with bogus data, of course) and I can take a look and see if I can get it to do what you want.

Ah - I figured it out! I now have my contact list report working beautifully just the way I want, volunteers listed first, staff listed next and all in one report. This is what I did - I added a field to my Contact table called FileAs. Then I put a Combo box control on my contact form, with the control source FileAS. Now I can designate each new contact as either ABC (Mama organization i.e. those I want listed as staff only) and EFG (Child -everyone else). Then I made an ABC query and and EFG query and a Union query to bring them together. I suppose this was not the most efficient way to solve my problem, but I'm happy with the results, and I can see no potential problems, other than needing to manually fix the sql in the Union query if I need to add or delete a field in either of my two joined queries. Since I don't anticipate needing to do this, and I am comfortable with how to if I do. I'm good.

So, now I have everything the way I want it and am on to my last task. I want to be able to see each volunteer once with a list of their clients. Any suggestions?

Again, I sooo appreciate the help.

If you are talking about in the same report you can create a subreport for pulling clients by voluneer and put that in the volunteer group header or footer linked on volunteer id.

No, I want it as a separate report.

So, how do you currently specify in the database that someone is a client of a particular volunteer?

So, how do you currently specify in the database that someone is a client of a particular volunteer?

I have a form for setting up teams which includes a combo box for choosing two volunteers.The team id is the clients id, since each client is a unique team. This info is stored in tbl.teams with fields such as teamid, startdate, vol1, vol 2, and so on. The data is stored by id #s.

Wow! I just figured it out - didn't realize it was so simple. Duh.
You have been a tremendous help. Thank you, thank you, thank you.
Happy New Year
GG

Wow! I just figured it out - didn't realize it was so simple. Duh.
You have been a tremendous help. Thank you, thank you, thank you.
Happy New Year
GG

Glad you figured it out (and it must feel good to know that YOU did it). I'm glad we could be of assistance to you.

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=35094&stc=1&d=1293818625









































.