Combine Data from the same Table

[brownti via AccessMonster.com]

We have Data in a table that we want to combine as follows:

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


We would like to combine it to look like this:

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

 

[Klatuu]

You need to state the business rules for what you are trying to do. Your
graphic display doesn't give anyone enough information to provide an answer.

 

[brownti via AccessMonster.com]

sorry. ill try to be more clear...

We're trying to add the quantities for each length by JobID and then put them
in a new table layed out like the second table with each record being a total
per JobID.

You need to state the business rules for what you are trying to do. Your
graphic display doesn't give anyone enough information to provide an answer.

We have Data in a table that we want to combine as follows:

[quoted text clipped - 10 lines]

1 2 2 6 2
2 4 0 2 2

 

[Klatuu]

Sorry, but it still doesn't make sense to me. If you sum the quanty for Job
1, the total is 6, not 2 as shown it the second table. Also, there is no way
to tell which field in table 1 related to a field in table 2.
--
Dave Hargis, Microsoft Access MVP

sorry. ill try to be more clear...

We're trying to add the quantities for each length by JobID and then put them
in a new table layed out like the second table with each record being a total
per JobID.

You need to state the business rules for what you are trying to do. Your
graphic display doesn't give anyone enough information to provide an answer.

We have Data in a table that we want to combine as follows:

[quoted text clipped - 10 lines]

1 2 2 6 2
2 4 0 2 2

 

[Michel Walsh]

You have to normalize the data and then, un-normalized it again.

To normalize the data, make a new query, and in the SQL view, type:


SELECT JobID, Length1 AS theLength, Qty1 As qty FROM yourTableName
UNION ALL
SELECT JobID, Length2, Qty2 FROM yourTableName


(Change the table name for yours). Save it under the name of, let say, qu.
Then, use a crosstab query:


TRANSFORM SUM(qty)
SELECT jobID
FROM qu
GROUP BY jobID
PIVOT theLength



Hoping it may help,
Vanderghast, Access MVP

 

[brownti via AccessMonster.com]

Thanks so much! It worked like a charm.

You have to normalize the data and then, un-normalized it again.

To normalize the data, make a new query, and in the SQL view, type:

SELECT JobID, Length1 AS theLength, Qty1 As qty FROM yourTableName
UNION ALL
SELECT JobID, Length2, Qty2 FROM yourTableName

(Change the table name for yours). Save it under the name of, let say, qu.
Then, use a crosstab query:

TRANSFORM SUM(qty)
SELECT jobID
FROM qu
GROUP BY jobID
PIVOT theLength

Hoping it may help,
Vanderghast, Access MVP

We have Data in a table that we want to combine as follows:

[quoted text clipped - 10 lines]

1 2 2 6 2
2 4 0 2 2