DB structure best practice question

[HansV]

You could create a query to count the number of programs per volunteer/staff:

SELECT Q.ConID, Count(Q.ProgID) AS NumberOfPrograms
FROM (SELECT DISTINCT tblAssign.ConID, tblProgTm.ProgID
FROM (tblTeams INNER JOIN tblProgTm ON tblTeams.TeamID = tblProgTm.TeamID) INNER JOIN tblAssign ON tblTeams.TeamID = tblAssign.TeamID
WHERE tblAssign.RoleId=2 Or tblAssign.RoleId=3) AS Q
GROUP BY Q.ConID;

You can combine this with the Contacts table to add personal information, and you can calculate the stipend. See the query qryStipend in the attached database.

ABLEzipHV.zip

[NWGal]

You could create a query to count the number of programs per volunteer/staff:

SELECT Q.ConID, Count(Q.ProgID) AS NumberOfPrograms
FROM (SELECT DISTINCT tblAssign.ConID, tblProgTm.ProgID
FROM (tblTeams INNER JOIN tblProgTm ON tblTeams.TeamID = tblProgTm.TeamID) INNER JOIN tblAssign ON tblTeams.TeamID = tblAssign.TeamID
WHERE tblAssign.RoleId=2 Or tblAssign.RoleId=3) AS Q
GROUP BY Q.ConID;

You can combine this with the Contacts table to add personal information, and you can calculate the stipend. See the query qryStipend in the attached database.

ABLEzipHV.zip

Thought I'd let you know that with a bit of tweaking, I used your suggestions and now have exactly what I want from forms to reports. Thank you again so much.

[HansV]

Good to hear that - thanks for the feedback!

[NWGal]

Me again...
The way we worked out the query for the report is great except that when a volunteer has assignments in more than one program, he still only gets X amount per program. As it is now, my report is fine as long as each volunteer has only one assignment per program, but if he has 2 or more assignments in the same program, it calculates on the total # of programs, so I'm getting $35 X # of programs. I'm not sure how to write the expression, or set the query to only count each program once per volunteer. I need it to be like this:
Yogi Bear - Wellness X 1 assignment = $35.00, ABLE X 4 assignments = $35.00, not $140.00, then on my report, I can total. In this case the total would be $70.00
Like I said, where Yogi has Wellness X 1, and ABLE X 1, it works fine.

[HansV]

That is precisely what the query qryStipend does...

[NWGal]

That is precisely what the query qryStipend does...

Well, it does until I try and add into the query those fields I need for information about the assignments. I get ambiguous join errors, or I end up with doubling the stipend amounts. Kind of confused.

[HansV]

Check out the report rptPrograms in the attached version. It uses a different approach. The report is based on qryProgs, and it uses a hidden check box txtCount in the ProgName header to keep a running count of the number of programs per volunteer, and a visible text box txtTotal in the Person footer to display the number of programs per person. The text box txtStipend calculates the stipend from txtTotal.

ABLEzipHV.zip

[NWGal]

Check out the report rptPrograms in the attached version. It uses a different approach. The report is based on qryProgs, and it uses a hidden check box txtCount in the ProgName header to keep a running count of the number of programs per volunteer, and a visible text box txtTotal in the Person footer to display the number of programs per person. The text box txtStipend calculates the stipend from txtTotal.

ABLEzipHV.zip

Sweet!!! That works beautifully. I already had a report format I liked and with your help, I now have exactly what I need. Thank you, thank you...THANK You!