Results 1 to 6 of 6
  1. #1
    sjs94704 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2012
    Location
    Berkeley, CA
    Posts
    20

    How do I combine fields together and alphabetize the list?

    I have a database that keeps track of my music.


    Some artists are individual people and others are musical groups.
    What I have are fields as follows:

    FirstName
    LastName
    Group
    GenreID
    ActivelyRecording
    SongsSetUp
    SongsDownloaded

    So, in a query I want it to look like this:

    Carey, Mariah
    Coldplay
    Midler, Bette
    Mr. Mister
    Presley, Elvis

    So, if the group field is empty then the name is the individuals name. If the individuals name fields (First and Last) are empty and there is something in the Group field then the group name is put in the list.

    I know how to set up a query, but, I just need to know what the syntax should look like to get the results above.

    In my case, if the entry is a group then the first and last name fields are blank with nothing in them. Conversely, if the entry is a group then the first and last name fields are empty.

    I would be grateful for anyone's help on this matter.

    With gratitude,

    Steven S.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What if the artist has a single name? Prince?
    (not a fan, but that's who came to mind)
    Would you use only 1 artist name field, or the group? Or something else? That will make a difference. What you might need is to use the IIF function.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    sjs94704 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Nov 2012
    Location
    Berkeley, CA
    Posts
    20
    If I have an artist with a single name like Cher or as you said Prince I would put that in the Group field.

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Try this:
    Code:
    Artist: [LastName] & (", "+[FirstName]) & [Group]

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Or Artist: IIf(isnull([Group]),[LastName] & (", " + [FirstName]),[Group])

    Cheers,
    Vlad

  6. #6
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Ι refrained from any logical contition having in my mind these two possible situations:
    1. If artist is a group, LastName and FirstName are null for sure.
    2. If artist is individual person, Group is surely Null and maybe and the FirstName.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-22-2015, 12:28 PM
  2. Query to Select Records and Alphabetize them
    By MarcieFess in forum Queries
    Replies: 2
    Last Post: 10-24-2013, 09:34 AM
  3. Replies: 4
    Last Post: 08-06-2012, 07:05 PM
  4. Combine two fields, Null fields involved
    By yes sir in forum Access
    Replies: 9
    Last Post: 10-03-2010, 09:20 AM
  5. Alphabetize Information
    By caljohn527 in forum Forms
    Replies: 1
    Last Post: 02-11-2009, 10:50 PM

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