Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports Help (A2k)

    Hi,

    As referenced in <post#=284911>post 284911</post#>, I need to create a main report with 3 subreports. This is my first attempt at creating a report. When I used my first table to create the main form, it returns all records in that table on one report. I need a separate report on a separate page for each record in my tables. Any help??

    Thanks for any help!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports Help (A2k)

    In design view, click on the gray bar at the top of the Detail section. In the Format tab of the Properties window, you can set the Force New Page property to Before Section.

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports Help (A2k)

    Thanks Hans. As I'm working through this, I just opened up the subreport wizard and it gives the option of using my existing subforms as subreports. Is this the best way to proceed, or shall I just do subreports based on my tables?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports Help (A2k)

    I would certainly use the Report Wizard to suggest report designs for you. You can always tweak them yourself afterwards, but the Wizard is a good way to learn how reports with grouping levels, totals etc. are constructed.

  5. #5
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports Help (A2k)

    Can you give me brief explanation of grouping levels?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports Help (A2k)

    Say that you have a table containing information about employees. Apart from identifying data such as name, date of birth, social security number etc., each employee belongs to a department and each employee has a monthly salary.

    When you create a report based on the employee table, you can group the data on department. This means that Access will automatically sort the table such that all employees from the same department are displayed together. You can include department info in the group header or footer, so that it doesn't have to be repeated for each individual record, and you can also include summary information about the employees in the group header or footer, such as the number of employees in each department, or the total or average monthly salary, etc. If you put a text box with control source =Sum([Salary]) in the group footer, the sum will automatically be calculated over the employees in each group (=department); you don't have to specify that in the expression explicitly.

    The Report Wizard will automatically propose to group a report for you, but you can also set or change grouping levels through the Sorting and Grouping window. This dialog is specific to reports, it doesn't exist for forms. You display it by View | Sorting and Grouping or by clicking the corresponding button on the toolbar.

  7. #7
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports Help (A2k)

    Thanks, that clears that up!

    Is it better to use my existing subforms as the datasource for my subreports, as this is exactly the info I want to display? Or would it be better to use the tables as the datasource for the subreports? Currently I have the subforms in as the subreports, but when I go to view the report I get an error message reading: The section width is greater than the page width and there are no items in the additional space, so some pages may be blank. All I can view is the information in the report header. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> once again!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports Help (A2k)

    Although it is possible to use (sub)forms as subreports, it's better to create subreports from scratch (interactively or using one of the Report Wizards) since forms are quite different from reports. The amount of tweaking to be done is usually not worth the time spent on it.

    The "section width is greater than the page width" can often be remedied by dragging the right edge of the report to the left as far as possible, and if necessary moving some controls a bit to the left. You can easily calculate how wide the report can be: with 1" left and right margins, there are 8.5"-2x1" = 6.5" left in a portrait report on Letter paper, and 11"-2x1" = 9" in a landscape report. If the report is wider than that, you must decrease the margins or you'll run into problems.

  9. #9
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports Help (A2k)

    Wonderful Hans, thank you so much for the advice!

  10. #10
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports Help (A2k)

    I want to use a text box to concatenate Name and Address information. Do I need separate text boxes for the name info and the address info, or can it be completed in one box with the hard returns and commas necessary?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports Help (A2k)

    You can do either.

    1. You can put name, company, street+street number and state+zip in separate text boxes. If you set the CanShrink property of the text boxes and of the section they are in to Yes, the space occupied by empty text boxes will be eliminated, text boxes below will move up to fill the space.

    2. You can create a single text box and set the control source to something like this:

    =[FirstName] & " " & [LastName] & Chr(13) & Chr(10) & [StreetAddress] & Chr(13) & Chr(10) & [State] & " " & [ZipCode]

    Chr(13) & Chr(10) corresponds to a hard return (carriage returns/line feed).

  12. #12
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports Help (A2k)

    Are those spaces inbetween the " "? I'm also going to need to build in IIF(IsNull) for things such as middle initial, suffix, degree and suite number in the address. I believe that "" denotes an empty string. I've been trying to use the expression builder, and if I don't use any of the IsNull functions, it returns the info I want, but otherwise I get #Error.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports Help (A2k)

    There is a handy trick that you can use if you want to concatenate strings, some of which can be empty.<pre>If MiddleInitial is "A.", [MiddleInitial] & " " equals "A. "
    If MiddleInitial is Null (empty), [MiddleInitial] & " " equals " ".
    This is undesirable when concatenating with other strings.
    But ([MiddleInitial]+" ") will equal "A. " and Null, respectively.</pre>

    So for a complete name, you can use something like this:<pre>([FirstName]+" ") & ([MiddleInitial]+" ") & [LastName] & (" "+[Suffix]) & (" "+[Degree])</pre>

    Similarly for the address.

  14. #14
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports Help (A2k)

    Hans, you da bomb! <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Reports Help (A2k)

    Post your expression here for all to see.

    You can use the + character for concatenation of strings and it also takes care of nulls.
    eg. if you had "Patrick" in the FirstName and nothing in MiddleName and "Thorn" in LastName then the following expression returns "Patrick Thorn":
    =FirstName & " " & (MiddleName + " ") & LastName

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
  •