one to many to many to many query problem

[gamisens]

I have a database with the following structure: an items table has a one to many relationship to a groups table. The groups table has a many to many relationship to a members table through a connecting table of group members. I need to find an item by searching for member data but there is no direct relation between the items table and the members table (only through the groups). How can I construct an SQL query to accomplish this? Do I need to restructure my database?

 

[tlbroadbent]

Open a new query. Add all of the tables you've listed - Item, Groups, GroupMember, Members to the query design Window. Create all the relationships if Access doesn't do so automatically. Add those columns you want the query to return as well as the column(s) you want to search on. Add a parameter to the criteria of the search column. You should then be able to run the query and it will prompt for the search criteria.

The resulting SQL would look something like the following. Of course, you'll need to modify this sample to match your table and column names but this should get you started.

Select i.ItemID, i.ItemName
From Item As i
Inner Join Groups As g
On i.ItemID=g.ItemID
Inner Join GroupMembers As gm
On g.GroupID=gm.GroupID
Inner Join Member As m
On gm.MemberID=m.MemberID
Where m.MemberName=[Enter the member name] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.