...

View Full Version : Date order with a union all



lozhowlett
02-13-2007, 01:20 PM
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

degsy
02-13-2007, 03:25 PM
Try ordering by the column number

e.g.
ORDER BY 1

lozhowlett
02-13-2007, 03:32 PM
sorry that didnt work - thanks for trying!

degsy
02-13-2007, 04:03 PM
You only need the ORDER BY once. Put it at the end of the query.

lozhowlett
02-13-2007, 05:23 PM
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

mehere
02-13-2007, 09:18 PM
try this:

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"



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum