??union or crosstab

I

Ian

I have a table names services with the fields ClientID, ContractName,
ContractNotes
I would like to be able to compare the notes from two different contracts
(year to year comparison). I'd like the user to be prompted to [Enter Name
Contract 1] and [Enter Name Contract 2] and have the results show:

Contract 1 Contract 2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
etc etc etc

Can someone tell me how to do this. I think it's a crosstab or union but I
don't I can't it to work. Thanks. Ian.
 
I

Ian

Forgot to add: I have this crosstab but it shows all contracts:

TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;
 
G

Gary Walter

Ian said:
I have a table names services with the fields ClientID, ContractName,
ContractNotes
I would like to be able to compare the notes from two different contracts
(year to year comparison). I'd like the user to be prompted to [Enter
Name
Contract 1] and [Enter Name Contract 2] and have the results show:

Contract 1 Contract 2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
etc etc etc

Can someone tell me how to do this. I think it's a crosstab or union but
I
don't I can't it to work. Thanks. Ian.
Click to expand...

Hi Ian,

I sure sounds like you just need 2 instances
of the table, joined on ClientID, with your
[Enter..] parameters in Criteria row under
each instance's ContractName.

try something like...

SELECT
t1.ClientID,
t1.ContractNotes As Contract1,
t2.ContractNotes As Contract2
FROM
Contracts As t1
INNER JOIN
Contracts As t2
ON
t1.ClientID = t2.ClientID
WHERE
t1.ContractName = [Enter name 1]
AND
t2.ContractName = [Enter name 2];

Please respond back if I misunderstood
or was not clear about something.

good luck,

gary
 
J

John Spencer

Perhaps the following.
Assumption: Contracts.Contract is a text field.

Parameters [Enter Contract 1] Text(255),
[Enter Contract 2] Text(255);
TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
WHERE Contracts.Contract = [Enter Contract 1] or
Contracts.Contract = [Enter Contract 2]
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Forgot to add: I have this crosstab but it shows all contracts:

TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

Ian said:
I have a table names services with the fields ClientID, ContractName,
ContractNotes
I would like to be able to compare the notes from two different contracts
(year to year comparison). I'd like the user to be prompted to [Enter Name
Contract 1] and [Enter Name Contract 2] and have the results show:

Contract 1 Contract 2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
etc etc etc

Can someone tell me how to do this. I think it's a crosstab or union but I
don't I can't it to work. Thanks. Ian.
Click to expand...
Click to expand...
 
I

Ian

Worked perfectly John but I altered the WHERE clause to
WHERE (((Contracts.Contract)=[Enter Contract 1] Or
(Contracts.Contract)=[Enter Contract 2]) AND (([Enter Contract 1]) Is Not
Null))
but it's still showing blank field in Contract 1. Do I need to specifiy a
blank field differently because it's a text field? Eg does it hold some
value in it for a blank?

Thanks Ian.

John Spencer said:
Perhaps the following.
Assumption: Contracts.Contract is a text field.

Parameters [Enter Contract 1] Text(255),
[Enter Contract 2] Text(255);
TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
WHERE Contracts.Contract = [Enter Contract 1] or
Contracts.Contract = [Enter Contract 2]
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Forgot to add: I have this crosstab but it shows all contracts:

TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

Ian said:
I have a table names services with the fields ClientID, ContractName,
ContractNotes
I would like to be able to compare the notes from two different contracts
(year to year comparison). I'd like the user to be prompted to [Enter Name
Contract 1] and [Enter Name Contract 2] and have the results show:

Contract 1 Contract 2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
etc etc etc

Can someone tell me how to do this. I think it's a crosstab or union but I
don't I can't it to work. Thanks. Ian.
Click to expand...
Click to expand...
Click to expand...
 
I

Ian

Hey John just noticed where you work (you've been answering my questions for
years now -- thank you). I'm in health care as well and working on wait
times issues -- if you get a chance check out my blog at
www.waittimes.blogspot.com I'd be interest in you're feedback. Thanks again
for all the help. Ian.


John Spencer said:
Perhaps the following.
Assumption: Contracts.Contract is a text field.

Parameters [Enter Contract 1] Text(255),
[Enter Contract 2] Text(255);
TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
WHERE Contracts.Contract = [Enter Contract 1] or
Contracts.Contract = [Enter Contract 2]
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Forgot to add: I have this crosstab but it shows all contracts:

TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

Ian said:
I have a table names services with the fields ClientID, ContractName,
ContractNotes
I would like to be able to compare the notes from two different contracts
(year to year comparison). I'd like the user to be prompted to [Enter Name
Contract 1] and [Enter Name Contract 2] and have the results show:

Contract 1 Contract 2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
etc etc etc

Can someone tell me how to do this. I think it's a crosstab or union but I
don't I can't it to work. Thanks. Ian.
Click to expand...
Click to expand...
Click to expand...
 
I

Ian

one last question -- the fileds can't be edited in the crosstab. Is that
normal?

John Spencer said:
Perhaps the following.
Assumption: Contracts.Contract is a text field.

Parameters [Enter Contract 1] Text(255),
[Enter Contract 2] Text(255);
TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
WHERE Contracts.Contract = [Enter Contract 1] or
Contracts.Contract = [Enter Contract 2]
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Forgot to add: I have this crosstab but it shows all contracts:

TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

Ian said:
I have a table names services with the fields ClientID, ContractName,
ContractNotes
I would like to be able to compare the notes from two different contracts
(year to year comparison). I'd like the user to be prompted to [Enter Name
Contract 1] and [Enter Name Contract 2] and have the results show:

Contract 1 Contract 2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
etc etc etc

Can someone tell me how to do this. I think it's a crosstab or union but I
don't I can't it to work. Thanks. Ian.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

If you want records where both Contract 1 and Contract 2 values are
present then you will need to change the query - to test for the
presence of the values


Parameters [Enter Contract 1] Text(255),
[Enter Contract 2] Text(255);
TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
WHERE (Contracts.Contract = [Enter Contract 1] or
Contracts.Contract = [Enter Contract 2])
AND Exists
(SELECT * FROM Contracts as Temp
WHERE Temp.ClientID = Contracts.ClientID
AND Temp.Contract = [Enter Contract 1])
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Worked perfectly John but I altered the WHERE clause to
WHERE (((Contracts.Contract)=[Enter Contract 1] Or
(Contracts.Contract)=[Enter Contract 2]) AND (([Enter Contract 1]) Is Not
Null))
but it's still showing blank field in Contract 1. Do I need to specifiy a
blank field differently because it's a text field? Eg does it hold some
value in it for a blank?

Thanks Ian.

John Spencer said:
Perhaps the following.
Assumption: Contracts.Contract is a text field.

Parameters [Enter Contract 1] Text(255),
[Enter Contract 2] Text(255);
TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
WHERE Contracts.Contract = [Enter Contract 1] or
Contracts.Contract = [Enter Contract 2]
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Click to expand...
Click to expand...
 
J

John Spencer

Crosstab queries are never updatable.

If you want an updatable query, you cannot use a crosstab query at all.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

one last question -- the fileds can't be edited in the crosstab. Is that
normal?

John Spencer said:
Perhaps the following.
Assumption: Contracts.Contract is a text field.

Parameters [Enter Contract 1] Text(255),
[Enter Contract 2] Text(255);
TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
WHERE Contracts.Contract = [Enter Contract 1] or
Contracts.Contract = [Enter Contract 2]
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Forgot to add: I have this crosstab but it shows all contracts:

TRANSFORM First(Contracts.ContractNotes) AS FirstOfContractNotes
SELECT Contracts.ClientID
FROM Contracts
GROUP BY Contracts.ClientID
PIVOT Contracts.Contract;

:

I have a table names services with the fields ClientID, ContractName,
ContractNotes
I would like to be able to compare the notes from two different contracts
(year to year comparison). I'd like the user to be prompted to [Enter Name
Contract 1] and [Enter Name Contract 2] and have the results show:

Contract 1 Contract 2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
ClientID Notes1 Notes2
etc etc etc

Can someone tell me how to do this. I think it's a crosstab or union but I
don't I can't it to work. Thanks. Ian.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

EXCEL07 how to use text in a PivotTable value field 1
Return cost per date range 1
Refer row or column of a range name 6
Crosstab AND not OR 2
Contract Details Tracking DB 0
union crosstab query 2
dcount in query 8
Database Design/Change Help Requested 2

Top