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
> Percentage Query, Access 2016    
 
   
lex
post Feb 16 2017, 12:48 PM
Post#1



Posts: 564
Joined: 20-October 05



I have (what I call) a pretty complicated report I'm trying to simplify. This (hopefully) is not too much of a challenge for someone. I'd really appreciate some help to possibly learn how to clean this up.

The output I need (for charting) looks as follows

Attached File  2017_02_16_12_45_44.jpg ( 51.06K )Number of downloads: 3


Actually - after posting this - I need to separate the "Deficient" and "Non-Deficient", but think I can handle that.

I have tblImportedSchedule which I'm trying to get some charts out of. This table is related to PUBLIC_VW_TMS_TSC_CDTB and MAP_JOB which are basically a lookups.

MAP_JOB (linked via ODBC ) and tblImportedSchedule are joined on JOB_ID

MAP_JOB.JOB_ID is a Decimal 8 (as viewed in Access) and due to the way tblImportedSchedule comes out (and I haven't changed datatype) is a Short_Text 255.

I am using 3 queries qryJob_Historical > qryCountOfDeficientNotDeficient_Historical > qryHistorical to get the output.

qryJob_Historical

CODE
SELECT tblImportedSchedule.pktblImportedScheduleID, PUBLIC_VW_TMS_TSC_CDTB.SHRT_DESC AS TSC, tblImportedSchedule.JobNumber, tblImportedSchedule.blAnyLate, tblImportedSchedule.dtmReportDate, tblImportedSchedule.PrepareBasePlansLate, tblImportedSchedule.PlanReviewLate, tblImportedSchedule.PlanCompletionLate, tblImportedSchedule.OecMeetingLate, tblImportedSchedule.PlanTurnInLate, tblImportedSchedule.LettingLate
FROM PUBLIC_VW_TMS_TSC_CDTB INNER JOIN (tblImportedSchedule INNER JOIN MAP_JOB ON clng(tblImportedSchedule.[JobNumber]) = MAP_JOB.JOB_ID) ON PUBLIC_VW_TMS_TSC_CDTB.TSC_CD = MAP_JOB.TSC_CD;


qryCountOfDeficientNotDeficient_Historical

CODE
SELECT qryJob_Historical.dtmReportDate, qryJob_Historical.TSC AS Expr1, IIf(([qryJob_Historical]![blAnyLate]=-1) Or ([qryJob_Historical]![PrepareBasePlansLate]="YES") Or ([qryJob_Historical]![PlanReviewLate]="YES") Or ([qryJob_Historical]![PlanCompletionLate]="YES") Or ([qryJob_Historical]![OecMeetingLate]="YES") Or ([qryJob_Historical]![PlanTurnInLate]="YES") Or ([qryJob_Historical]![LettingLate]="YES"),[qryJob_Historical]![TSC] & "-Deficient",[qryJob_Historical]![TSC] & "-NotDeficient") AS Cat, Count(qryJob_Historical.pktblImportedScheduleID) AS CountOfID
FROM qryJob_Historical
GROUP BY qryJob_Historical.dtmReportDate, qryJob_Historical.TSC, IIf(([qryJob_Historical]![blAnyLate]=-1) Or ([qryJob_Historical]![PrepareBasePlansLate]="YES") Or ([qryJob_Historical]![PlanReviewLate]="YES") Or ([qryJob_Historical]![PlanCompletionLate]="YES") Or ([qryJob_Historical]![OecMeetingLate]="YES") Or ([qryJob_Historical]![PlanTurnInLate]="YES") Or ([qryJob_Historical]![LettingLate]="YES"),[qryJob_Historical]![TSC] & "-Deficient",[qryJob_Historical]![TSC] & "-NotDeficient");


qryHistorical

CODE
TRANSFORM Nz(Max(qryCountOfDeficientNotDeficient_Historical.CountOfID),0) AS MaxOfCountOfID
SELECT qryCountOfDeficientNotDeficient_Historical.Cat
FROM qryCountOfDeficientNotDeficient_Historical
GROUP BY qryCountOfDeficientNotDeficient_Historical.Cat
PIVOT qryCountOfDeficientNotDeficient_Historical.dtmReportDate;


For the most part, what I'm doing works, but I would like to minimize manipulation in Excel and output the table as used by the chart directly, need to calculate percentages and totals and don't quite know how to do it. Any advice is appreciated. If you'd like a sample file- I can do that.


Lex


Go to the top of the page
 
projecttoday
post Feb 16 2017, 12:56 PM
Post#2


UtterAccess VIP
Posts: 7,973
Joined: 10-February 04
From: South Charleston, WV


So you are creating the totals and then dumping them in Excel? I think it would be simpler and better to dump the data in Excel and then create a pivot table. You get an automatic drill-down that way. You can use deficient and non-deficient and row headings. You can produce the whole thing with VBA code in Access.

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Feb 16 2017, 09:17 PM
Post#3



Posts: 1,945
Joined: 27-February 09



Either a Pivot in Excel or PowerPivot... then you can build your own measures etc. The downside to creating your own measures is that they can get tricky fast. If you go that route, check out Rob Collie's website PowerPivotPro.com

How does the percentage stuff work? What is the "whole" that each cell value a percentage of?
Go to the top of the page
 
lex
post Feb 17 2017, 07:01 AM
Post#4



Posts: 564
Joined: 20-October 05



Thanks for the comments, MadPiet and projecttoday.

So the output of the last crosstab query is a count of each (category and date). The total is a total (by date) of each category. I guess I could have yet another query give me the totalcount, and use that in qryCountOfDeficientNotDeficient_Historical to output to qryHistorical. I haven't tried that yet. Part of the guidance for which I was looking was on SQL - ie am I doing this the right way. I get uncomfortable with it as I'm not that good with SQL .... but am trying to learn, and just like the comfort of knowing someone is looking over my shoulder.

The other piece is that I'm trying to automate as much as I can to hand off to someone so that they can click a couple buttons and get a chart. So, I was trying to get the table I showed in the original post straight out of Access, then have the user work in Excel from there.

I'm not that familiar with Pivottables, or the VBA coding of them.

Thanks
Lex
Go to the top of the page
 
projecttoday
post Feb 17 2017, 07:47 AM
Post#5


UtterAccess VIP
Posts: 7,973
Joined: 10-February 04
From: South Charleston, WV


Yes, you can do a spreadsheet from a click. You do that with an Excel object in your code if you haven't already. Do a search on "copyfromrecordset" for examples. That's how you drop your query result on the spreadsheet. If you want to do the totals in Access that's how you would do it.

I suggested creating a pivot table in Excel because I think the result is better. You can get the code for this by recording it in Excel and putting it in your database. But you have to tweak it a little. If you're not familiar with this, just be aware. It will probably take you longer than doing it the other way. You need to query the size of the data and you need to "qualify" the code if you want the click to work more than once per opening of the database.

I'm not familiar with PowerPivot.

--------------------
Robert Crouser
Go to the top of the page
 
lex
post Feb 17 2017, 09:06 AM
Post#6



Posts: 564
Joined: 20-October 05



So, I believe when you say

QUOTE
If you want to do the totals in Access that's how you would do it.


You mean you would calc percentages in Access?

I have the result of qryCountOfDeficientNotDeficient_Historical (shown above) that looks like this



Attached File  2017_02_17_8_46_07.jpg ( 170.67K )Number of downloads: 2


Actually that is just Deficient as I separated it out.

and a total by report date query

CODE
SELECT qryJob_Historical.dtmReportDate, Count(qryJob_Historical.pktblImportedScheduleID) AS CountOfpktblImportedScheduleID
FROM qryJob_Historical
GROUP BY qryJob_Historical.dtmReportDate;


with result

Attached File  2017_02_17_9_03_47.jpg ( 26.46K )Number of downloads: 0


I need to combine the two in order to get the percentages. Were you suggesting this be done in Access? or in Excel?

Lex
Go to the top of the page
 
projecttoday
post Feb 17 2017, 09:49 AM
Post#7


UtterAccess VIP
Posts: 7,973
Joined: 10-February 04
From: South Charleston, WV


Assuming you definitely want a spreadsheet my suggestion is to do the calculations on the Excel side. Your Access code runs a query that reads the detail data. Your code drops the results of this query on the spreadsheet and then creates a pivot table which does the totals and percentages.

The other way is to query the totals and percentages in Access and then drop the results on a spreadsheet.

The choice is yours.

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Feb 17 2017, 10:03 AM
Post#8



Posts: 1,945
Joined: 27-February 09



I think this would be infinitely easier in Excel.

Personally, I really like just grabbing the data (just raw, no totals/counts/averages), and doing all the statistics in Excel Pivot Tables. You can do the same in PowerBI and it does proper drill through. The only downside is it helps a lot if you know a little DAX.

If you can post a sanitized version of your data (obscure any confidential stuff... numbers, names, whatever) and post the Excel file, and an idea of what you want, I'm pretty sure I could bang something out that can get you at least most of the way to your solution.
This post has been edited by MadPiet: Feb 17 2017, 10:05 AM
Go to the top of the page
 
lex
post Feb 21 2017, 11:18 AM
Post#9



Posts: 564
Joined: 20-October 05



Thanks to both MadPiet and projecttoday ... and Sorry for taking so long to reply

Below is the result of qryJob_Historical. Hopefully from the discussion below, you can see what I'm looking for.


Attached File  qryJob_Historical.zip ( 39.6K )Number of downloads: 4


Also, the chart is below

Attached File  2017_02_21_11_14_11.jpg ( 109.62K )Number of downloads: 6


Again, thanks for your help. I don't do Pivottables much. I'll give it go while awaiting (hopefully you're still up for it) your thoughts.

Lex
Go to the top of the page
 
MadPiet
post Feb 26 2017, 02:29 AM
Post#10



Posts: 1,945
Joined: 27-February 09



Lex,
What's the percentage of ? It's a total, sure, but how do you calculate it? (and I think you're missing a column in your table...)

If you've never ever done PowerPivot, then you might want to watch Chandoo's introduction to PowerPivot on YouTube. His stuff is excellent. (He's an Excel MVP) Sure, it's like 25 minutes, but if you want to understand PowerPivot and all the cool things you can do with it, it's absolutely worth it.

Pieter
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th April 2017 - 04:01 AM