I have a crosstab query which produces exactly what I want:
TRANSFORM Sum(tRundown.Quantity) AS SumOfQuantity SELECT tJob.Job, tStripe.Stripe, tChildPart.ChildPart
FROM tOperation INNER JOIN (((tChildPart INNER JOIN tJob ON tChildPart.JobID = tJob.JobID) INNER JOIN (tStripe INNER JOIN tRundown ON tStripe.StripeID = tRundown.StripeID) ON (tJob.JobID = tRundown.JobID) AND (tChildPart.ChildID = tRundown.ChildID)) INNER JOIN tPartParent ON (tPartParent.ParentID = tRundown.ParentID) AND (tPartParent.ParentID = tChildPart.ParentID) AND (tJob.JobID = tPartParent.JobID)) ON (tStripe.StripeID = tOperation.StripeID) AND (tOperation.OperationID = tRundown.OperationID)
GROUP BY tJob.Job, tStripe.Stripe, tChildPart.ChildPart, tChildPart.ChildLevel
PIVOT tOperation.Operation;
I want the values of Operation and Quantity, but I want it record-by-record, not consolidated. In other words, in a report, the results of Operation and Quantity want to look like this:
WXYZ
1 2 1 1
J K
1 1
A B C
1 1 1
NOT like this:
WXYZJKABC
1 2 1 1 11111
NOR like this:
WXYZJKABC
1 2 11
______11
_________111
When I put a parameter prompt in the crosstab query above for ChildPart, and key the ChildPart, I get one record that looks exactly like what I want. Then run again, type the next ChildPart, and so on. But there are up to 99 jobs, with 9 or fewer ChildParts each. Too much to expect someone to key each run. The above crosstab query somehow should be able to iterate over the ChildPart field in another SELECT query, using first ChildPart as the parameter to match. This would produce the a perfect one-record crosstab result. Somehow store that. Then run the crosstab query again for NEXT value of ChildPart. Store that right below the last. Repeat until EOF of the SELECT query. There will never be more than 9 values of ChildPart, nor more than 99 jobs so the entire run would be <99 jobs each with <9 ChildParts, so it doesn't seem huge.
I've done this:
http://support.microsoft.com/kb/328320
...but can't figure out how to get the report to run for one record store it, stop, run again for another one record, store that, and so on.
Very intriguing how simple and close it looks. I'm tempted to do the whole thing in perl, but the customer wants it as MS as possible.
Any kind souls up to the perplexity?
TRANSFORM Sum(tRundown.Quantity) AS SumOfQuantity SELECT tJob.Job, tStripe.Stripe, tChildPart.ChildPart
FROM tOperation INNER JOIN (((tChildPart INNER JOIN tJob ON tChildPart.JobID = tJob.JobID) INNER JOIN (tStripe INNER JOIN tRundown ON tStripe.StripeID = tRundown.StripeID) ON (tJob.JobID = tRundown.JobID) AND (tChildPart.ChildID = tRundown.ChildID)) INNER JOIN tPartParent ON (tPartParent.ParentID = tRundown.ParentID) AND (tPartParent.ParentID = tChildPart.ParentID) AND (tJob.JobID = tPartParent.JobID)) ON (tStripe.StripeID = tOperation.StripeID) AND (tOperation.OperationID = tRundown.OperationID)
GROUP BY tJob.Job, tStripe.Stripe, tChildPart.ChildPart, tChildPart.ChildLevel
PIVOT tOperation.Operation;
I want the values of Operation and Quantity, but I want it record-by-record, not consolidated. In other words, in a report, the results of Operation and Quantity want to look like this:
WXYZ
1 2 1 1
J K
1 1
A B C
1 1 1
NOT like this:
WXYZJKABC
1 2 1 1 11111
NOR like this:
WXYZJKABC
1 2 11
______11
_________111
When I put a parameter prompt in the crosstab query above for ChildPart, and key the ChildPart, I get one record that looks exactly like what I want. Then run again, type the next ChildPart, and so on. But there are up to 99 jobs, with 9 or fewer ChildParts each. Too much to expect someone to key each run. The above crosstab query somehow should be able to iterate over the ChildPart field in another SELECT query, using first ChildPart as the parameter to match. This would produce the a perfect one-record crosstab result. Somehow store that. Then run the crosstab query again for NEXT value of ChildPart. Store that right below the last. Repeat until EOF of the SELECT query. There will never be more than 9 values of ChildPart, nor more than 99 jobs so the entire run would be <99 jobs each with <9 ChildParts, so it doesn't seem huge.
I've done this:
http://support.microsoft.com/kb/328320
...but can't figure out how to get the report to run for one record store it, stop, run again for another one record, store that, and so on.
Very intriguing how simple and close it looks. I'm tempted to do the whole thing in perl, but the customer wants it as MS as possible.
Any kind souls up to the perplexity?