Results 1 to 5 of 5
  1. #1
    Rustin788 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    47

    Transposing Linked Data

    Hey,



    So I have several large budget tables like below with the months going through to December.

    GLCode GLCode-Description Location Category Jan Feb Etc… Dec
    450-01 Salaries 1 A 50,5 50,5 Etc… 505
    450-03 Hourly Labor 1 A 505 505 Etc… 505

    I am going to be taking over some monthly Actual vs Budget reporting and would like to automate it by connecting the budget to our SQL database. I was wondering if there was a way to transpose the monthly data while somehow linking it to the GL Code. There are a couples way I can think to do it manually just wanted to check and see if there was an easier way.

    Thanks for any advice.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a select query to pull the data you want, qsData.

    then use the query wizard and create a CROSSTAB query, that uses the qsData query as source data.
    you will pick the row header fields,
    then col header fld,
    and the values.

    save this query, put it in a macro to run every month.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    transpose which way?

    if your data is as presented, with months going across the top aka excel and you want a database view, then you will need multiple queries, one for each column. These can be combined as a union query or update a table

    If the other way, then as Ranman suggests

  4. #4
    Rustin788 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    47
    Thanks I will give that a try.

    I need to look like this:
    GLCode GLCode-Description Location Category Month Budget
    4509-031 Salaries 1 A Jan 505
    4509-031 Salaries 1 A Feb 505
    4509-031 Salaries 1 A Etc…
    4509-031 Salaries 1 A Dec 505
    4509-030 Hourly Labor 1 A Jan 505

    It will connect to our database much easier and will make the reports nicer because I will be able to include Month dropdown list so instead of keeping 12 different reports by the end of the year everyone just needs the one.

  5. #5
    Rustin788 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Posts
    47
    Quote Originally Posted by Ajax View Post
    transpose which way?

    if your data is as presented, with months going across the top aka excel and you want a database view, then you will need multiple queries, one for each column. These can be combined as a union query or update a table

    If the other way, then as Ranman suggests
    Believe I have it figured out. I created a table that just had the months listed and then did a query with my main table that way each GLCode had a row for each month. From There I am just creating a separate cross tab for each Month and then union them together at the end.

    Thank you both for the help.

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

Similar Threads

  1. Help! Transposing Data via Cross Tab Query?
    By William McKinley in forum Queries
    Replies: 3
    Last Post: 03-03-2015, 10:45 AM
  2. transposing data in a report
    By brianrunsphilly in forum Reports
    Replies: 3
    Last Post: 10-23-2013, 02:22 PM
  3. Transposing Columns to Rows
    By hycho in forum Programming
    Replies: 5
    Last Post: 07-19-2012, 01:17 AM
  4. "transposing vertical data into horizontal columns"
    By stevelondon in forum Queries
    Replies: 3
    Last Post: 03-20-2012, 12:36 PM
  5. Transposing Data
    By NoiCe in forum Queries
    Replies: 0
    Last Post: 11-09-2008, 05:24 PM

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