Results 1 to 8 of 8
  1. #1
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28

    Upload a Dynamic Crosstab Based Query to Excel

    I have created the attached report from a select query, for inventory levels of items that have stock, joined to a crosstab query, that shows the sales level for each of the last 12 months. The last 12 months will change every month. For the report, in the crosstab query, I used fixed column headings, e.g., Per1, Per2 , etc.. I used formulae in the report headings to show the actual period name, e.g. 201909, 201910, etc.


    My problem is that the users now want this data sent to Excel with the actual period name rather than the fixed column heading. Tried OutputTo and Transferspreadsheet but they both carry over the fixed column head e.g. Per1, Per2, etc.. Have done a lot of googling to see if I could score a snippet, but to no avail.


    Any assistance would be appreciated.

    The SQL for the query I used for the report is below:

    SELECT [777tblforreport].Rep, [777tblforreport].Cust, [777tblforreport].CustItem, [777qtyOrd2].OdrQty, [777tblforreport].[Spec#], [777tblforreport].Compnt, [777tblforreport].Stocked, [777tblforreport].skid, [777tblforreport].OnHand, [777tblforreport].CustPO, [777tblforreport].LstInv, [777tblforreport].Dt_of_Per1, [777tblforreport].Per1, [777tblforreport].Per2, [777tblforreport].Per3, [777tblforreport].Per4, [777tblforreport].Per5, [777tblforreport].Per6, [777tblforreport].Per7, [777tblforreport].Per8, [777tblforreport].Per9, [777tblforreport].Per10, [777tblforreport].Per11, [777tblforreport].Per12, [777tblforreport].Dt_of_Per12, [777tblforreport].DaysInInvty, [777tblforreport].ageddaysused AS AgingDaysUse, [777tblforreport].TodaysDate
    FROM 777tblforreport LEFT JOIN 777qtyOrd2 ON ([777tblforreport].[Spec#] = [777qtyOrd2].INVBITEMNO) AND ([777tblforreport].CustNo = [777qtyOrd2].CustNo)
    WHERE ((([777tblforreport].Rep) Like "*" & Forms![777FrmMainMenu]!Combo12 & "*") And (([777tblforreport].Cust) Like "*" & Forms![777FrmMainMenu]!Combo43 & "*"))
    ORDER BY [777tblforreport].Rep, [777tblforreport].Cust, [777tblforreport].CustItem;
    Attached Files Attached Files

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you could try a union query and export without headers

    SELECT TOP 1 1 AS Ord, "Rep" AS F1, "Cust" As F2, "CustItem" AS F3,....yourFormulaHere for Per1 AS F12, yourFormulaHere for Per2 AS F13,.... FROM [777tblforreport]

    UNION SELECT 2, [777tblforreport].Rep, [777tblforreport].Cust, [777tblforreport].CustItem, .....

    ORDER BY Ord

    You may not need the Ord column if you can sort your data on another column - requirement being the data is numeric, the sort is DESC and you don't mind the order being based on that column

    Otherwise think you will need to create your sql in vba or have vba code to open the export file and change the headings

    sql in vba might include something like this

    "..........[777tblforreport].Per1 AS " & yourFormulaHere & ", [777tblforreport].Per2......"

  3. #3
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Thanks for your reply Ajax
    .

    I have changed my tack, as you suggested, and have uploaded the file with the Transferspreadsheet method. This brings all the data over fine but I still need to change the headings for the 12 months of sales from Per1, Per2, etc. to e.g.202001, 202002, etc.. I have these desired heading, using a formula to get them, hidden on a form.

    I am looking for a snippet via Excel Automation that will open the Excel file and either replace the 12 sales headers or delete the entire first row and replace it with the desired headers.

    I have uploaded an Excel file that shows what heads came over with Transferspreadsheet and what headings I would like in there. I also uploaded a file that shows the form where I wish to get the sales months' headings from.

    As usual, any assistance would be helpful.
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Use Excel automation to manipulate Excel file after created. There are many examples. Take this in steps.
    1. learn how to automate Excel with VBA in Access
    2. incorporate code to populate Excel cells with header text

    Or modify query with QueryDefs. Review example of building a crosstab with dynamic headers https://www.fmsinc.com/MicrosoftAcce...ort/index.html
    Try referencing control on form for [Start Date] parameter. Otherwise, use VBA and QueryDefs to modify query to use a static date parameter.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    there are plenty of threads on the subject of opening an excel file from access and making changes to cells. This is a different topic from your initial question so recommend google something like 'open excel from access'. This is an example https://gamblisfx.com/open-excel-fil...ess-using-vba/ Alternatively start a new thread since it is a different topic.

  6. #6
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Thanks for your assistance. I did find some Excel Automation code that gave me ideas to solve my problem.

  7. #7
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Thanks for your assistance. I did find some Excel Automation code that gave me ideas to solve my problem.


  8. #8
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Thanks for your assistance Ajax. I did find some Excel Automation code that gave me ideas to solve my problem.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using A Dynamic Crosstab Query On A Form
    By lccrews in forum Forms
    Replies: 9
    Last Post: 09-24-2018, 07:35 PM
  2. Replies: 1
    Last Post: 02-22-2017, 03:39 AM
  3. Replies: 1
    Last Post: 01-26-2017, 08:29 AM
  4. Dynamic CrossTab Report from Query
    By ZJGMoparman in forum Programming
    Replies: 1
    Last Post: 08-20-2015, 10:54 AM
  5. Replies: 1
    Last Post: 07-30-2010, 10:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums