Showing all records in a Crosstab query - even null values (1 Viewer)

Hi there,

I have identified the problem but cannot solve it?

In the query below, records are showing up but only where items are allocated to a 'Kit' (as in KitNumber).

What I currently get is all the kit Numbers displayed but only when there is a count value in any of the kitNumber fields.

What I need to do is display all the records even if they are not allocated to a Kit?

The icing on the cake would be to display only the kits that have quantities (Count) instead of every Kit from 1 to R25.

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 displays correctly and makes sense?

Thank you very much

Sam Summers; that's a username blast from the past...

One quick solution would be to, in the query design view, right click on whatever your field is your crosstab's column heading and enter the Column Headings, separated by commas (i.e. "Q1","Q2","Q3","Q4")

Or, if the list is in somewhere in your database tables, base a query of this, sorting appropriately, and join it into your crosstab, using this as your crosstab column headers.

Hello again!

The column headings are the 1,2,3 etc.

Looking back at what i wrote in a rush looks incorrect for what i actually want to achieve!

What i want is to display all the Equipment even if it is not allocated to a kit?

For example right now i am getting all the kit numbers displayed horizontally but only two items (EquipmentDescription field) showing because they are the only items currently allocated to a kit.

Hope this makes sense?