Help condensing query data

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

Re: Help condensing query data

"Daveo" wrote in message
news:870bf540-7d87-4fd7-9076-b3be2c2a44f9@1g2000hsl.googlegr oups.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.

Re: Help condensing query data

On Jan 14, 8:02 pm, "paii, Ron" wrote:
> "Daveo" wrote in message
>
> news:870bf540-7d87-4fd7-9076-b3be2c2a44f9@1g2000hsl.googlegr oups.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

Re: Help condensing query data

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" wrote in message
news:870bf540-7d87-4fd7-9076-b3be2c2a44f9@1g2000hsl.googlegr oups.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

Re: Help condensing query data

"Daveo" wrote in message
news:8af8a9b5-224e-441a-a304-6acc18118079@q39g2000hsf.google groups.com...
> On Jan 14, 8:02 pm, "paii, Ron" wrote:
> > "Daveo" wrote in message
> >
> > news:870bf540-7d87-4fd7-9076-b3be2c2a44f9@1g2000hsl.googlegr oups.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;

Re: Help condensing query data

On Jan 15, 1:55 pm, "paii, Ron" wrote:
> "Daveo" wrote in message
>
> news:8af8a9b5-224e-441a-a304-6acc18118079@q39g2000hsf.google groups.com...
>
>
>
> > On Jan 14, 8:02 pm, "paii, Ron" wrote:
> > > "Daveo" wrote in message
>
> > >news:870bf540-7d87-4fd7-9076-b3be2c2a44f9@1g2000hsl.googleg roups.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

Re: Help condensing query data

"Daveo" wrote in message
news:26e56237-cc77-4b66-a719-fd1b59198a16@c23g2000hsa.google groups.com...
> On Jan 15, 1:55 pm, "paii, Ron" wrote:
> > "Daveo" wrote in message
> >
> >
news:8af8a9b5-224e-441a-a304-6acc18118079@q39g2000hsf.google groups.com...
> >
> >
> >
> > > On Jan 14, 8:02 pm, "paii, Ron" wrote:
> > > > "Daveo" wrote in message
> >
> > >
>news:870bf540-7d87-4fd7-9076-b3be2c2a44f9@1g2000hsl.googleg roups.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.