Solved

sql code for crosstab

Posted on 2014-12-02
1
100 Views
Last Modified: 2014-12-11
PARAMETERS [start date] DateTime, [end date] DateTime;
SELECT WO.ID, Sum(WO.GALLONSGAS) AS SumOfGALLONSGAS, Sum(WO.COSTPERGAL) AS SumOfCOSTPERGAL, ([SumOfGALLONSGAS]*[SumOfCOSTPERGAL]) AS TOTALCOST
FROM WO INNER JOIN VEHICLE ON WO.ID = VEHICLE.Id
WHERE (((WO.EVENTDATE) Between [Start Date] And [End Date]))
GROUP BY WO.ID
HAVING (((WO.ID) Not Like "GA*" And (WO.ID) Not Like "FT*" And (WO.ID) Not Like "BH*" And (WO.ID) Not Like "BD*" And (WO.ID) Not Like "BC*" And (WO.ID) Not Like "BL*" And (WO.ID) Not Like "GOLFCT*" And (WO.ID) Not Like "KUBOTA*" And (WO.ID) Not Like "PW*" And (WO.ID) Not Like "TR*" And (WO.ID) Not Like "WE*" And (WO.ID) Not Like "WG*" And (WO.ID) Not Like "EXMARK*"))
ORDER BY WO.ID;
I am trying to get the totalcost for August, Sept., and November of 2014.  I want the sql code create sample below.  What can I add to the code to produce sample below?  The id are rows, the months are column headings, and totalcost is value for each month.


Id        Aug            Sept          Nov
Id       totalcost    totalcost   totalcost
Id
id
0
Comment
Question by:campbme
1 Comment
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40477462
I don't have Access on this machine, but this is what I think you need.
PARAMETERS [start date] DateTime, [end date] DateTime;

TRANSFORM Sum(TotalCost) AS Cost
SELECT ID
  FROM (SELECT month(wo.eventdate) as TheMonth, WO.ID as ID, Sum(WO.GALLONSGAS) AS SumOfGALLONSGAS, Sum(WO.COSTPERGAL) AS SumOfCOSTPERGAL, ([SumOfGALLONSGAS]*[SumOfCOSTPERGAL]) AS TOTALCOST
FROM WO INNER JOIN VEHICLE ON WO.ID = VEHICLE.Id
WHERE (((WO.EVENTDATE) Between [Start Date] And [End Date]))
GROUP BY WO.ID
HAVING (((WO.ID) Not Like "GA*" And (WO.ID) Not Like "FT*" And (WO.ID) Not Like "BH*" And (WO.ID) Not Like "BD*" And (WO.ID) Not Like "BC*" And (WO.ID) Not Like "BL*" And (WO.ID) Not Like "GOLFCT*" And (WO.ID) Not Like "KUBOTA*" And (WO.ID) Not Like "PW*" And (WO.ID) Not Like "TR*" And (WO.ID) Not Like "WE*" And (WO.ID) Not Like "WG*" And (WO.ID) Not Like "EXMARK*"))
ORDER BY WO.ID)
GROUP BY ID
PIVOT theMonth

Open in new window


If you have any problems with this, please post some sample data from the two tables.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

You may have a outside contractor who comes in once a week or seasonal to do some work in your office but you only want to give him access to the programs and files he needs and keep privet all other documents and programs, can you do this on a loca…
A quick guide on how to use Group Policy to create a custom power plan and set it active on Windows 7.
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now