Results 1 to 2 of 2
  1. #1
    Jesspedersen is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    2

    Dynamic headings on Make-Table Query based on Crosstab query

    Hi,


    I have a problem with a query which is based on a crosstab query.

    First of all, the SQL code from the crosstab query is as follows:



    TRANSFORM Count([FG-SalesOrders].QTY) AS CountOfQTY
    SELECT [FG-SalesOrders].ITEMID
    FROM [FG-SalesOrders]
    GROUP BY [FG-SalesOrders].ITEMID
    PIVOT Format([Dateexpected],"yyyy/mm");

    This splits my transactional data into one column with Item numbers and several columns with date intervals (e.g. 2009-11, 2009-12, 2010-01 etc.).

    I have an Make-Table query where I would like to generate the following columns:

    Item number
    Februar sales orders
    March sales orders
    April sales orders
    May sales orders
    June sales orders
    July sales orders

    (Last five months sales including current month)

    and also some other information from another crosstab query with production orders also grouped by months as the above. The end result should therefore be produced in a new table with the following headings, when this is run in July:


    Item number
    Februar sales orders
    March sales orders
    April sales orders
    May sales orders
    June sales orders
    July sales orders
    Februar Production orders
    March Production orders
    April Production orders
    May Production orders
    June Production orders
    July Production orders


    I have found a tutorial of how to produce these dynamic headings if it should be in a report (by using DateAdd). But this is a make-table query since I need the data for further analysis.

    Does anyone have a solution for this?

    It would be very much appreciated - thank you!

    Best Regards
    Jess Pedersen
    Last edited by Jesspedersen; 07-28-2010 at 04:31 AM.

  2. #2
    Jesspedersen is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    2
    Is there anyone which has previously been dealing with the above problem? It would help a lot, since I have been struggeling with this problem for a few days...

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

Similar Threads

  1. make a temp table from crosstab query
    By stigmatized in forum Programming
    Replies: 0
    Last Post: 07-26-2010, 03:01 PM
  2. Dynamic Column Headers but NOT CROSSTAB
    By jtkjames in forum Queries
    Replies: 1
    Last Post: 07-26-2010, 05:16 AM
  3. Make table query
    By Zahed in forum Queries
    Replies: 1
    Last Post: 11-06-2009, 05:44 AM
  4. Replies: 2
    Last Post: 10-27-2009, 07:09 AM
  5. Make a table query
    By Fiona in forum Access
    Replies: 4
    Last Post: 06-25-2009, 11:24 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