Moving data from one table to another

[brownti via AccessMonster.com]

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.

 

[Jon Ley]

To get the results listing you're after you need a crosstab query based on
your current query (or you may be able to just modify your current query).
You can either use the crosstab query wizard and follow the prompts, or just
create a new query and then set the query type to crosstab. Your fields will
be:

JobID (Total = 'Group By', Crosstab = 'Row Heading')
Length (Total = 'Group By', Crosstab = 'Column Heading')
Qty (Total = 'Sum', Crosstab = 'Value')

The only thing this won't give you is the 'zero' value in the last row of
your example data; instead you'll get a null. The next question is whether
you really need to copy this data to another table, or can you just use the
crosstab query as is?

Hope this helps start you in the right direction.

Jon.

 

[John Spencer]

That looks like you would need to use a crosstab query. There is a crosstab
query wizard that should help you.

Select JOBID as the row heading
Select Length as the Column heading
Select Qty as the field for the calculation and Sum as the Function.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..