Crosstab AND not OR

[Ian]

I have a crosstab query and want to show clients that have BOTH contract 1
and contract 2. Currently it shows 1 or 2. If I change the WHERE clause to
AND I get nothing. Any ideas?

PARAMETERS [Enter Contract 1] Text ( 255 ), [Enter Contract 2] Text ( 255 );
TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID, Main.[Last Name]
FROM Contracts INNER JOIN Main ON Contracts.ClientID = Main.ClientID
WHERE (((Contracts.Contract)=[Enter Contract 1] Or
(Contracts.Contract)=[Enter Contract 2]))
GROUP BY Contracts.ClientID, Main.[Last Name]
PIVOT Contracts.Contract;

 

[KARL DEWEY]

Your Contracts.Contract field cannot contain both therefore if you change the
WHERE clause to AND you get nothing.
Post an example of data and what you expect the crosstab results to look like.

 

[Ian]

The table is called Contracts and each row has ClientID, Contract,
ContractNote.
I want the query to select ClientID's that have the 2 specific contracts (to
make sure the notes match) and show each clientID in a row with the notes
from contract1 in the second column and the notes from contract2 in the third
column. The select portion of the query though, needs to select out
clientID's that have the two contracts. I think I need part of the query
that creates the select portion of the clause then a second that puts it into
a crosstab.

ClientID Contract1Note Contract2Note
245 cut to 2" cut to 2"
247
where each clientID has contract1 and contract2

The simple select query is:
SELECT Contracts.ClientID, Contracts.ContractNotes, Contracts.Contract
FROM Contracts
GROUP BY Contracts.ClientID, Contracts.ContractNotes, Contracts.Contract
HAVING (((Contracts.Contract)=[Enter contract1] Or
(Contracts.Contract)=[Enter Contract 2]));

but this doesn't put the contract notes in 2 columns as I showed above.

Clear as mud? thx.

Your Contracts.Contract field cannot contain both therefore if you change the
WHERE clause to AND you get nothing.
Post an example of data and what you expect the crosstab results to look like.
--
KARL DEWEY
Build a little - Test a little

I have a crosstab query and want to show clients that have BOTH contract 1
and contract 2. Currently it shows 1 or 2. If I change the WHERE clause to
AND I get nothing. Any ideas?

PARAMETERS [Enter Contract 1] Text ( 255 ), [Enter Contract 2] Text ( 255 );
TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID, Main.[Last Name]
FROM Contracts INNER JOIN Main ON Contracts.ClientID = Main.ClientID
WHERE (((Contracts.Contract)=[Enter Contract 1] Or
(Contracts.Contract)=[Enter Contract 2]))
GROUP BY Contracts.ClientID, Main.[Last Name]
PIVOT Contracts.Contract;