Results 1 to 4 of 4
  1. #1
    tozey is offline Novice
    Windows XP Access 97
    Join Date
    Aug 2010
    Posts
    14

    Make table containing specific column based on variable

    Hi all, hopefully the title makes sense.



    I am looking for the best way to create a daily table of data (snapshot for edit from a static table) based on what day it is.

    So, for example in a table I have

    | Name | Monday | Tuesday | Wednesday | Thursday | Friday |

    Each day has different set values for each Name

    So I would like to make a new table (make table query, or even an append into another table) with the Name and whatever day I want to choose, whether it be from a combo or it could be based on the current day, I just don't know how to call the specific column in the query.

    I have searched the forums but cannot find anything, I cant help but think this is a lot easier than I think and just missing a simple trick.

    Any help very much appreciated.

    thanks

    Alex

  2. #2
    tozey is offline Novice
    Windows XP Access 97
    Join Date
    Aug 2010
    Posts
    14
    I should have mentioned I have more than 7 columns I would like to potentially call so a simple nested iif didnt work as limited to 7. I am looking at 20 columns based 4 weeks worth of week days. Week 1 Monday, Week 1 Tuesday and so on.

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I am not sure exactly what your data looks like, but it sounds like you may have some design issues, and may be working with data that is not normalized, which can make things much harder than they need to be.

    See these links for Design basics and the Rules or Normalization:
    http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf
    https://support.office.com/en-us/art...4-bd4f9c9ca1f5

    Basically, you typically should not have separate columns for each day of the week. You should instead have a Weekday field, and then a Value field. So you will have less fields, but more records.

    Also, you typically should not have to be creating a whole bunch of tables in the production environment. If your tables are of similar structure, that is often a sign of a design issue. Most of the time, you can get what you need through select queries, and do not need to use Make Table queries much in production.

  4. #4
    tozey is offline Novice
    Windows XP Access 97
    Join Date
    Aug 2010
    Posts
    14
    Thank you

    I have adopted this database and re-building parts, the thought had crossed my mind to change the data structure and you have confirmed it.

    So I have done it, and it didnt take that long.

    Thank you

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

Similar Threads

  1. Replies: 1
    Last Post: 02-19-2016, 08:16 AM
  2. Replies: 3
    Last Post: 12-21-2015, 08:03 AM
  3. Replies: 4
    Last Post: 04-20-2013, 10:12 AM
  4. Replies: 8
    Last Post: 12-05-2012, 07:24 PM
  5. Replies: 1
    Last Post: 03-14-2011, 10:04 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