Cross Tab Queru question...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have 2 Tables, CONTRACTS & CONTRACTS_OTHER_PARTIES, where they are linked
via ContractNumber. For every Contract there can me many
Contract_OtherParties.

I then Created a Cross Tab Query with
ContractNumber as the Row Heading
PartyNumber as the Column Heading
Part as the Value.

This is the SQL code:

TRANSFORM First(tblContracts_OtherParties.Party) AS FirstOfParty
SELECT tblContracts.ContractNumber
FROM tblContracts LEFT JOIN tblContracts_OtherParties ON
tblContracts.ContractNumber = tblContracts_OtherParties.ContractNumber
GROUP BY tblContracts.ContractNumber
PIVOT tblContracts_OtherParties.PartyNumber;

It produces the data as follows:

ContractNumber <> 1 2 3
--------------------------------------
AAA-01-2
ENG-01-1 zz xx bb
ENG-05-1 cc ff ee
ENG-05-2 aa gg aa

Note it is dummy data.

What I would like to do & cant figure out is how to Sort on the 1 column not
the Contract Number column.

Any help would be greatly appreciated.

Thank you,
Jeff
 
I think you could create another Row Heading based on the expression:

Max(
IIf(tblContracts_OtherParties.PartyNumber=1,tblContracts_OtherParties.Party,
Null) )

Sort on this column.
 
Since you have posted this (or a similar) question in several different
threads, I'm not sure if you've seen my answer to your message titled
"RE post - Need help writing a Query", but if you look at that you'll
see a possible solution not involving a Crosstab Query. Crosstab may do
exactly what you want, but the Select Query that I suggested may give
you some additional ideas, and I hope it hasn't gotten lost. For
example, it's pretty easy to sort the results of that Query by any
column(s) you choose.

Since the sample data you quote here look way different from those in
the other message, perhaps this is a different problem, but it's not
obvious to me exactly what you're looking for.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top