Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export Access to Excel - Subtotals (Access 2003)

    I have Access 2003. I am trying to export an Access report over to Excel. I have groupings with sub totals within the report. The problem I am having is that when the file is exported over to Excel, the sub total column shifts over one column. Does anyone know how I could get the amount columns with the sub totals to stay within there own column. I am putting the subtotals in the page footers.

    I

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

    Re: Export Access to Excel - Subtotals (Access 2003)

    Welcome to Woody's Lounge!

    1) Try placing the subtotals in the group footer instead of in the page footer.
    2) Try moving the text box displaying the subtotal a bit to the left or right, depending on how it is shifted in the export.

    If you want to apply an Excel template, you'll have to use Automation to control Excel from Access. If you search this forum for Excel.Application, you'll find examples. Post back if you have questions.

  3. #3
    New Lounger
    Join Date
    Aug 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Access to Excel - Subtotals (Access 2003)

    Hi Hans,
    Thanks for the reply back.

    The subtotals are in the group footer. After playing with the report, I noticed that if i take out the report footer the subtotals do come out right when I do the export. Two problems come up. I need the grand totals in the report header. The second problem I notice which is really important is that when I export my report I never get any label coming from my report into Excel. So the words "Sum Total" is not coming up, and my headings on my pages aren't coming up either.....

    Any advice would be greatly appreciated.

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

    Re: Export Access to Excel - Subtotals (Access 2003)

    I'm afraid that the facilities Access offers for exporting a report are rather weak. A lot gets lost in the translation, and there is little or nothing you can do about that. You may fare better if you export the underlying table or query to Excel, and use Excel's optiions for summarizing data (subtotals, pivot tables).

  5. #5
    New Lounger
    Join Date
    Aug 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Access to Excel - Subtotals (Access 2003)

    Hans,

    If I had like a group called "cost centers", in a query how would I write a formula that it would group each subtotal of each cost center.

    Also, when I run my report and I export it, if I take out the report footer, the columns come out right, but I don't get the grand total at the end. How would I get a grand total at the end without a report footer.

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

    Re: Export Access to Excel - Subtotals (Access 2003)

    The Data | Subtotals... feature in Excel lets you create group totals and a grand total.

  7. #7
    New Lounger
    Join Date
    Aug 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Access to Excel - Subtotals (Access 2003)

    Hans,

    I tried to use the subtotals option, but because there are spaces between them, it seems to be giving me a "0" amount each time. Any suggestions,

    Do you know any possible way to keep the report header in and keeping my subtotal amounts together in my Access Report

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

    Re: Export Access to Excel - Subtotals (Access 2003)

    I don't think you should try to export a report. As I noted before, the export feature for reports is weak and limited. Instead, export a table or query, and create the (sub)totals in Excel, either interactively or using code.

Posting Permissions

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