Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export to Excel (2000-SR1)

    I need to generate a report that will need to be in a regular report format, but also to export it to an Excel spreadsheet that will be emailed to a vendor to place an order.
    The report from [tblOrderDetails] needs to have a left-hand column showing the name of the item ordered, the next column needs to show the ItemQty (but these are made up of up to three ItemID that are of a similar type; Item ID = 2, 11 or 20, e.g.) Another column would be the price charged per item, the last column would be the total for that item (or set of items, in this case). Row 2 then would have the next item (or set of items) ordered.
    Does all this need to be done first in a query or all on the report, or what? Then how to make it into a spreadsheet?

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

    Re: Export to Excel (2000-SR1)

    It's probably best to create a query that returns the data you need for the report, and to export the query to Excel instead of the report.
    It's not clear to me what you want the second column to show: the individual quantities or the total quantity.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel (2000-SR1)

    I attached a sample as spreadsheet to help me sort this out. What I have in mind with the second column would be the total number of items matching the ItemID (say, 2 of Item 2, 3 of Item 11 and 5 of Item 20, making the total of 10). Sorry to be so dense, but I'm not visualizing how to set this up right.
    Warren
    Attached Files Attached Files

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

    Re: Export to Excel (2000-SR1)

    How do you specify that (for example) ItemIDs 2, 11 and 20 belong together?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export to Excel (2000-SR1)

    The only way I have thus far is by specifying these together in the criteria field in a query. Up till now, I didn't need to include more than one of these groupings in a report, so I have individual queries for each of these groupings. I take it that there would be a better way, like assigning each item a group ID in the tblItems, then running the query based on a GroupID, rather than multiple criteria, or something like that?

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

    Re: Export to Excel (2000-SR1)

    I agree with Hans. The best way is to use a separate query for the Export.

    What I do is make the query behind the report into a stand-alone query. So if my report is something like "rptProductList", my query would be "qryrptProductList". I then have a "qryExportToExcel", and I on-the-fly change the SQL prior to export. Something like this:

    Currentdb.tabledefs("qryExportToExcel").SQL = "SELECT ProductID, ProductDescription, QtyOnHand FROM qryrptProductList"

    I can add GROUP BY and ORDER BY clauses as needed. Then export qryExportToExcel using Docmd.TransferSpreadsheet method.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Export to Excel (2000-SR1)

    Yes, I'd use a GroupID field. That way, you can use a straightforward Totals query that groups on GroupID.

Posting Permissions

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