Multiple table, Many-to-Many Report

[willtell]

Hi Everyone,

It's been quite a few years since I've used Access, and I am asking for anyone's opinion on how to solve this dilemma.

I work for a publishing clearing house and I'm trying to set up a simple database for them, I feel like this should be an easy one but for the life of me I can't work it through my brain!

I'll start by describing my tables below, including only important fields:

tblSong - Song_ID (p_key), Song_Name
tblWriter - Writer_ID, Writer_Last, Writer_First
tblPublisher - Publisher_ID, Publisher_Name
tblSongWriter - Song_ID (f_key), Writer_ID (f_key)
tblSongPublisher - Song_ID (f_key), Publisher_ID (f_key)

I want the report to look like the following below:

Line 1: Song_Name, Writer_First, Writer_Last, Publisher_Name
Line 2 (if needed): Writer_First, Writer_Last, Publisher_Name
Line 3 (if needed): Writer_First, Writer_Last, Publisher_Name

Basically, I want to list the song name only once, and then if there are more than one writers, list those, and then the same for publishers...

I've tried but I can't seem to do it without it listing the writers over and over again, for each publisher, if there is more than one, or vice-versa!

Any help would be greatly appreciated!

Cheers,

Bret

 

[georgedwilkinson]

Since nobody answered and I know little to nothing about reports, I decided to take this on. I was able to essentially duplicate your scenario using 2 sub-reports. It took me about 30 minutes to do, including creating and populating the tables and research into the links I've provided.

However, I think you need to rethink either the table design or the report design as writers and publishers are not represented as 1:1 in your table design and what you say you want the report to look like seems to be 1:1.

Do you know how to make a sub-report? If not, take a look at this: http://office.microsoft.com/en-us/access/HP051874091033.aspx
This is for 2003 but I had no trouble adapting to use it in 2007.

First, remove all references to writers and publishers in the main report's design.

Using the instructions from Microsoft which I linked to, create a sub-report (in the main report's detail area) for writers. In the query for the writers subform, include:
tblSongWriter.Song_ID
tblWriter.Writer_First
tblWriter.Writer_Last

Manually select the linked fields and select Song_ID from the main form and Song_ID from the sub-form. Delete the field Song_ID from the design of the sub-form. Viola, you have a sub-form to display each song's writer when the report runs.

Do the same thing with your publishers data.

Move the 2 sub-reports around on your form so it is visually appealing and change the labels so they say what you want them to say (or totally remove them as appropriate).

When you're done, take a look at this to prevent showing the sub-form if there is no data:
http://www.mvps.org/access/forms/frm0029.htm

I'll leave it to you to figure out how to size the detail area and sub-report sizes as the number of rows in each change. Please post your answer back here for all to enjoy.

Have fun.

 

[willtell]

Hi George,

Thanks for your reply. I will try it out in the morning, and let you know the results!

 

[willtell]

Hi George,

I followed your instructions, and then ran the report. Everything looks the way it should but Access seems to lock up, and the cursor turns to an hourglass. At the bottom of the main Access window, it says "Formatting the report, press Crtl+Break to stop...", which goes away after about five minutes.

And then, when I scroll down in the report, even by just a small bit, Access freezes again, and says at the bottom "Calculating...", and after about a minute or so, displays the report.

I'm thinking it's because there are no page breaks?? And Access is trying to format the entire, contiguous, report all at once, even when I move the scroll just a bit.

Anyway, I think I can get it to put a break in every 15 or so records...

Thanks for your help, and I'm more than open to further suggestions!

- B

 

[georgedwilkinson]

Hmmmm. How big is the recordset? Also, do you have indexes set up?

 

[willtell]

I have 800 records in tblSong, 1000 in tblWriter, 800 in tblPublisher, 1100 in tblSongPublisher, and 1460 in tblSongWriter.

And, I'm not sure if I have indexes set up or not, would that help?

Update: I just checked the table design of all the involved tables, and they are all marked Indexed "Yes (No duplicates" except the tblSongPublisher and tblSongWriter, which are marked Index "Yes (Duplicates OK)".

-B