Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More pages in report than expected (Access 2000 SR-1a)

    I have a customer records database from which I print several reports. The database is structured so that there is a Customers table, an Orders table, then a Machines table. A customer can have multiple orders and an order can have multiple machines. Normal structure for this type of database, there are two problems when I print a report for an order that has multiple machines.
    Some of the reports are structured so I should have only one order with one machine type and serial number per page along with all the detail for each machine. When I print the report however, I get twice as many pages as there are records, (ie if there are 2 machines, I get 4 pages two each of each record, if there are 4 machines, I get 8 pages). The header only shows on the first page.
    Other reports are details about the order as a whole, so I expect one page for each order, but I get the same number of pages as there are machines (one order had 36 machines so I got 36 copies of each report). Again the header only shows on the first page.
    I suspect the first problem has something to do with my join properties, but I'm not sure which join method is correct. On the second problem, I tried setting the hide duplicates property for the order number to yes, but I still get the same number of copies as there are machines.
    This isn't a big problem, I'm just trying to cut down on the number of trees I kill.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: More pages in report than expected (Access 2000 SR-1a)

    Pick one of the reports, and check the recordsource. First of all, what are the tables in the recordsource? That is, is there some other table involved that you haven't mentioned, perhaps one that is a child table to the Machine Table? Then, run the recordsource as a query, and see how many records it is returning.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More pages in report than expected (Access 2000 SR-1a)

    The report that should give me one page per machine record has a total of 4 tables and a query to make up the query which is the record source. The 1st query, Software Customer Data Query, starts with the Customers table on the left, then the Shop Orders table in the middle with a left outer join (I think that is the right terminology, it is a 3 anyway) to Customers using the Customer ID field, then the Machines table with a left outer join (3) to Shop Orders using the Order Number field.
    That query is then the basis for the Software Request Query, the other 4 tables are each linked to the Order Number field, a table called Software Request that is an inner join (1), and 3 software options tables that are all left outer joins (3).
    If I run the Software Request Query with a single Shop Order Number in the criteria with six machines on the order, I get back six identical records, which explains the six copies of the report, but how do I stop it?
    The other report, where I expect one page for the entire order regardless of the number of machines, uses an SQL statement as the top level source. In that, again it goes Customers, Shop Orders, Machines in the order as above, linked on the same fields, this time all inner joins. There is also a Report Card Body Query also inner joined to the Order Number field in Shop Orders. This query consists of two tables, Report Card which is left outer joined (3) to Shop Orders on the Order Number field. I'm not sure why I had to set it up this way, it was over 2 years ago, but I think it had something to do with using the Report Card Body Query for a data entry form as well as the report. It was also when I was very new to Access, so my logic wasn't very developed at the time.
    When I run that query, again with only one Shop Order Number as the criteria (the same order number with six machines), I get six identical records.
    I did figure out one thing though that is slightly different than my question in the original post. On the order I am currently using as the example, there are six machines but only one computer to run them so I only fill out one Software Request, but I get 6 records on the query. On the order I was working on when I made the original post, the order only had two machines, but the customer bought two computers to run them, one for each, so I filled out two Software Requests for the same order. That is when I got twice as many pages as there were machines, 2 copies for each machine.

  4. #4
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More pages in report than expected (Access 2000 SR-1a)

    Sorry, I didn't answer one of your questions. There are no child tables to the machines table in either of the queries.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: More pages in report than expected (Access 2000 SR-1a)

    I'm sorry, but there is a little information overload here. I can really only deal with one situation at a time, and it seems you are describing several.

    Now, I don't understand the purpose of those reports, but I question the Outer Joins. An outer join returns ALL the records from one of the joined tables, whether or not there is a matching record in the other table.

    Let's start with this one situation from your previous message:
    "The other report, where I expect one page for the entire order regardless of the number of machines, uses an SQL statement as the top level source. In that, again it goes Customers, Shop Orders, Machines in the order as above, linked on the same fields, this time all inner joins."

    If you are getting multiple pages, then you likely have a GroupHeader defined for Machines, and you are forcing a NewPage.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More pages in report than expected (Access 2000 SR-1a)

    Thanks for your replies and I'm sorry for the convoluted, detailed explanation. It would have been easier to show you, but I'm not sure how to strip my database to just show you the objects in question without breaking all of links.

    I double checked the two reports in question, and there are no grouping properties shown when I click the sorting/grouping icon. I don't typically use the grouping function because all of my reports are drawn from a single record.

    Most of my reports are not typical access reports, rather they mimic a Word document, instead of printing rows of data with a common entry (ie all orders shipped this week), I'm printing a report of a single order record to show how that order was configured.

    To keep my focus on a single report, I'll continue with the one you selected. My order entry process starts with a single customer (a unique record). That customer places an order (also a unique record), in this case he is ordering six machines, so six machine records get created, each with a different serial number.

    Could my problem be caused because the Order Number field in my queries is the usually the foreign key Order Number field from the Machines table, rather than the primary key field from the Shop Orders table?

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: More pages in report than expected (Access 2000 SR-1a)

    Since you have no groupings, I'm assuming you only have a detail section (and a rather large one at that) in your report? Your recordsource for this order will produce 6 records, and each detail section takes up a page, so you get 6 pages.

    Do you want all 6 machines to appear on a single page? This can be accomplished by one of 2 methods. The simplest is to create a Group Header for the OrderNo, and move all of the Customer and Order info into this header. Set the header to start a new page. Then only the machine info should appear in the detail section; which is now alot smaller.

    The other method keeps your report format as it is; that is, a single detail section. However, you need to remove all machine info and move it to a subreport. Also remove the machine table from your report's recordsource.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More pages in report than expected (Access 2000 SR-1a)

    Yes, that is what I want, the comments for all six machines, which is really all the comments for one order on a single page. I will think about this today, and try your suggestions tonight. I only modify the database design at night when no one is using it. Thanks for your input, I'll let you know if either method works.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: More pages in report than expected (Access 2000 SR-1a)

    You wrote:
    "I only modify the database design at night when no one is using it. "

    This strongly suggests your are working with a single database rather than a split database schema. This is one of the primary disadvantages of the single-database approach. You should really split the database into frontend/backend, and put a copy of the backend on each user's local drive. Then you can have a development version of frontend to run tests on (which links to a test backend). When you are ready to distribute changes, just relink to main backend and send new frontend to all users.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More pages in report than expected (Access 2000 SR-1a)

    I have read this warning/recommendation many times on this forum, and I know that it is the way I need to go. I knew when I wrote that statement that I would get that comment from you, I just haven't taken the time to look into doing it. When I started the database, it was actually replicated because we didn't have network access. Splitting it would actually help with a couple of other problems, so I will take your advice and look into it more.

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: More pages in report than expected (Access 2000 SR-1a)

    >>I knew when I wrote that statement that I would get that comment from you<<

    Yeah, there are a couple of "hot buttons" that will set some of us off!<g> Seriously, though, it is one of the most common situations we find, and properly splitting and relocating the databases generally cures a whole slew of problems.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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