DB design / Query help

[rooterr]

I am currently developing a database for project tracking. The db can have up to three groups responsible for each project. So I created a respgrps table that has grp1, grp2, grp3, and grpID. The grpID is related to the project tables grp_ID. I also have a groups table that has groupID and group(actual group name). My problem arises when I try and query the information I can only pull up one of the groups at a time because you can only have the groups table related to one field. Basically what I am wanting to do is two fold query by group to find out all of the projects they are currently working on and query by project and see all of the groups responsible. Can I do this with my current table structure if so how. If I cant what can I do so that I can. Attached below is a breakdown of my tables.

respgrps groups projects
grp1 groupid proj_id
grp2 group grp_id
grp3 project
grpid

Any help or suggestions would be beneficial

 

[sko]

The problem is with your table structure. First of all don't place a field for each group in RespGrps. You have a one to many relationship between projects and groups. One project can have many responsible groups, therefore one project could have many records in responsible groups,one for each group assigned.

----------tblGroups---------------
groupID > Primary Key
groupName

Data Sample:
1 Group1
2 Group2
3 Group3

----------tblProjects-------------
proj_ID > Primary Key
project

Data Sample:
1 ProjectA
2 ProjectB

-----------tblRespGrps------------
proj_ID > Foreign key linked to tblProject.proj_ID
groupID > Foreign key linked to tblGroups.groupID

Data Sample:
1 1
1 2
1 3
2 2
2 3

Translation:
ProjectA Group1
ProjectA Group2
ProjectA Group3
ProjectB Group2
ProjectB Group3

--------Relationships---------------
One to Many
tblProjects.proj_ID to tblRespGrps.proj_ID

One to Many
tblGroups.groupID to tblRespGrps.groupID

---------Query Groups and Their Projects--------
Bring all 3 tables into your query grid
Select group name from tblGroups, sort ascending
Select project name from tblProjects, sort ascending

 

[rooterr]

Thanks sko

It makes perfect sense now. I was letting the big picture(the other stuff I left out so that it would make sense on the post) cloud my judgement.