HELP with sql (Access) and asp

[torbeing]

I am trying to compare answers from 3 different firms in a table.
All the answers is collected in on table with the firmID, answer
(Yes/no) and a comment to the answer.
I wonder if its possible to extract the answer and rename them?
Here is a SQL (that dont work..) but hopefully explain what I am
looking for

SELECT firm1Answar,firm2Answar,firm3Answar
FROM (SELECT cmsAnswar as firm1Answar FROM CMS WHERE
CMS.firmID=MMfirm1),(SELECT cmsAnswar as firm2Answar FROM CMS WHERE
CMS.firmID=MMfirm2 ),(SELECT cmsAswar as firm3Answar FROM CMS WHERE
CMS.firmID=MMfirm3)

MMfirmID is the ID I have given the firm that is returned from the page
where i select which firms to compare
(asp?firm1ID=1&firm2ID=2&firm3ID=3).

 

[Guest]

well you cant have 3 select statements in one table, im not sure i
understand, are your values for MMfirm1 coming from a single control or from
3 separate controls?

Im just hazzarding a guess but if the table is set up as youve shown then
you can hold the different firmID's in one control and let the user pick
which firm they wish to filter and bring up that firms results (which you can
then manipulate) however you seem to want to bring up all 3 firms aswers at
the same time, as you said, to compare them. You'd maybe have to split your
table into 3 different tables and join them to get youre results like that.

 

[Bob Barrows [MVP]]

I am trying to compare answers from 3 different firms in a table.
All the answers is collected in on table with the firmID, answer
(Yes/no) and a comment to the answer.
I wonder if its possible to extract the answer and rename them?
Here is a SQL (that dont work..) but hopefully explain what I am
looking for

SELECT firm1Answar,firm2Answar,firm3Answar
FROM (SELECT cmsAnswar as firm1Answar FROM CMS WHERE
CMS.firmID=MMfirm1),(SELECT cmsAnswar as firm2Answar FROM CMS WHERE
CMS.firmID=MMfirm2 ),(SELECT cmsAswar as firm3Answar FROM CMS WHERE
CMS.firmID=MMfirm3)

MMfirmID is the ID I have given the firm that is returned from the
page where i select which firms to compare
(asp?firm1ID=1&firm2ID=2&firm3ID=3).

I would need to know more about your table structure (perhaps if you showed
us some sample data and the results you wish to get from that data in
tabular form, we would have a better idea) but it appears that you want to
do a crosstab. Have you tried using the Access Crosstab Query wizard?

Bob Barrows

 

[torbeing]

I have tried Access Crosstad Query, but it is no use when using the
code in Dreamweaver (where I am making my page).

Here are the table structure:

TEKSPEK
tekspekID (prim)
TS_ID
tekspekName
tekspekDescription
tekspekPriority

FIRM
firmID (prim)
firmName

CMS
cmsID (prim.key)
firmID (foreign key)
tekspekID(foreign key
cmsAnswer
cmsComment

The result I want to get:
Firm Firm1 Firm2 Firm3
TS_ID
1.1 Yes Yes Yes
1.2 No Yes No
1.3 Yes No Yes

And so on...

 

[Bob Barrows [MVP]]

I have tried Access Crosstad Query,

So you created a query that generates the output you want? Save that query!
Call it "FirmCrosstab".

but it is no use when using the
code in Dreamweaver (where I am making my page).

Of course it is!
Look, if DW doesn't provide the ability to execute saved Access queries
(shame on them!), you are going to have to write your own code to execute
"FirmCrosstab" It will look like this:

dim cn, rs
set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=p:\ath\to\database.mdb"
set rs=createobject("adodb.recordset")
cn.FirmCrosstab rs
if not rs.eof ...


You will likely get more help with this in
microsoft.public.inetserver.asp.db

The result I want to get:
Firm Firm1 Firm2 Firm3
TS_ID
1.1 Yes Yes Yes
1.2 No Yes No
1.3 Yes No Yes

And so on...

I don't think i will need to be involved after this, but if you did need
further help, I would not be able to provide it without seeing some sample
data from your 3 tables (in tabular format) ...