Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jun 2007
    Posts
    8

    Question Unanswered: Sorting with Double Standards

    I want to sort songs by genre. Then by bpm(beats per minute). But if two songs have the same name AND at least one common artist, then sort by song remix then bpm as long as "this" bpm is greater than the previous song.


    Here are my table definitions.
    Code:
    tbl_songs     tbl_genres          jtbl_song_artist     tbl_artists
    sid           gid                 sid                  aid
    bpm           genre               aid                  artist
    gid
    title
    remix
    Some records
    Code:
    tbl_genres
    gid    genre
    1      HipHop
    2      Country
    3      Reggae
    
    
    tbl_artists
    aid    artist
    20     50 Cent
    21     Sean Paul
    22     Brooks And Dunn
    23     Ciara
    
    tbl_songs
    sid   bpm     gid    title            remix
    35    104     1      Candy Shop
    42    120     1      Candy Shop
    36    99      1      Candy Shop       (FUNKYMIX)
    41    99      1      Candy Shop       (X MIX)
    37    100     2      Save A Horse
    38    87      1      Promise          (BLEND)
    39    100     1      2 Step
    42    103     1      1 Step
    40    60      3      Deport Dem
    Code:
    jtbl_song_artist
    sid   aid
    35    20
    41    20
    36    20
    36    21
    37    22
    41    22
    42    23
    The Result I'm looking for should look like this
    Code:
    genre          bpm         title            remix
    Country        100         Save A Horse
    HipHop         87          Promise          (BLEND)
    HipHop         104         Candy Shop
    HipHop         99          Candy Shop       (FUNKYMIX)
    HipHop         99          Candy Shop       (X MIX)
    HipHop         100         2 Step
    HipHop         103         1 Step
    HipHop         120         Candy Shop
    Reggae         60          Deport Dem
    I can do this outside of MS Access, but was wondering if it could be done with a creative SELECT statement.

    Any help would be greatly appreciated

    p.s.
    Is there an easy way to line up these "CODE" columns or display results, when posting a thread?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sure - when you construct your sql statement for the query, you can specify the sort order....i.e:

    ...test criteria...

    strSQL = "Select * from ..... where .... Order By SomeFieldName"
    else
    strSQL = "Select * from ..... where .... Order By SomeFieldName Desc"

    but I have a feeling your question is a little more involved than that. If you need to manipulate sort order within the recordset itself sometimes sorting on one field and other times sorting on another field (or a combination of some fields), you might want to consider opening the recordset in vba code in one sorted way, looping through the records (testing your logic on each record/against the previous record), and writing to a "temp" table with a field designating the correct order. For example, as you loop through the records, maybe you can combine one field or more fields with another (i.e. genre + bmp) else (song remix + bmp) or (bmp + prevRecbmp + genre) or (prevBmpRec + bmp + genre), etc.. writing to a "SortBy" field in the temp table to produce the sort you want. Then open the "temp" table with it sorted by the "SortBy" field. The key will be opening your initial recordset sorted correctly and as you loop through the records, and as you test the logic, finding the right combination of the fields (saving the previous rec in a variable if you want to match against it) to produce the correct final "SortBy" order you want.

    It sounds like it might get messy but if your initial recordset is sorted right, you should be able to find the right combination and just have to run it through trials opening the final temp table to see if your logic for the "SortBy" field is correctly constructed.

    I hope that gives you some kind of help with what you're trying to do.
    Last edited by pkstormy; 06-10-07 at 15:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    In the Queries design Screen

    The data gets sorted from left to right as long as the sort is full out

    col 1 = 1 sort then col 2 2rd sort and so on

    What i do is If I have a order that the data has to be in

    I click the show (not tick)

    so that I get the data in the right (sort) order and right Display order
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Jun 2007
    Posts
    8
    Thank you for the quick reply pkstormy. I will try your suggestion.

  5. #5
    Join Date
    Jun 2007
    Posts
    8
    Thanks Myle, but like pkstormy pointed out, it's a little more involved than that.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Re-reading your requirements again..."I want to sort songs by genre. Then by bpm(beats per minute). But if two songs have the same name AND at least one common artist, then sort by song remix then bpm as long as "this" bpm is greater than the previous song.", I thought of a couple of queries you might need in your function to test how the "SortBy" is created:

    1. A query which shows which records have a matching name.
    2. A query which shows which records have a common artist.
    3. And probably a query which shows matching record ID's in 1 and 2 above.

    Your initial recordset might be sorted by genre and then bpm (or in an order for how you want to test it against the previous record!) As you loop through the initial recordset, you can then utilize a dlookup to the query(s) or open a couple more recordsets (with the sql string for the recordset the same as the queries and record ID equals the current record ID in the loop) to see if that specific record ID is in the recordset. Match it against the previous record variables in the loop and then have the logic to construct your "SortBy" value depending on the criteria (and query matches) and write the record with the "SortBy" to the "Temp" table. Obviously the first record in your loop would not have anything to match with a previous stored record variable so you would want to initially set the variable to 0 or null and test to see if the variable = 0 or null so as to skip the match with the previous record for the first record.

    Maybe I got this wrong but this was my best guess on what you're attempting.
    Last edited by pkstormy; 06-10-07 at 17:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Jan 2005
    Posts
    31
    Quote Originally Posted by djkube
    Here are my table definitions.
    Code:
    tbl_songs     tbl_genres          jtbl_song_artist     tbl_artists
    sid           gid                 sid                  aid
    bpm           genre               aid                  artist
    gid
    title
    remix
    Your table definitions miss the most important part, primary and foreign keys.

    I attempted to reconstruct the tables, but when I got to tbl_songs, there were duplicate value in the sid column, meaning that couldn't be the primary key.

    Adding gid to the primary key wouldn't have allowed for unique values. I added bpm instead, although I felt certain that was not a good choice.

    Then, I tried to create jtbl_song_artist. I could not create a foreign key on sid back to tbl_songs because sid in tbl_songs didn't have any unique index (from a primary key), and couldn't have one added because it possessed duplicate values.

    At this point, my attempt to re-create your tables failed.

    Can you please repost the above code box and indicate which columns are primary keys and which are foreign keys? (That, or indicate how the sample data in tbl_songs for column sid should actually appear.)

  8. #8
    Join Date
    Jun 2007
    Posts
    8
    Code:
    tbl_songs     tbl_genres          jtbl_song_artist     tbl_artists
    sid (pk)      gid (pk)            sid (fk)              aid (pk)
    bpm           genre               aid (fk)              artist
    gid (fk)
    title
    remix
    (pk) = primary key
    (fk) = foreign key

  9. #9
    Join Date
    Jan 2005
    Posts
    31
    Quote Originally Posted by djkube
    Code:
    tbl_songs     tbl_genres          jtbl_song_artist     tbl_artists
    sid (pk)      gid (pk)            sid (fk)              aid (pk)
    bpm           genre               aid (fk)              artist
    gid (fk)
    title
    remix
    (pk) = primary key
    (fk) = foreign key
    Those are the assumptions I made.

    Can you provide corrected sample data for tbl_songs? (Without duplicate values for column sid.)

  10. #10
    Join Date
    Jun 2007
    Posts
    8
    tbl_songs
    sid bpm gid title remix
    43 103 1 1 Step

    Sorry about that. I just added this entry to help clarify what I wanted. I just changed sid = 43

    By the way, thank you for your time. I've been working on this all day and I don't know of a way to do it all in SQL, so I'm just sorting the data externally.

  11. #11
    Join Date
    Jan 2005
    Posts
    31
    Quote Originally Posted by djkube
    tbl_songs
    sid bpm gid title remix
    43 103 1 1 Step

    Sorry about that. I just added this entry to help clarify what I wanted. I just changed sid = 43

    By the way, thank you for your time. I've been working on this all day and I don't know of a way to do it all in SQL, so I'm just sorting the data externally.
    I'm leaving for work in a few minutes. I'll be home this evening to attempt to create your sort order.

    I don't know that I can do it, but now that I can get the tables re-created, I'll be able to attempt it.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by djkube
    I want to sort songs by genre. Then by bpm(beats per minute). But if two songs have the same name AND at least one common artist, then sort by song remix then bpm as long as "this" bpm is greater than the previous song.
    Bit in bold is a nightmare. I guess a few iif statements including one or two corrolated subqueries to get this to work. Interested to see attempts at a solution....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And there was me thinking that you couldn't perform conditional sorting!
    I think the question is slowly becoming
    Why?
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can sort on expressions.
    Useful article to solidify in your mind. It applies to Iif in Access too.
    http://weblogs.sqlteam.com/jeffs/arc.../03/60195.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hehe I've actually read that before!
    I guess I just plain forgot.. *ahem*
    Interestingly his latest article may be helpful: http://weblogs.sqlteam.com/jeffs/arc.../05/60224.aspx
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •