Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Add Crosstab values

I have created a crosstab query and require a total for each row. can that be achieved in the crosstab

TRANSFORM Sum(qry_datehires.[count(DDAI_SM_EquipmentHireLogID)]) AS [SumOfcount(DDAI_SM_EquipmentHireLogID)]
SELECT qry_datehires.[date(SM_HireDate_TimeOut)]
FROM qry_datehires LEFT JOIN tbllocation ON qry_datehires.SM_LocationID = tbllocation.LocationID
GROUP BY qry_datehires.[date(SM_HireDate_TimeOut)]
ORDER BY qry_datehires.[date(SM_HireDate_TimeOut)] DESC 
PIVOT tbllocation.Location;

Open in new window

0
Brogrim
Asked:
Brogrim
1 Solution
 
Kelvin SparksCommented:
TRANSFORM Sum(qry_datehires.[count(DDAI_SM_EquipmentHireLogID)]) AS [SumOfcount(DDAI_SM_EquipmentHireLogID)]
SELECT qry_datehires.[date(SM_HireDate_TimeOut)], Sum(qry_datehires.[count(DDAI_SM_EquipmentHireLogID)]) AS Total
FROM qry_datehires LEFT JOIN tbllocation ON qry_datehires.SM_LocationID = tbllocation.LocationID
GROUP BY qry_datehires.[date(SM_HireDate_TimeOut)]
ORDER BY qry_datehires.[date(SM_HireDate_TimeOut)] DESC
PIVOT tbllocation.Location;

Should give you what you want


Kelvin
0
 
BrogrimAuthor Commented:
Thanks
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now