By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,544 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Query type for asp page?

P: n/a
I have a table that stores information about client jobs. Two fields called
PinkSatisfaction and WhiteSatisfaction in the jobs table, store the values
for client satisfaction. The fields values are ...

1 (for not satisfied)
2 (for satisfied)
3 (for very satisfied)

I need to have a table on our Intranet to diplay running totals like this.

Not satisfied Satisfied Very Satisfied
Pink 23 12 122
White 12 45 54

What is the best way to get this data from an access database to an asp
page? I have used individual recordsets to get the data but this means a
seperate recordset for each value in the table.

Should I be trying a pivot type recordset?

Thanks all.

--
MrBitsy
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
MrBitsy wrote:
I have a table that stores information about client jobs. Two fields called
PinkSatisfaction and WhiteSatisfaction in the jobs table, store the values
for client satisfaction. The fields values are ...

1 (for not satisfied)
2 (for satisfied)
3 (for very satisfied)

I need to have a table on our Intranet to diplay running totals like this.

Not satisfied Satisfied Very Satisfied
Pink 23 12 122
White 12 45 54

What is the best way to get this data from an access database to an asp
page? I have used individual recordsets to get the data but this means a
seperate recordset for each value in the table.

Should I be trying a pivot type recordset?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your table should be designed differently. You shouldn't have a
separate column for each satisfaction type; it should be like this:

CREATE TABLE table_name (
satisfaction_type VARCHAR(8) NOT NULL
FOREIGN KEY REFERENCES SatisfactionTypes,
satisfaction_value INTEGER NOT NULL
CHECK (satisfaction_value IN (1,2,3)),
.... other columns ...
)

The advantage of this table is in the future you could add more
satisfaction_types by just adding the new type to the table
SatisfactionTypes.

Example data:

satisfaction_type satisfaction_value
================= ==================
Pink 1
White 2
Pink 3
Pink 2

Then you'd make a pivot table (aka crosstab) query like this:

TRANSFORM Count(*) As TheCount;
SELECT satisfaction_type
FROM table_name
GROUP BY satisfaction_type
PIVOT Choose(satisfaction_value=1, "Not Satisfied",
satisfaction_value=2, "Satisfied",
satisfaction_value=3, "Very Satisfied")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQuaKEoechKqOuFEgEQLuqQCguWZnOVSZbHB1lb7O8uI/ozPgiqgAoNNN
Rl1JYNn+KHme/JQbaZOlnKRF
=9X0n
-----END PGP SIGNATURE-----
Nov 13 '05 #2

P: n/a
MGFoster wrote:
MrBitsy wrote:
I have a table that stores information about client jobs. Two fields
called PinkSatisfaction and WhiteSatisfaction in the jobs table, store
the values for client satisfaction. The fields values are ...

1 (for not satisfied)
2 (for satisfied)
3 (for very satisfied)

I need to have a table on our Intranet to diplay running totals like
this.

Not satisfied Satisfied Very Satisfied
Pink 23 12 122
White 12 45 54

What is the best way to get this data from an access database to an
asp page? I have used individual recordsets to get the data but this
means a seperate recordset for each value in the table.

Should I be trying a pivot type recordset?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your table should be designed differently. You shouldn't have a
separate column for each satisfaction type; it should be like this:

CREATE TABLE table_name (
satisfaction_type VARCHAR(8) NOT NULL
FOREIGN KEY REFERENCES SatisfactionTypes,
satisfaction_value INTEGER NOT NULL
CHECK (satisfaction_value IN (1,2,3)),
... other columns ...
)

The advantage of this table is in the future you could add more
satisfaction_types by just adding the new type to the table
SatisfactionTypes.

Example data:

satisfaction_type satisfaction_value
================= ==================
Pink 1
White 2
Pink 3
Pink 2

Then you'd make a pivot table (aka crosstab) query like this:

TRANSFORM Count(*) As TheCount;
SELECT satisfaction_type
FROM table_name
GROUP BY satisfaction_type
PIVOT Choose(satisfaction_value=1, "Not Satisfied",
satisfaction_value=2, "Satisfied",
satisfaction_value=3, "Very Satisfied")


Correction: Change the PIVOT line to

PIVOT Choose(satisfaction_value, "Not Satisfied",
"Satisfied",
"Very Satisfied")
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Nov 13 '05 #3

P: n/a
MGFoster <me@privacy.com> wrote in news:9SvFe.8616$dU3.3841
@newsread2.news.pas.earthlink.net:
Should I be trying a pivot type recordset?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your table should be designed differently. You shouldn't have a
separate column for each satisfaction type; it should be like this:

CREATE TABLE table_name (
satisfaction_type VARCHAR(8) NOT NULL
FOREIGN KEY REFERENCES SatisfactionTypes,
satisfaction_value INTEGER NOT NULL
CHECK (satisfaction_value IN (1,2,3)),
... other columns ...
)


Thanks very much for the suggesting and that makes complete sense. I'll
create the table and transfer the current data into it.

--
MrBitsy
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.