crosstab query.. i think..

[stephendeloach via AccessMonster.com]

I am trying to make a query that has Rig1, Rig2, Rig3, Rig4 on the top..
Category 1-15 down the left side and the Totals of each category for that Rig
under the Rig name (Rig1, 2, 3). Sounds simple.. In recent reports I have the
Total = total_value: Sum(Descriptions.[1Quantity]*Descriptions.[1UnitPrice])
in the query.. I cant seem to figure it out.

 

[Guest]

It sounds like you need a crosstab.

Post a sample of your data and what you want the results to look like from
that data.

Post your query SQL.

 

[stephendeloach via AccessMonster.com]

Here is an example of one query I have. It basically has the same info I need,
just in a different format.
SELECT Main.Rig, Main.LeaseName, Descriptions.[1Category], Sum(Descriptions.
[1Quantity]*Descriptions.[1UnitPrice]) AS total_value
FROM Main INNER JOIN Descriptions ON Main.Invoice = Descriptions.[1Invoice]
GROUP BY Main.Rig, Main.LeaseName, Descriptions.[1Category];

I need the Main.Rig to be across the top of the page and Descriptions.
[1Category] to be down the left side is that is what you are asking for...
Thanks

It sounds like you need a crosstab.

Post a sample of your data and what you want the results to look like from
that data.

Post your query SQL.

I am trying to make a query that has Rig1, Rig2, Rig3, Rig4 on the top..
Category 1-15 down the left side and the Totals of each category for that Rig
under the Rig name (Rig1, 2, 3). Sounds simple.. In recent reports I have the
Total = total_value: Sum(Descriptions.[1Quantity]*Descriptions.[1UnitPrice])
in the query.. I cant seem to figure it out.

 

[Guest]

Try this --
TRANSFORM Sum([1UnitPrice]*[1Quantity]) AS Expr1
SELECT Descriptions.[1Category], Sum([1UnitPrice]*[1Quantity]) AS Total
FROM Main INNER JOIN Descriptions ON Main.Invoice = Descriptions.[1Invoice]
GROUP BY Descriptions.[1Category]
PIVOT "Rig " & [Rig];

--
KARL DEWEY
Build a little - Test a little

Here is an example of one query I have. It basically has the same info I need,
just in a different format.
SELECT Main.Rig, Main.LeaseName, Descriptions.[1Category], Sum(Descriptions.
[1Quantity]*Descriptions.[1UnitPrice]) AS total_value
FROM Main INNER JOIN Descriptions ON Main.Invoice = Descriptions.[1Invoice]
GROUP BY Main.Rig, Main.LeaseName, Descriptions.[1Category];

I need the Main.Rig to be across the top of the page and Descriptions.
[1Category] to be down the left side is that is what you are asking for...
Thanks

It sounds like you need a crosstab.

Post a sample of your data and what you want the results to look like from
that data.

Post your query SQL.

I am trying to make a query that has Rig1, Rig2, Rig3, Rig4 on the top..
Category 1-15 down the left side and the Totals of each category for that Rig
under the Rig name (Rig1, 2, 3). Sounds simple.. In recent reports I have the
Total = total_value: Sum(Descriptions.[1Quantity]*Descriptions.[1UnitPrice])
in the query.. I cant seem to figure it out.

 

[stephendeloach via AccessMonster.com]

wow.. works like a charm! thanks!

Try this --
TRANSFORM Sum([1UnitPrice]*[1Quantity]) AS Expr1
SELECT Descriptions.[1Category], Sum([1UnitPrice]*[1Quantity]) AS Total
FROM Main INNER JOIN Descriptions ON Main.Invoice = Descriptions.[1Invoice]
GROUP BY Descriptions.[1Category]
PIVOT "Rig " & [Rig];

Here is an example of one query I have. It basically has the same info I need,
just in a different format.

[quoted text clipped - 18 lines]

Total = total_value: Sum(Descriptions.[1Quantity]*Descriptions.[1UnitPrice])
in the query.. I cant seem to figure it out.