Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Feb 2007
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date order with a union all

    Hi Guys and Gals,

    I have a problem with my date sorting. I have 3 tables all with a comman field "reviewdate" which is within MS Access as a date/time type. I need to look at all three tables and join the results, and then display them within dat order. So far my sql code selects the records fine, but does not sort the all the records put together into date order!

    Code ---------------------------

    varSQL = "SELECT tbl_client_gen.reviewdate, tbl_client_gen.reviewreason, tbl_client1.* FROM tbl_client_gen, tbl_client1 WHERE cint(tbl_client_gen.clientid_1)=tbl_client1.fld_clientid ORDER BY tbl_client_gen.reviewdate ASC"
    varSQL = varSQL & " UNION ALL"
    varSQL = varSQL & " SELECT tbl_life.reviewdate, tbl_life.reviewreason, tbl_client1.* FROM tbl_life, tbl_client1 WHERE cint(tbl_life.clientid1)=tbl_client1.fld_clientid ORDER BY reviewdate ASC"
    varSQL = varSQL & " UNION ALL"
    varSQL = varSQL & " SELECT tbl_mortgage.reviewdate, tbl_mortgage.reviewreason, tbl_client1.* FROM tbl_mortgage, tbl_client1 WHERE cint(tbl_mortgage.clientid1)=tbl_client1.fld_clientid ORDER BY reviewdate ASC"
    varSQL = varSQL & " UNION ALL"
    varSQL = varSQL & " SELECT tbl_general_insure.reviewdate, tbl_general_insure.reviewreason, tbl_client1.* FROM tbl_general_insure, tbl_client1 WHERE cint(tbl_general_insure.clientid1)=tbl_client1.fld_clientid ORDER BY reviewdate ASC"

    END CODE ------------------------------


    Does anyone have and ideas as to where I might be going wrong?

    thanks

    loz

  • #2
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try ordering by the column number

    e.g.
    ORDER BY 1

  • #3
    New Coder
    Join Date
    Feb 2007
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    sorry that didnt work - thanks for trying!

  • #4
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You only need the ORDER BY once. Put it at the end of the query.

  • #5
    New Coder
    Join Date
    Feb 2007
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    new code

    -----------------------
    varSQL = "SELECT * FROM ("
    varSQL = varSQL & "SELECT tbl_client_gen.reviewdate, tbl_client_gen.reviewreason, tbl_client1.* FROM tbl_client_gen, tbl_client1 WHERE cint(tbl_client_gen.clientid_1)=tbl_client1.fld_clientid"
    varSQL = varSQL & " UNION ALL"
    varSQL = varSQL & " SELECT tbl_life.reviewdate, tbl_life.reviewreason, tbl_client1.* FROM tbl_life, tbl_client1 WHERE cint(tbl_life.clientid1)=tbl_client1.fld_clientid"
    varSQL = varSQL & " UNION ALL"
    varSQL = varSQL & " SELECT tbl_mortgage.reviewdate, tbl_mortgage.reviewreason, tbl_client1.* FROM tbl_mortgage, tbl_client1 WHERE cint(tbl_mortgage.clientid1)=tbl_client1.fld_clientid"
    varSQL = varSQL & " UNION ALL"
    varSQL = varSQL & " SELECT tbl_general_insure.reviewdate, tbl_general_insure.reviewreason, tbl_client1.* FROM tbl_general_insure, tbl_client1 WHERE cint(tbl_general_insure.clientid1)=tbl_client1.fld_clientid"
    varSQL = varSQL & ") ORDER BY reviewdate"
    -------------------------------

    still doesnt work??

    date format is dd/mm/yyyy
    ms access
    VB ASP
    and field is Date/Time

  • #6
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    try this:
    Code:
    varSQL = varSQL & "SELECT tbl_client_gen.reviewdate, tbl_client_gen.reviewreason, tbl_client1.* FROM tbl_client_gen, tbl_client1 WHERE cint(tbl_client_gen.clientid_1)=tbl_client1.fld_clientid"
    varSQL = varSQL & " UNION ALL"
    varSQL = varSQL & " SELECT tbl_life.reviewdate, tbl_life.reviewreason, tbl_client1.* FROM tbl_life, tbl_client1 WHERE cint(tbl_life.clientid1)=tbl_client1.fld_clientid"
    varSQL = varSQL & " UNION ALL"
    varSQL = varSQL & " SELECT tbl_mortgage.reviewdate, tbl_mortgage.reviewreason, tbl_client1.* FROM tbl_mortgage, tbl_client1 WHERE cint(tbl_mortgage.clientid1)=tbl_client1.fld_clientid"
    varSQL = varSQL & " UNION ALL"
    varSQL = varSQL & " SELECT tbl_general_insure.reviewdate, tbl_general_insure.reviewreason, tbl_client1.* FROM tbl_general_insure, tbl_client1 WHERE cint(tbl_general_insure.clientid1)=tbl_client1.fld_clientid"
    varSQL = varSQL & " ORDER BY reviewdate"


  •  

    Posting Permissions

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