sort data in ms access querie

[abu taher]

I made a querie with 2 table. both of 2 table have date fields. the querie also have this both of date fields with other fields. so all data show in a each line. like
name | address | date this 3 fields in table1
Roll | Class | date this 3 fields in table2
name | date | roll | date this 4 fields in querie1
now this data show in querie1 like
Taher | 17/11/2008 | 100 | 16/11/2008
but I want it show in two row with sort by date like:
Taher | 17/11/2008
100 | 16/11/2008
how can I do this

[abu taher]

help me anybody please

[QVeen72]

Hi,

use :

Select * From MyTable Order By MyDate

Regards
Veena

[debasisdas]

you can also based on multiple fields one after another

.......order by field1,field2,field3.

[abu taher]

I delete all fields and make it new. now every thing is ok. but One problem. every data show double double. I mean same data show in 2 row.

[abu taher]

anybody help me please

[aktharshaik]

Plz Post your query here. will help u out.

[abu taher]

please

[abu taher]

I attach a sample file. please check it. here same data (faruk)show in two row. another problem was it not show with sort by date.

[aktharshaik]

MODIFY THE QUERY AS GIVEN BELOW

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)

I have attached back ur file with the same change. plz have a look at it.

[aktharshaik]

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

TO SORT BY DATE

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

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

[abu taher]

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.

[aktharshaik]

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:

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.

[abu taher]

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

[aktharshaik]

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

[jyonto]

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 '.'.

[jyonto]

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 '.'.

plz. help me out

thanks

[aktharshaik]

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

try the above query.

[vb5prgrmr]

ak, do you realize that this thread is almost a year old???