Query Design Problem

[cuttsy]

I have a database containing details of schools their fantasy 'record companies' and their artists.

I need a query to be made of the following 4 tables.

tblSchool(SchoolID, schoolName)
tblCompanies(companyID, schoolID, CompanyName)
tblMembers(memberID, companyID, MemberName)
tblPortfolio(portfolioID, companyID, artistID)

I'm not sure its possible but just in case. Could I have a query to show the school name, company name, names of members and names of artist without duplication.

With a normal query I get:

School A, Caompany 1, Fred, Green Day
School A, Caompany 1, Fred, Britney Spears
School A, Caompany 1, Jim, Green Day
School A, Caompany 1, Jim, Britney Spears
School A, Company 2 ...........

I need to show each artist and member only once for each company.

The reason I need this is to be used in a Reprort to show:

Shool Name 1
Company Name 1
Member 1
Member 2
Member 3
Artist 1
Artist 2

Am I asking the impossible?

Any help would be greatly appreciated.

 

[ecniv]

Forget the query returning duplicate info, if you are designing a report using access reports - then use the grouping function in access.

I think its under the right mouse menu>Sorting and grouping

Here you specify how to group and when, and also if you want a header/footer for each group. Create Headers as required, move the appropriate textbox outputs into the headers, and in the main data, leave the last textboxes. Then your report should output as you want.


Vince