I have a query that produces results like those below:

JobID | Length | Qty
1 8 2
1 7 2
1 7 2
1 6 2
1 8 2
2 7 2
2 6 2

I would like the results listed in a new table like such:

JobID | 6 | 7 | 8
1 2 4 4
2 2 2 0

How can this be accomplished? I am very new to Access. Thanks for any help.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200712/1

Make a crosstab query. The query wizard should be fine. Use JobID as GROUP,
aggregate with SUM over qty, and use Length as PIVOT.

Iin case the query wizard would not 'cooperate' as you like it,

the SQL view of your crosstab query should look like:



TRANSFORM SUM(qty)
SELECT jobID
FROM yourTableNameHere
GROUP BY jobID
PIVOT length


You can paste that code in the SQL view of a new query, change the table
name for yours, and switch back in design view to see how you could have
done it, graphically.



Hoping it may help,
Vanderghast, Access MVP



"brownti via AccessMonster.com" <u31540@uwe> wrote in message
news:7ca1843ccc14f@uwe...
>I have a query that produces results like those below:
>
> JobID | Length | Qty
> 1 8 2
> 1 7 2
> 1 7 2
> 1 6 2
> 1 8 2
> 2 7 2
> 2 6 2
>
> I would like the results listed in a new table like such:
>
> JobID | 6 | 7 | 8
> 1 2 4 4
> 2 2 2 0
>
> How can this be accomplished? I am very new to Access. Thanks for any
> help.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...eries/200712/1
>