comparing the two sets of data

[subs1678]

There are two different tables as shown below . The first table is for mode L. The second table is for mode T. Now i want to compare cost/lb for every wgt range- wgt range could be
1)<500
2)>500 and <1000
3)>1000


First Table -
Ost ocity dstate dcity Mode Wgt Cost/lb
KY hop IN del L 2000 0.90
BC kyr KY dd L 4444 0.87
KY CAs IN del L 45 0.865
DE LAs MI dds L 432 0.567


Ost ocity dstate dcity Mode Wgt Cost/lb
KY hop IN del T 2334 0.903
BC kyr KY dd T 1344 0.57
KY CAs IN del T 43335 0.665
DE LAs MI dds T 4322 0.867

OUTPUT should be in the following format( Where ever the Ost,Ocity, Dstate and dcity are common across two tables)

Ost Ocity Dstate Dcity Wgt-range cost/lb Mode Cost/lb Mode
BC kyr KY dd >1000 0.87 L 0.57 T

Can this be done by pivot table or cross tab query- ? If so please help on how to do this

 

[JerryKlmns]

A simple query would do the job

SELECT L.Ost, 
       L.ocity,
       L.dstate,
       L.dcity,
       myWeightRange(L.Wgt),
       L.Mode,
       L.[Cost/lb],
       T.Mode,
       T.Wgt,
       T.[Cost/lb]
FROM FirstTable As L INNER JOIN SecondTable As T On
       (L.Ost = T.Ost) AND
       (L.ocity = T.ocity) AND
       (L.dstate = T.dstate) AND
       (L.dcity = T.dcity);

Function myWeightRange(lWeight As Long) As String
Select Case lWeight
     Case < 500
          myWeightRange = "<  500"
     Case > 1000
          myWeightRange = "> 1000"
     Case Else
          myWeightRange = "500 - 1000"
End Select

you could replace the use of function myWeightRange by replacing it form sql statement with the following nested IIF
IIF(L.Wgt<500,"< 500",IIF(L.Wgt>1000,">1000","500-1000"))

I include the =500 and =1000 to the middle range, although you don't say that.