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

Help condensing query data

P: n/a
Hi there,

I have a query (say it's called "Query1") that contains data in the
following structure:
id reference scoretype score
-- -------------- -------------- ---------
1 ABC quality 8
2 ABC relevance 7
3 DEF quality 9
4 DEF relevance 6
5 GHI quality 7
6 GHI reference 9

I would like to convert the data into the following format:

reference quality score relevance score
------------- ------------------- ----------------------
ABC 8 7
DEF 9 6
GHI 7 9

I started writing a module that would do it and write the results to
another table but couldn't figure out the logic.

Can anyone help please?

Many thanks - David
Jan 14 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"Daveo" <wr**********@gmail.comwrote in message
news:87**********************************@1g2000hs l.googlegroups.com...
Hi there,

I have a query (say it's called "Query1") that contains data in the
following structure:
id reference scoretype score
-- -------------- -------------- ---------
1 ABC quality 8
2 ABC relevance 7
3 DEF quality 9
4 DEF relevance 6
5 GHI quality 7
6 GHI reference 9

I would like to convert the data into the following format:

reference quality score relevance score
------------- ------------------- ----------------------
ABC 8 7
DEF 9 6
GHI 7 9

I started writing a module that would do it and write the results to
another table but couldn't figure out the logic.

Can anyone help please?

Many thanks - David
A cross tab query should produce the above output. Using the cross tab query
wizard will help.
Jan 14 '08 #2

P: n/a
On Jan 14, 8:02 pm, "paii, Ron" <n...@no.comwrote:
"Daveo" <writetoda...@gmail.comwrote in message

news:87**********************************@1g2000hs l.googlegroups.com...
Hi there,
I have a query (say it's called "Query1") that contains data in the
following structure:
id reference scoretype score
-- -------------- -------------- ---------
1 ABC quality 8
2 ABC relevance 7
3 DEF quality 9
4 DEF relevance 6
5 GHI quality 7
6 GHI reference 9
I would like to convert the data into the following format:
reference quality score relevance score
------------- ------------------- ----------------------
ABC 8 7
DEF 9 6
GHI 7 9
I started writing a module that would do it and write the results to
another table but couldn't figure out the logic.
Can anyone help please?
Many thanks - David

A cross tab query should produce the above output. Using the cross tab query
wizard will help.

Hi there,

Thanks for your reply.

I tried to put "reference" as a Row Heading, "scoretype" as a Column
Heading and "score" as a value. The thing is, the score values are
not numbers but rather text and numbers (I just put numbers in the
example above), so the crosstab doesn't work as it wants to calculate
something.

Can anyone assist please?

Many thanks,

David

Jan 15 '08 #3

P: n/a
You could do something like:

SELECT Query1.reference, Sum(IIf([scoretype]="quality",[score],0)) AS
Quality, Sum(IIf([scoretype]="relevance",[score],0)) AS Relevance
FROM Query1
GROUP BY Query1.reference;

Dominic

"Daveo" <wr**********@gmail.comwrote in message
news:87**********************************@1g2000hs l.googlegroups.com...
Hi there,

I have a query (say it's called "Query1") that contains data in the
following structure:
id reference scoretype score
-- -------------- -------------- ---------
1 ABC quality 8
2 ABC relevance 7
3 DEF quality 9
4 DEF relevance 6
5 GHI quality 7
6 GHI reference 9

I would like to convert the data into the following format:

reference quality score relevance score
------------- ------------------- ----------------------
ABC 8 7
DEF 9 6
GHI 7 9

I started writing a module that would do it and write the results to
another table but couldn't figure out the logic.

Can anyone help please?

Many thanks - David

Jan 15 '08 #4

P: n/a

"Daveo" <wr**********@gmail.comwrote in message
news:8a**********************************@q39g2000 hsf.googlegroups.com...
On Jan 14, 8:02 pm, "paii, Ron" <n...@no.comwrote:
"Daveo" <writetoda...@gmail.comwrote in message

news:87**********************************@1g2000hs l.googlegroups.com...
Hi there,
I have a query (say it's called "Query1") that contains data in the
following structure:
id reference scoretype score
-- -------------- -------------- ---------
1 ABC quality 8
2 ABC relevance 7
3 DEF quality 9
4 DEF relevance 6
5 GHI quality 7
6 GHI reference 9
I would like to convert the data into the following format:
reference quality score relevance score
------------- ------------------- ----------------------
ABC 8 7
DEF 9 6
GHI 7 9
I started writing a module that would do it and write the results to
another table but couldn't figure out the logic.
Can anyone help please?
Many thanks - David
A cross tab query should produce the above output. Using the cross tab
query
wizard will help.


Hi there,

Thanks for your reply.

I tried to put "reference" as a Row Heading, "scoretype" as a Column
Heading and "score" as a value. The thing is, the score values are
not numbers but rather text and numbers (I just put numbers in the
example above), so the crosstab doesn't work as it wants to calculate
something.

Can anyone assist please?

Many thanks,

David
I entered you data in a temp table and the wizard created the following
query except I delete the total column

TRANSFORM First(zTest.Score) AS [The Value]
SELECT zTest.Reference
FROM zTest
GROUP BY zTest.Reference
PIVOT zTest.ScoreType;

Jan 15 '08 #5

P: n/a
On Jan 15, 1:55 pm, "paii, Ron" <n...@no.comwrote:
"Daveo" <writetoda...@gmail.comwrote in message

news:8a**********************************@q39g2000 hsf.googlegroups.com...
On Jan 14, 8:02 pm, "paii, Ron" <n...@no.comwrote:
"Daveo" <writetoda...@gmail.comwrote in message
>news:87**********************************@1g2000h sl.googlegroups.com...
Hi there,
I have a query (say it's called "Query1") that contains data in the
following structure:
id reference scoretype score
-- -------------- -------------- ---------
1 ABC quality 8
2 ABC relevance 7
3 DEF quality 9
4 DEF relevance 6
5 GHI quality 7
6 GHI reference 9
I would like to convert the data into the following format:
reference quality score relevance score
------------- ------------------- ----------------------
ABC 8 7
DEF 9 6
GHI 7 9
I started writing a module that would do it and write the results to
another table but couldn't figure out the logic.
Can anyone help please?
Many thanks - David
A cross tab query should produce the above output. Using the cross tab
query
wizard will help.
Hi there,
Thanks for your reply.
I tried to put "reference" as a Row Heading, "scoretype" as a Column
Heading and "score" as a value. The thing is, the score values are
not numbers but rather text and numbers (I just put numbers in the
example above), so the crosstab doesn't work as it wants to calculate
something.
Can anyone assist please?
Many thanks,
David

I entered you data in a temp table and the wizard created the following
query except I delete the total column

TRANSFORM First(zTest.Score) AS [The Value]
SELECT zTest.Reference
FROM zTest
GROUP BY zTest.Reference
PIVOT zTest.ScoreType;
Thankyou all - it was the "First" part that finally made it work. It
also works when you choose "Last" - why is that?

David
Jan 15 '08 #6

P: n/a

"Daveo" <wr**********@gmail.comwrote in message
news:26**********************************@c23g2000 hsa.googlegroups.com...
On Jan 15, 1:55 pm, "paii, Ron" <n...@no.comwrote:
"Daveo" <writetoda...@gmail.comwrote in message
news:8a**********************************@q39g2000 hsf.googlegroups.com...


On Jan 14, 8:02 pm, "paii, Ron" <n...@no.comwrote:
"Daveo" <writetoda...@gmail.comwrote in message
>
news:87**********************************@1g2000h sl.googlegroups.com...
Hi there,
I have a query (say it's called "Query1") that contains data in
the
following structure:
id reference scoretype score
-- -------------- -------------- ---------
1 ABC quality 8
2 ABC relevance 7
3 DEF quality 9
4 DEF relevance 6
5 GHI quality 7
6 GHI reference 9
I would like to convert the data into the following format:
reference quality score relevance score
------------- ------------------- ----------------------
ABC 8 7
DEF 9 6
GHI 7 9
I started writing a module that would do it and write the results
to
another table but couldn't figure out the logic.
Can anyone help please?
Many thanks - David
A cross tab query should produce the above output. Using the cross
tab
query
wizard will help.
Hi there,
Thanks for your reply.
I tried to put "reference" as a Row Heading, "scoretype" as a Column
Heading and "score" as a value. The thing is, the score values are
not numbers but rather text and numbers (I just put numbers in the
example above), so the crosstab doesn't work as it wants to calculate
something.
Can anyone assist please?
Many thanks,
David
I entered you data in a temp table and the wizard created the following
query except I delete the total column

TRANSFORM First(zTest.Score) AS [The Value]
SELECT zTest.Reference
FROM zTest
GROUP BY zTest.Reference
PIVOT zTest.ScoreType;

Thankyou all - it was the "First" part that finally made it work. It
also works when you choose "Last" - why is that?

David
Assuming your sample data looks like your live data, the query would return
only one row for each combination of reference and scoretype. So First,
Last, Min, Max would have the same 1 value to return.
Jan 15 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.