MS Access / VB Help needed

maxheadroom

Hi,

I'm trying to muddle my way through some MS access work, and I'm stuck. I've stripped out the irrelevant parts of the following problem (there;s more detail on the report and in the query, but it isn't used for any operations)

I've got a main table containing a list of people, their groups, and their roles. I have a query which selects people who have role ID 1, formats their names properly, and has returns FullName and GroupID. I also have tables defining the Groups and the Roles.

I created a report which prints a list of names grouped by GroupID. The Header for each group displays the group name. I want it to display the group name and the people in charge of the group. The problem is that those people can have a roleID of either 2,3,4 or 10. However, all of those roles have Roles.Chair set to true.

Basically, I want to execute an SQL statement like this:
[PHP]SELECT Participants.FirstName, Participants.Surname, Roles.Chair, Roles.RoleName
FROM Roles INNER JOIN Participants ON Roles.ID = Participants.Role
WHERE (((Participants.GroupID)=Me![GroupID]) AND (Roles.Chair)=True))
ORDER BY Participants.Surname;[/PHP]
And then walk through the results, and for each result, format a string as Firstname Surname(RoleName) for each responsible person.

However, I have no idea how to get VB or Access to do that. Can anyone help, or give me a link to somewhere that can. I've tried google and the access newsgroups, but to be honest, I don't even know what keywords I should be searching for.

maxheadroom

Never mind - got it solved with some assistance from www.tek-tips.com