DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

sort data in ms access querie

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: Aug 2008

Posts: 304

Reputation: aktharshaik is an unknown quantity at this point

Solved Threads: 37

aktharshaik aktharshaik is offline

Offline

Posting Whiz

Re: sort data in ms access querie

#11

Dec 8th, 2008

You can even add sort clause if u like to the same

TO SORT BY DATE

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
SELECT sNAME, sDATE, sTK FROM (SELECT Table1.name AS sNAME, Table1.date1 AS sDATE, Table1.tk AS sTK from Table1 UNION ALL SELECT Table2.add AS sNAME, Table2.date2 AS sDATE, Table2.tk2 AS sTK FROM Table2) ORDER BY sDATE
SELECT sNAME, sDATE, sTK FROM (SELECT Table1.name AS sNAME, Table1.date1 AS sDATE, Table1.tk AS sTK from Table1 UNION ALL SELECT Table2.add AS sNAME, Table2.date2 AS sDATE, Table2.tk2 AS sTK FROM Table2) ORDER BY sDATE

OR

TO SORT BY NAME AND DATE

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
SELECT sNAME, sDATE, sTK FROM (SELECT Table1.name AS sNAME, Table1.date1 AS sDATE, Table1.tk AS sTK from Table1 UNION ALL SELECT Table2.add AS sNAME, Table2.date2 AS sDATE, Table2.tk2 AS sTK FROM Table2) ORDER BY sNAME, sDATE
SELECT sNAME, sDATE, sTK FROM (SELECT Table1.name AS sNAME, Table1.date1 AS sDATE, Table1.tk AS sTK from Table1 UNION ALL SELECT Table2.add AS sNAME, Table2.date2 AS sDATE, Table2.tk2 AS sTK FROM Table2) ORDER BY sNAME, sDATE

Regards
Shaik Akthar

•

Join Date: Jul 2008

Posts: 481

Reputation: abu taher is an unknown quantity at this point

Solved Threads: 24

abu taher

Offline

Posting Pro in Training

Re: sort data in ms access querie

#12

Dec 15th, 2008

sorry dear I faced a few problem
1. here you gather 6 field in the query by 3 field. but i want the query have both 6 field of both table
I write code like you but it show a msg like
"The number of colums in two selected tables or queries of a union
query do not match"
I dont understand what I need to do. please help me.

•

Join Date: Aug 2008

Posts: 304

Reputation: aktharshaik is an unknown quantity at this point

Solved Threads: 37

aktharshaik aktharshaik is offline

Offline

Posting Whiz

Re: sort data in ms access querie

#13

Dec 16th, 2008

when you write a union query u should see that both the queries have equal number of columns and that their datatypes are also same. If suppose one query has 6 and other query has 4 columns then query the remaining 2 columns with empty dummy data. for eg:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
SELECT sNAME, sDATE, sTK, sFATHER, sAGE, sPlace FROM (SELECT Table1.name AS sNAME, Table1.date1 AS sDATE, Table1.tk AS sTK, Table1.FatherName as sFATHER, Table1.Age as sAGE, Table1.Place as sPLACE from Table1 UNION ALL SELECT Table2.add AS sNAME, Table2.date2 AS sDATE, Table2.tk2 AS sTK, '' as sFATHER, 0 as sAGE, '' as sPLACE FROM Table2) ORDER BY sNAME, sDATE
SELECT sNAME, sDATE, sTK, sFATHER, sAGE, sPlace FROM (SELECT Table1.name AS sNAME, Table1.date1 AS sDATE, Table1.tk AS sTK, Table1.FatherName as sFATHER, Table1.Age as sAGE, Table1.Place as sPLACE from Table1 UNION ALL SELECT Table2.add AS sNAME, Table2.date2 AS sDATE, Table2.tk2 AS sTK, '' as sFATHER, 0 as sAGE, '' as sPLACE FROM Table2) ORDER BY sNAME, sDATE

Here i assume that the Table1 having fields FatherName, Age and Place fields which are not there in Table2. hence i filled them with empty dummy fields only b'cos to match the total number of columns of both queries joined by the union query. This however being the SELECT query and is only a view, no changes are made to your actual database tables.

Regards
Shaik Akthar

•

Join Date: Jul 2008

Posts: 481

Reputation: abu taher is an unknown quantity at this point

Solved Threads: 24

abu taher

Offline

Posting Pro in Training

Re: sort data in ms access querie

#14

Dec 18th, 2008

But when I show the query I can'nt understand what are actual

•

Join Date: Aug 2008

Posts: 304

Reputation: aktharshaik is an unknown quantity at this point

Solved Threads: 37

aktharshaik aktharshaik is offline

Offline

Posting Whiz

Re: sort data in ms access querie

#15

Dec 19th, 2008

Use some other notation for those dummy fields like in the query which i had suggested in the last post.

Table2.tk2 AS sTK, '***' as sFATHER, -999 as sAGE, '***' as sPLACE

Regards
Shaik Akthar

•

Join Date: Aug 2009

Posts: 2

Reputation: jyonto is an unknown quantity at this point

Solved Threads: 0

jyonto

Offline

Newbie Poster

For sql server what it should be.?

#16

Aug 26th, 2009

i try to execute your sql statment in sql query analyzer but it giving following error......

my query
SELECT project_code, ref, rdate, princ,
service [select TblRePayment.project_code,TblRePayment.ref,TblRePayment.rdate,TblRePayment.princ,TblRePayment.service FROM TblRePayment UNION ALL SELECT TblRePayment_re.project_code,TblRePayment_re.ref,TblRePayment_re.rdate,TblRePayment_re.princ,TblRePayment_re.service FROM TblRePayment_re]. AS [%$##@_Alias];

error

Server: Msg 103, Level 15, State 7, Line 2
The identifier that starts with 'select TblRePayment.project_code,TblRePayment.ref,TblRePayment.rdate,TblRePayment.princ,TblRePayment.service FROM TblRePayment ' is too long. Maximum length is 128.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.