Totals Query to Excel with range adjustment

[netrusher]

I currently have a Totals Query that I want to take to excel to produce a pie chart.

Select SystemGroup, Count(*) As [Mechanical Totals]
From WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And  ([TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt])
Group By SystemGroup

UNION ALL Select 'Total Work Units', count([WorkUnit]) from (select distinct [WorkUnit]
From WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And  ([TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]));

The results of the query looks as follows:

SYSTEM GROUP MECHANICAL TOTALS
Coolant System 3
Cooling Systme 1
Engine 3
Transmission 3
Total Work Units 5

The System Group will expand or shrink depending on how many work units and what time frame my query is on. I know that I will need to make this a Dynamic Chart in Excel to allow for the expanding and shrinking. My problem is as follows:

I want the pie chart to only show the System Groups and not the Total Work Units as part of the pie chart. I would like the Total Work Units to appear as a title on the Pie Chart. Can this be done? If so how?

 

[SkipVought]

Hi,

I'd have two queries: One for System Group = Total Work Units and the other for System Group <> Total Work Units.

I'd also query FROM Excel. You can use code like this to NAME the resultset range dynamically...

Sub GetWIP()
    Dim sSQL As String
    
    sSQL = "SELECT"
    sSQL = sSQL & "  MFG_ORD"
    sSQL = sSQL & ", PART_ID"
    sSQL = sSQL & ", NG, QTY"
    sSQL = sSQL & ", RC"
    sSQL = sSQL & ", SF_STATUS"
    sSQL = sSQL & ", CURRENT_OP"
    sSQL = sSQL & ", WIP_DAYS"
    sSQL = sSQL & ", LOCATION"
    sSQL = sSQL & ", SF_CC"
    sSQL = sSQL & ", MACH_GRP"
    sSQL = sSQL & ", SF_OPER"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM FPRPTSAR.MFG_ORDER_INFO"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE SF_CC Is Not Null"
    sSQL = sSQL & "  AND RC Like 'M%'"
    sSQL = sSQL & "  And RC<>'MBY'"
    
    With wsWIP_Data.QueryTables(1)
        .Connection = Array(Array( _
        "ODBC;DSN=A010PROD;;DBQ=A010PROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;" _
        ), Array("CSR=F;FWC=F;PFC=10;TLO=0;"))
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False[b]
        Application.DisplayAlerts = False
        .ResultRange.CurrentRegion.CreateNames True, False, False, False
        Application.DisplayAlerts = True[/b]
    End With
End Sub

Skip,

_[red][/red]

 

[netrusher]

WOW!

That looks awesome but is way over my head. Can you explain a little more as to where the code goes and anything else you can to help me understand what is happening with this? and thanks!

 

[SkipVought]

You must have a driver configured (Start/Settings/Control Panel/Administrative Tools/Data Sources (ODBC)...

This starts with native Excel functionality.

Data/Get External Data/New Database Query...

Select MS Access Driver - Drill down to your Access Data base - select your table(s)/Query(s) - Edit the query in the QBE grid - File/Return Data to Excel.

This has inserted a QueryTable in your sheet and returns the resultset of data. To get FRESH data from Access, Data/Refresh.

To generate the CODE, Tools/Macros/Record new macro - Data/Get External Data/Edit Query - In the QBE Editor, File/return Data to Excel - STOP the macro recorder. Now open the VB Editor and observe your code.

Post back and we'll help you customize your code.




Skip,

_[red][/red]