Stumped

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

attachment.php










































.
 

Attachments

  • HappyNewYear.jpg
    HappyNewYear.jpg
    5 KB · Views: 140

Users who are viewing this thread

Back
Top Bottom