Using SUM in crosstab query

[Sam Summers]

Hello,

Can anyone help me?

I am trying to sum up the contents of certain fields in my crosstab query to display the totals in a sub-report created from the crosstab query.

You can see in the attached screenshot of my subform, the package numbers from 1 to R25 (as specified as column headings in the SQL below) with some having values.
I am trying to create the sum of Packages with items (Total Qty) as well as the Total Weight of all the items and the total cost (both of these are not working correctly either?)

TRANSFORM Count(ItemsInKits.EquipmentID) AS CountOfEquipmentID

SELECT ItemsInKits.EquipDescription, Equipment.LocationID, Location.JobNo, EquipLookup.UnitKg, EquipLookup.UnitValue, EquipLookup.HatchCode, EquipLookup.CommodityCode

FROM ((ItemsInKits INNER JOIN Location ON ItemsInKits.LocationID = Location.LocationID) INNER JOIN Equipment ON (Location.LocationID = Equipment.LocationID) AND (ItemsInKits.EquipmentID = Equipment.EquipmentID)) INNER JOIN EquipLookup ON (EquipLookup.EquipRef = Equipment.EquipRef) AND (ItemsInKits.EquipRef = EquipLookup.EquipRef)

WHERE (((Equipment.LocationID)=[Forms]![ManifestDetails]![LocationID].[Text]))

GROUP BY ItemsInKits.EquipDescription, Equipment.LocationID, Location.JobNo, EquipLookup.UnitKg, EquipLookup.UnitValue, EquipLookup.HatchCode, EquipLookup.CommodityCode

PIVOT ItemsInKits.KitNumber In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,"R1","R2","R3","R4","R5","R6","R7","R8","R9","R10","R11","R12","R13","R14","R15","R16","R17","R18","R19","R20","R21","R22","R23","R24","R25");

I hope this makes sense?

Many thanks in advance

 

[Guus2005]

Crosstab queries are not the easiest queries to figure out. You might want to add a sample database describing the problem.

HTH

 

[Sam Summers]

Thank you very much for replying.

The easiest way i can explain is from the screen shot. The Database itself is functioning fine but my company have a standard equipment manifest which i am attempting to reproduce in a report?

The crosstab query is displaying the number of items (EquipDescription) in each package.

At the right of the subreport you will see:

Total Qty
Weight (Kg)
Total Value

These are the sums of:

Items of each EquipDescription in all packages (Total Qty).
So from the screenshot you will see that Total Qty should be displaying 2 as there are items in R3 and R6.

Next would be Unit Kg x Total Qty which will give Weight (Kg)

and finally:

Unit Value x Total Qty should give Total Value

Hope this makes sense again?

This is my first time using a crosstab query

 

[JHB]

...
TRANSFORM Count(ItemsInKits.EquipmentID) AS CountOfEquipmentID

SELECT ItemsInKits.EquipDescription, Equipment.LocationID, Location.JobNo, EquipLookup.UnitKg, EquipLookup.UnitValue, EquipLookup.HatchCode, EquipLookup.CommodityCode, Count(ItemsInKits.EquipmentID) AS TotalOfEquipmentID

FROM ((ItemsInKits INNER JOIN Location ON ItemsInKits.LocationID = Location.LocationID) INNER JOIN Equipment ON (Location.LocationID = Equipment.LocationID) AND (ItemsInKits.EquipmentID = Equipment.EquipmentID)) INNER JOIN EquipLookup ON (EquipLookup.EquipRef = Equipment.EquipRef) AND (ItemsInKits.EquipRef = EquipLookup.EquipRef)

WHERE (((Equipment.LocationID)=[Forms]![ManifestDetails]![LocationID].[Text]))

GROUP BY ItemsInKits.EquipDescription, Equipment.LocationID, Location.JobNo, EquipLookup.UnitKg, EquipLookup.UnitValue, EquipLookup.HatchCode, EquipLookup.CommodityCode

PIVOT ItemsInKits.KitNumber In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,"R1","R2","R3","R4","R5","R6","R7","R8","R9","R10","R11","R12","R13","R14","R15","R16","R17","R18","R19","R20","R21","R22","R23","R24","R25");

Try adding the red marked, (not tested).

Next would be Unit Kg x Total Qty which will give Weight (Kg)

and finally:

Unit Value x Total Qty should give Total Value

The above can be calculated in the report, when first the TotalOfEquipmentID is found.

 

[Sam Summers]

Mange Tak JHB!!!

As easy as that! it worked!!

 

[JHB]

Mange Tak JHB!!!

As easy as that! it worked!!

Velbekom!

Every thing is easy, (if you know)!

 

[Sam Summers]

One final thing - I could be missing something in my excitement but when I try and set the textbox property in the subreport to the TotalOfEquipmentID, it isn't in the list?

 

[Sam Summers]

Sorry, solved it myself.

As it was a new field, I had to set it to include it in the subreport query!

Tak for det
Hav en god dag

 

[JHB]

Hav en god dag

I lige måde.