Results 1 to 6 of 6
  1. #1
    laniebe is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    17

    Question Combine Multiple Fields from One Table - Union Query?

    Hello! I am trying to figure out how to combine multiple fields in one table. Because this data pulls automatically from our inventory management system, I am unable to format it differently.

    What data looks like from system:
    ContainerNumber, PartNumber, Part Quantity, Serial Num, Serial Num2, Serial Num3.... etc (up to 15).
    SCAC123456, 514-296, 3, 198797, 198798, 198799

    As you can see, we have 3 part quantity and then three serial numbers. I want to combine those serial numbers into one column for reporting purposes. If possible, I could have the container num, part num, and part quantity lines repeat and the serial numbers all in one column, I could then group / sort by container, part, and quantity and then below that would be the three serial numbers.

    Is there a way to combine the fields above to retain the header information (container, part, quantity) and then have 1 column for the 15 possible serial numbers for that shipment?



    I built a Union All Query to combine the 15 Serial Number columns but it then has no header information I can tie it back to. HELP!

    Thank you!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, that would be a UNION query. Should provide your attempted query.

    I don't see 3 part quantity, only 1 [Part Quantity] field.

    SELECT ContainerNumber, PartNumber, [Part Quantity] As PartQty, [Serial Num] AS SerNum, "Ser0" AS Source FROM tablename
    UNION SELECT ContainerNumber, PartNumber, Part Quantity, [Serial Num1], "Ser1" FROM tablename
    UNION SELECT ContainerNumber, PartNumber, Part Quantity, [Serial Num2], "Ser2" FROM tablename
    ...;


    Advise no spaces or punctuation/special characters (underscore 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.

  3. #3
    laniebe is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    17
    I have attached the database I am working with. The union query worked, EXCEPT, it is now showing a "blank" row for each line. Is there a way to remove the duplicate blank lines WITHOUT putting Is Not Null? - Reason being, some lines do not have a serial number so it may be blank for a reason. Thank you!!
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you don't want the record then have to use filter criteria. What do you have against Is Not Null?
    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.

  5. #5
    laniebe is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    17
    Normally that would be what I would choose BUT some of the records in the database have a blank in the serial number field because they are parts that do not have an assigned serial number. What I'm not sure about is why the parts that DO have an assigned serial number are producing a record with a blank serial number.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I don't understand the confusion. There are records with no value in SERIAL_NO_CHASSIS so the UNION will reflect that.
    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. Combine multiple fields into one
    By rpyee15 in forum Access
    Replies: 1
    Last Post: 05-09-2016, 08:31 AM
  2. SQL: Union Query to Combine 3 Queries
    By pdevito3 in forum Access
    Replies: 6
    Last Post: 06-03-2015, 12:53 PM
  3. Replies: 3
    Last Post: 05-29-2014, 09:50 AM
  4. Replies: 1
    Last Post: 10-23-2012, 02:04 PM
  5. Replies: 1
    Last Post: 09-13-2012, 09:31 AM

Tags for this Thread

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