Results 1 to 2 of 2
  1. #1
    eastharbour is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    1

    New to forum - assistance requested on building query with multiple lines based on multiple columns

    I have a query in Access 2016 - I need to create a query to use as a lookup to another table. Below is the a prior example of my query and the after with the data I would like to generate. Is this possible?



    Subscriber Type Subscriber Name Category1 Category2 Category3 Category4 Category5 Category6 Category7 Category8 Volume Level
    1234 Subscriber 1 Name a c d 1 m w 8m pc low
    1235 Subscriber 2 Name b medium
    1236 Subscriber 3 Name a c low
    1237 Subscriber 4 Name b medium
    1238 Subscriber 5 Name d high


    Lookup Subscriber Type Subscriber Name Category2 Category3 Category4 Category5 Category6 Category7 Category8 Volume Level
    1234-a 1234 Subscriber 1 Name a c d 1 m w 8m pc low
    1234-c 1234 Subscriber 1 Name a c d 1 m w 8m pc low
    1234-d 1234 Subscriber 1 Name a c d 1 m w 8m pc low
    1234-1 1234 Subscriber 1 Name a c d 1 m w 8m pc low
    1234-m 1234 Subscriber 1 Name a c d 1 m w 8m pc low
    1234-w 1234 Subscriber 1 Name a c d 1 m w 8m pc low
    1234-8m 1234 Subscriber 1 Name a c d 1 m w 8m pc low
    1234-pc 1234 Subscriber 1 Name a c d 1 m w 8m pc low
    1235-b 1235 Subscriber 2 Name b medium
    1236-a 1236 Subscriber 1 Name a c d 1 m w 8m pc low
    1236-c 1236 Subscriber 3 Name a c low
    1237-b 1237 Subscriber 4 Name b medium
    1238-d 1237 Subscriber 5 Name d high

  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,825
    A UNION query could generate that output.

    Why do the category field names change in the output? Was this a posting error?

    SELECT [Subscriber Type] & "-" & Category1 AS Lookup, [Subscriber Type] As SubType, [Subscriber Name] AS SubName, Category1 AS Cat1, Category2 AS Cat2, Category3 AS Cat3, Category4 AS Cat4, Category5 AS Cat5, Category6 AS Cat6, Category7 AS Cat7, Category8 AS Cat8, [Volume Level] AS VolLvl FROM tablename
    UNION SELECT [Subscriber Type] & "-" & Category2, [Subscriber Type], [Subscriber Name], Category1, Category2, Category3, Category4, Category5, Category6, Category7, Category8, [Volume Level] FROM tablename
    ...;

    Advise no spaces or punctuation/special characters (underscore is only exception) in naming convention.
    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: 3
    Last Post: 10-18-2016, 08:31 PM
  2. Replies: 3
    Last Post: 08-09-2016, 01:52 PM
  3. Replies: 4
    Last Post: 10-13-2014, 09:20 AM
  4. Replies: 5
    Last Post: 09-11-2014, 10:25 AM
  5. Replies: 7
    Last Post: 01-02-2013, 11:23 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