Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Very basic question (Access 2000)

    Learning to apply Access after reading (some time ago) tutorials is interesting. Basic question concerning the difference between a query and a report. As an Excel user I'm used to putting subtotals and grand totals anywhere I want on a spreadsheet. Now, I'm in Access with a Table. Now I want to see the details by asset with totals and subtotals by account/branch for my cost and depreciation and a grand total for everything now in the database. Can a Query provide those on-screen, or do I have to do a Report instead? I cannot get the query to show me subtotals by branch with account grand totals. I may be misdesigning the whole deal. Thanks.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Very basic question (Access 2000)

    You need the report.

    Each row of data in a query has the same structure, so a query either returns raw data , or it returns subtotal, or totals, but not all three at once.

    A report can do this.
    Regards
    John



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

    Re: Very basic question (Access 2000)

    Access is quite different from Excel, despite the superficial similarity between the row and column layout of a table and of a spreadsheet. So it is to be expected that you have to get used to Access.

    A select query is a way to retrieve records, with the added ability to combine tables, perform calculations within a record, and to filter, sort and group records. But queries do not have facilities for subtotals by group or grand totals together with the individual records. (You can create queries that return ONLY subtotals or totals.) Forms aren't very good at it either. This is what reports are for. They offer lots of possibilities for reporting counts, averages, sums etc. by group and overall.

    Post back if you need more info, or have a specific question.

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very basic question (Access 2000)

    OK, thanks for the guidance-at least I'm thinking correctly about objects in Access. I am fiddiling around with the reports. I seem to be on my hands and knees getting these controls lined up...the "styles" they've got are strictly for show. I need to grind out thousands of lines on a report in a tight format with no gooey flower pics or fancy fonts on them. To that end: Where do I set the Portrait/Landscape property? I need landscape on 11x17 paper. How do I keep the "row height" tight? And are there tricks to getting these headers and the sum controls associated with them properly lined up? Thanks again!

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

    Re: Very basic question (Access 2000)

    Page size and page orientation is set in the Page tab of File | Page Setup... the other tabs of this dialog are Margins and Columns. If you want to set up a multi-column report, the Columns tab is where you specify the number of columns, their spacing etc.

    You can use the Report Wizard to do part of the design work for you, then fine tune the design manually. The Format menu has several items (with submenus) that are useful when working with multiple controls: Align, Size, Horizontal Spacing and Vertical Spacing. Note that these items are only enabled if you have selected more than one control.

    To get a lot of data on one page:
    <LI>Use a small font: 8 points, or even 7 points - the latter is hard to read on-screen but prints well.
    <LI>Don't make controls taller than necessary.
    <LI>If a control can contain more than one line, set the Can Grow property of both the control and of the Detail section to Yes.
    <LI>Don't leave unnecessary extra space above or below the controls in the detail section.

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very basic question (Access 2000)

    OK, thanks. I'll wrestle with that. I can get subtotals and so on, as well. Can access be used to circumvent the row limits of Excel 2002 pivot tables? I'm often getting those "Excel cannot handle this many unique items" messages when I'm doing a big project. That's one reason I'm here in Access.

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

    Re: Very basic question (Access 2000)

    Access 2000 doesn't have pivot tables (they were introduced in Access 2002), but it does have crosstab queries that are like pivot tables without the ability to drag the fields from row header to column header etc. I'm not aware of a limit on the number of items in a row header.

Posting Permissions

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