Results 1 to 4 of 4
  1. #1
    kagoodwin13 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    181

    Query to find condition and return field name

    I think I may have reached a limitation with my database design. I am wondering whether I can do one of two things:



    1. Set up a query to find a condition within a record, then return the field name where the condition is met, or
    2. Transpose field names into a single field

    I realize this is the opposite of how a query normally works (find a record that meets the condition within a field), so I'm not sure if this is possible.

    I have a table that is currently set up like this:

    PART_NUMBER JAN14 FEB14 MAR14 APR14 MAY14
    1234567890 900 800 200 -100 200
    1234567891 800 -500 -400 -200 100

    I would like to find when each part number meets the condition of being less than zero.
    So output of the query would look like:

    PART_NUMBER NEGATIVE_MONTH
    1234567890 APR14
    1234567891 FEB14
    1234567891 MAR14
    1234567891 APR14


    I could possibly transpose the field names to a single field itself, like below, but it will require a lot of work, and violate the primary key (PART_NUMBER):

    PART_NUMBER VALUE MONTH
    1234567890 900 JAN14
    1234567890 800 FEB14
    1234567890 200 MAR14
    1234567890 -100 APR14
    1234567890 200 MAY14
    1234567891 800 JAN14
    1234567891 -500 FEB14
    1234567891 -400 MAR14
    1234567891 -200 APR14
    1234567891 100 MAY14


    Any ideas on how to accomplish this? Or am I stuck until I re-design my table?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The table design is not normalized structure. A normalized structure would mean 2 tables with 1 to many relationship. The second table would be dependent on the first and look like the last table in your post. That table can be generated with a UNION query.

    SELECT Part_Number, "Jan14" AS SourceMo, Jan14 AS Data FROM table
    UNION SELECT Part_Number, "Feb14", Feb14 FROM table
    UNION SELECT Part_Number, "Mar14", Mar14 FROM table
    ...;

    There is a limit of 50 SELECT lines and must be typed or copy/paste into SQL View of query builder.

    Use the UNION query in subsequent queries to sort/filter records. Or use it to make table and normalize data structure.

    Suggest not using Value and Month as field names - reserved words.

    Is this a multi-year database?
    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.

  3. #3
    kagoodwin13 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    181
    Thanks again, June7! There are only 18 months in this database, so I think the UNION query will be a good work-around.

    I'm still learning proper table normalization. I've got a lot of "Excel-itis" to rid myself of.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Recognizing the problem (Excel-itis) is a big step! LOL

    Think you will get there eventually. Good Luck.
    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.

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

Similar Threads

  1. Replies: 2
    Last Post: 09-06-2013, 06:41 AM
  2. Replies: 3
    Last Post: 11-15-2012, 06:59 PM
  3. Replies: 7
    Last Post: 05-31-2012, 11:19 AM
  4. Replies: 7
    Last Post: 08-03-2011, 08:00 AM
  5. Replies: 6
    Last Post: 12-01-2010, 06:52 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