Generating a Report with Multiple Items

Topic

New Reply

This should not be a problem but I can’t figure out how to solve it, so… back to the Lounge!

here’s the situation:
1) Set up a form with borrower info (name, address, etc.) and a subform with video tapes borrowed, linked to form by last name. When a borrower is selected in the form, all the videos borrowed are displayed in the subform ‘window’. all is fine.
2) I want to generate a receipt listing all the videos borrowed by any particular person (and overdue notices as well), but I CANNOT figure out how to get access to pull the data from a field in the subform (video name, for example) without having the borrower information printed for each different video. Since we have seven titles, and more to come, I do not want to send a multi-page receipt or overdue notice.

I am running Access 2000 SR-1 on Win2000.

Thanks!

Reply | Quote

Replies

have you tried grouping the report on the person or name field

Reply | Quote

No but I am starting to get some success with using a subreport. just goes to show, ask for help and you start to figure it out…

Reply | Quote

actually, now I can generate a report with the videos listed, but now the problem is that for every borrower with more than one video, I get multiple reports. I have tried doing a SELECT DISTINCT in the query SQL but that doesn’t do anything terribly useful.

the problem seems to be that, although each borrower is listed once in the borrower info table, since the child field linking the list of videos to the subform has to be a name to link up the forms and report/subreports, the subreport section writes the name again for each new video listed, and I can’t get the query to stop doing that… And the report doesn’t seem to have any way to stop generating duplicate names, which would probably be the thing I need.

So I have ‘half-way’ solved the problem.

Reply | Quote

Would the sorting/grouping property of the report help. GroupBy for example.

Reply | Quote

I tried that but it didn’t do anything significant. I assume by ‘GroupBy’ you are saying to go into the report design mode, click the control item to group by and then do so using the group/sorting toolbar button.

The problem seems to be that the query I have based the report on generates a copy of the customer name for each video title selected. However, for each (duplicate) item, all the video titles are displayed, so what I am thinking I need to do is, on generating the report, supress printing of duplicate names. Perhaps there is a VB solution…

Reply | Quote

You probably don’t need a subreport for this, only setting the grouping of the main report to group on the customer and sort on the dates, or whatever. Then when you want to print the report for a particular customer, use the DoCmd.OpenReport method from your form and pass a Where condition – the customer ID that will return only the records for the current customer – to the report.

If you put the customer information fields in the report header and fields that contain the same information as your subform in the detail , you should get something very similar without printing the name multiple times.

Reply | Quote

Thanks for your suggestions. I placed the borrower info in the report header and that makes the report format look a little nicer, but… still getting duplicate reports for anyone borrowing more than one video. I have previously done a form without a subform but wanted to use a subform in anticipation that the video inventory will grow and didn’t want to keep re-designing the form with each additional title.

I also looked at the code for a OpenReport and wondered if there is a method for blocking duplicate reports on open or format or something. Here’s the familiar OpenReport VB:

stDocName = “Borrower Receipt”
DoCmd.OpenReport stDocName, acPreview

Is there a VB code for suppressing duplicates at this point?

Many thanks to all who have responded so far!

Reply | Quote

Hi,
Is your database small enough to attach to a post (i.e. less than 100k)? If not, can you post the SQL of the query that the report is based on?

Reply | Quote

The database won’t zip down to less than 100 k — it’s at 268K. I will attach a word doc with screen shots of the main form and an example of an offending report. btw, since i used a subreport on the report, the beast won’t convert to Word without losing the subreport info. sheesh!

Reply | Quote

Hi Steve,
If you run the query underlying the report, do you get multiple copies of each entry – i.e. the same video appearing multiple times for the same person? If so, then your SQL is, as Charlotte suggested, incorrect. If not, then you may have a problem in the design of your report – you mentioned a subreport and from what you’ve described, you shouldn’t really need one. From a quick look at the SQL I think you should have a left join rather than a right one but I’m not convinced that would cause the symptoms you’ve described.
Another thing that occurs to me is that I wouldn’t recommend linking videos to individuals solely by last name. In the screen shots you’ve provided, the last name is Brown which is a fairly common one. Once you have more than one person called Brown I think you’ll have problems. I’d suggest assigning each person a unique ID number and linking the videos by ID rather than name.
Hope that helps.

Reply | Quote

I agree with the idea to link the form/subform with a user ID rather than last name, and will make that change. On the subject of not needing a subform, I read that as meaning I should design a form based on a table that includes all the various videos in the collection. I did that initially but thought it would be better in the long run to separate the video table from the borrower table and relate the items in order to make it easier to update the video list as needed. Otherwise, I would have to add additional check box controls for each video on the single form. Overall, I think what i am trying to accomplish via form/subform is not an uncommon situation, and a form/subform works well enough in terms of entering the data. It’s just getting a report that doesn’t print duplicates of the borrower information that I am getting a problem.

The underlying query *does* generate “duplicate” records for a borrower who has more than one video, but that is because it creates a new row for each video, which is not all that suprising — as far as the SQL is concerned, each row is unique. So, the thing I would like to do is do something like On Open (or On Activate), instruct Access to print only *one* instance of a borrower ID. That would solve the problem, and probably it’s a VB solution. Perhaps a counter loop in the ID and an If test for counts greater than 1 then no print or something like that…

Ah well, on to the VB module!

Reply | Quote

Hi Steve,
I’m attaching a zipped database I slapped together to show what I think will do what you want. Take a look and let me know if it helps you.

Reply | Quote

Some confusion is being generated because you actually asked two different questions in the same post, one on forms and one on reports. It has been a little difficult to tell which one you were addressing in any given post. In general, you’ll get better answers if you split your questions into separate posts. There’s nothing wrong with using a subform, it was a subreport that was unnecessary.

Reply | Quote

If you have duplicates in the recordset that don’t exist in the data, either your query is put together improperly or you grouping and sorting is wrong. You suppress duplicate instances of a record in the query by using the DISTINCTROW keyword, which selects unique records. However, if your query is poorly joined, you’ll need to go back and rewrite it to get this to work as expected. Why not post the SQL of the query so we can see if that is the culprit.

Reply | Quote

Thanks for all the help everybody – I have what I need by now. It turns out the problem was using a subreport. I did a query linking the Borrower Info to the Videos table and placed the borrower info in the Header of the report and the Videos info in the Detail of the report, and now all is hunky-dory. No need to use a subreport.

Just goes to show – I knew there was an easy solution to this problem but got all side-tracked with subreports. I’d like to thank Rory esp. for his help in slapping together a demo db that addressed the problem.

Reply | Quote