UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Format Query So Months Are Sorted Jan-dec, Access 2016    
 
   
Scooter1
post Mar 13 2018, 08:17 PM
Post#1



Posts: 62
Joined: 8-January 18



HI UA Pros,


I have one of my old project db(closed/completed) that has the same type of table set up. So here is my problem: I have a query that will list data monthly, the issue I am having is that the months are not sorted from Jan- Dec in that order (ASC). I need to have it in this format so that when I create graphs they go in nicely. I know I can use excel, but I am trying to see if this can be done in Access.

So it shoud be like the following:

Jan---data
Feb--data
Mar--data
etc., etc.,

***** query to check are: qry_servicesPerMonthGraph and qryDataForGraph

Thanks!



Attached File(s)
Attached File  TechSupport_3_Rev9.zip ( 74.78K )Number of downloads: 5
 
Go to the top of the page
 
DanielPineault
post Mar 13 2018, 08:33 PM
Post#2


UtterAccess VIP
Posts: 6,270
Joined: 30-June 11



There are different ways that this can be handled, but here is one

qry_ServicesPerMonthGraph

SQL
SELECT CLng(Format([dateClosed],"m")) AS MonthNo, Format([dateClosed],"mmm") AS [Month], tblTicket.ticket, Count(tblServices.service) AS CountOfservice
FROM (tblServiceTicket RIGHT JOIN tblTicket ON tblServiceTicket.ticketID = tblTicket.ticketID) LEFT JOIN tblServices ON tblServiceTicket.serviceID = tblServices.serviceID
GROUP BY CLng(Format([dateClosed],"m")), Format([dateClosed],"mmm"), tblTicket.ticket;





qryDataForGraph

SQL
SELECT qry_ServicesPerMonthGraph.Month, Count(qry_ServicesPerMonthGraph.ticket) AS CountOfticket, Sum(qry_ServicesPerMonthGraph.CountOfservice) AS SumOfCountOfservice
FROM qry_ServicesPerMonthGraph GROUP BY qry_ServicesPerMonthGraph.Month, qry_ServicesPerMonthGraph.MonthNo
ORDER BY qry_ServicesPerMonthGraph.MonthNo;



Go to the top of the page
 
Scooter1
post Mar 13 2018, 09:25 PM
Post#3



Posts: 62
Joined: 8-January 18



Daniel,

This looks good, so could you please point me in the right direction on how to make sure that the list also includes the rest of the other months? If no data for those months I just need them to show up as 0 value. So it should be static Jan, Feb, Mar, April etc to Dec.


Hope you could assist, and thank you for this quick response!!
Go to the top of the page
 
DanielPineault
post Mar 14 2018, 05:04 AM
Post#4


UtterAccess VIP
Posts: 6,270
Joined: 30-June 11



You'd need to create a months tables and then use a Cartesian query to ensure they are always include in the tally.
Go to the top of the page
 
GroverParkGeorge
post Mar 14 2018, 06:27 AM
Post#5


UA Admin
Posts: 34,074
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

I've long held the belief that "try it yourself and see what YOU can accomplish" is a great learning strategy.
Go to the top of the page
 
Scooter1
post Mar 15 2018, 09:02 PM
Post#6



Posts: 62
Joined: 8-January 18



Thanks everyone, I got that going ok now.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2018 - 12:01 AM