Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Generating a Report with Multiple Items

    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!

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Report with Multiple Items

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

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Report with Multiple Items

    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...

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Report with Multiple Items

    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.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Report with Multiple Items

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

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Report with Multiple Items

    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...

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating a Report with Multiple Items

    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.
    Charlotte

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Report with Multiple Items

    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!

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Generating a Report with Multiple Items

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Report with Multiple Items

    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!
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating a Report with Multiple Items

    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.
    Charlotte

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Generating a Report with Multiple Items

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating a Report with Multiple Items

    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!

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Generating a Report with Multiple Items

    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.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating a Report with Multiple Items

    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.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •