Report Design

[cuttsy]

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

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

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

I need a Reprort to show:

Shool Name 1

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

Company Name 2
Member 4
Member 5
Member 6
Artist 1
Artist 3

School Name 2

Company Name 3
Member 7
Member 8
Member 9
Artist 2
Artist 4

....


Where as I currently get

Shool Name 1

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



Am I asking the impossible?

Any help would be greatly appreciated.

 

[john471]

Sub-Report

cuttsy,

Sounds like you need a main report with a sub-report . Have no fear, it is not that difficult!

I'm guessing that the "members" are the school children responsible for the company ? If that is along the right track....

Create two queries, one to establish the relationship between (and retrieve the set of data pertaining to) schools, companies and their members, and a second query to do the same for schools, companies and their artists. Include the relevant ID fields (SchoolID and CompanyID) in each query, as these will be used as the basis for the relationship between the main report and the sub-report.

Build a main report based on the first query (don't need to include the ID fields in the report, they just have to be in the underlying query).

Build a sub-report based on the second query (presumably only showing the ArtistName). Again, don't need to show the ID field(s) in the report they just have to be in the underlying query.

In the main report, add a Company Name footer (via Sorting and Grouping) and insert a sub-report object. Link it (set its "Source Object") to the sub report you just created, and link the SchoolID & CompanyID fields in the child and master reports. I find the wizard to be pretty good for this, or you can set the "Link Child Fields" and "Link Master Fields" properties to both be "SchoolID;CompanyID".

Save both reports, and run your master (first) report.

And Robert, as they say, is your father's brother!

Attached is one I prepared earlier, if it helps explain further....


HTH

Regards

John.