Solved

Add  Crosstab values

Posted on 2013-06-18
2
313 Views
Last Modified: 2013-06-18
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
Comment
Question by:Brogrim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 39256026
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
 

Author Closing Comment

by:Brogrim
ID: 39256066
Thanks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question