Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Texas, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transfer Records to Excel based on Field values (2000,SR1)

    I have an Access table which has records for 25 groups in my department. Each month, I send out an XL file with 26 worksheets (1 for each group and a Totals sheet) for their expenditures for the month. There's a field in my table that has the group's name and the worksheets have corresponding names, but I currently send the data to an empty XL sheet, then cut and paste each group's records into their worksheet.

    I know I can break the table up into 25 separate tables and programatically send each group's records to their worksheet, but I was wondering is there an easier way to do this based on the values in the table and the name of the worksheet? Or I guess I could create and delete the 25 tables when I'm done each month.

    Thanks.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Records to Excel based on Field values (2000,SR1)

    Couldn't you just transfer the contents of 25 different queries run on the one table? I assume you could differentiate each of the 25 groups in some way so that you could run one query for each group? Then a loop would transfer each recordset to XL.
    Peter

  3. #3
    New Lounger
    Join Date
    Apr 2001
    Location
    Dacula, GA, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Records to Excel based on Field values (2000,SR1)

    Could an access pivot table provide what you want?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transfer Records to Excel based on Field values (2000,SR1)

    You can use a parameter query to separate the records for each group and then export the result to an XL worksheet. Then just import each one into the workbook you're sending out.
    Charlotte

Posting Permissions

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