Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Change Field name in table/query based on another table in access

    Hello.
    I have an access DB (I know no VBA but can copy and paste) In it I have a query with field names I need changed before I spit out a report.

    For example:

    Query1 (could be made into a table if that helps)
    Item
    Description
    1
    2
    3
    4

    Table:
    Wk#
    Date

    In the above table it always is numbered in WK# column starting with 1, 2, 3, etc but date for 1, 2, 3 etc will be a different date each week. The table is create earlier in the DB based on data feed to it.

    I need to take the final query (Query1) and change the field names from 1 to whatever date is in the table for 1, 2 to whatever date is in 2, etc.

    Any ideas? I understand queries, intermediate macros but very little VBA (just starting to learn)

    Thanks!!!

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I don't understand what you want, a diagram would help.

    Maybe some form of crosstab query is what you need?
    Last edited by patt; 2014-08-09 at 03:11.

  4. #3
    New Lounger
    Join Date
    Aug 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello. A cross tab only works with one table to my knowledge and I have a table and a query.

    The table is called DATES and has two fields, date and week#. WK# is always 1 - XXX. The dates change. So this week #1 is 8/11/14, next week week#1 will be 8/18/14, etc.

    DATES
    Date WK#
    8/11 1
    8/18 2
    8/25 3


    My query is called FINAL, it has 20 fields, they are:
    Item
    Description
    1
    2
    3
    4
    5
    6
    7
    8
    9
    etc to 18


    What I need is a table/query that has:
    Item
    Description
    8/11/14 (instead on 1)
    8/18/14 (instead of 2)
    8/25/14
    etc

    I have not been able to figure out how to replace the field names with whatever date is in the corresponding firld in the DATES table.

    Does this help?
    Attached Images Attached Images

  5. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Does this do what you want?

    SELECT d.[Date] as ItemDescription
    FROM Dates as d INNER JOIN FINAL as f ON d.[WK#] = f.ItemDescription
    ORDER BY d.[WK#]

Posting Permissions

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