Sorting not working

[syed ripon via AccessMonster.com]

Hi There:
I have 2 Select queries.
q1:Select pn,yr,mn,v1 from q1 where yr=2004
group by pn,yr,mn Order by pn,yr,mn
q2:Select pn,yr,mn,v2 from q1 where yr=2005
group by pn,yr,mn Order by pn,yr,mn
-----
Pn Yr Mn V1 Pn Yr Mn V1
2 2004 1 10 2 2005 1 11
2 2004 2 15 2 2005 2 16
3 2004 1 10 3 2005 1 14
4 2004 1 15 4 2005 1 15
4 2004 2 20 4 2005 2 28

How can I get a query so that both data can join together after sorted on
'mn' like:
Pn Yr Mn V1 Pn Yr mn V2
2 2004 1 10 2 2005 1 11
3 2004 1 10 3 2005 1 14
4 2004 1 15 4 2005 1 15
2 2004 2 15 2 2005 2 16
4 2004 2 20 4 2005 2 28


I tried select q1.pn,q1.yr,q1.mn,q1.v1,q2.pn,q2.yr,q2.mn,q2.v2
from q1 inner join q2 on q1.pn = q2.pn order by q1.pn, q1.mn

Result: data are Not being sorted on 'mn'

Pls help and thanks in advance

 

[Tom Ellison]

Dear Syed:

It looks to me like the rows are not joined on Mn = mn only, but also by
ascending rank of the values in V1 and V2 plus Pn and Pn.

Notice that you have two rows where Mn / mn are joined on the value 1, with
the row on the left Pn = 2 joined to Pn = 2 on the right. Does this pretty
much describe the rule you want to use?

If so, then this should do it:

select q1.pn, q1.yr, q1.mn, q1.v1,
q2.pn, q2.yr, q2.mn, q2.v2
from q1
inner join q2 on q2.pn = q1.pn
AND q2.Mn = q1.Mn
AND q2.Yr = q1.Yr + 1
order by q1.pn, q1.mn

Does that do it?

Tom Ellison