cyberman55
Registered User.
- Local time
- Today, 01:06
- Joined
- Sep 22, 2012
- Messages
- 83
Hi all, I'm hoping one you SQLMeisters can help me out. I've got a very complex form going that updates MS charts based on user selections of a frame control (3) options, a treeview control (free download from http://www.jkp-ads.com/ and very nice all-code control solution) having 5 nodes (5 optiions) and two listboxes which, themselves populate via user selections. It's a complex mess, and I'll post on the coding forum for ideas on that since currently I'm using an unwieldy set of case select statements to manage it. I believe I have 225 permutations of possibilities right now. Specifically 1) Space inventory from the tree (all, selected account, selected building, selected floor, selected room) times three from the option frame (inspected, not inspected, both) and then all the listbox selections (by space inventory, by supervisory zone, etc).
I discovered through trial and error that to reuse the chart control with a fixed set of view settings (data series, axis settings, etc), it's best to change it's rowsource via a string, such as in this code snippet:
strGraphRowSource = "TRANSFORM Max([Percent]) AS [MaxOfPercent] SELECT [Status] FROM [qryInspRpt54] GROUP BY [Status] PIVOT [Building Name];"
Me.Graph02.RowSource = strGraphRowSource
Me.Graph02.Requery
Me.Graph02.ChartTitle.Text = "Percentage Inspected By Building-All Customers"
In order to somewhat reduce the sheer number of queries, I'd like to build this as a string whereby the embedded "qryInspRpt54" which is:
PARAMETERS [Forms]![frmInspectionReports]![StartDate] DateTime, [Forms]![frmInspectionReports]![EndDate] DateTime;
SELECT DISTINCT qryInspRpt52.[Building Name], qryInspRpt52.Status, ([qryInspRpt52].[sumofsf]/[qryinsprpt51].[sumofsf])*100 AS [Percent]
FROM qryInspRpt52 INNER JOIN qryInspRpt51 ON qryInspRpt52.[BLDG Code] = qryInspRpt51.[BLDG Code]
GROUP BY qryInspRpt52.[Building Name], qryInspRpt52.Status, ([qryInspRpt52].[sumofsf]/[qryinsprpt51].[sumofsf])*100
HAVING (((qryInspRpt52.Status)="Inspected"));
becomes part of the whole Transform statement. I suppose I could build both strings, but then this query depends on several others. There is some logic to the madness that's giving me a headache here - but I'm guessing their is some way to simplify the problem.
I discovered through trial and error that to reuse the chart control with a fixed set of view settings (data series, axis settings, etc), it's best to change it's rowsource via a string, such as in this code snippet:
strGraphRowSource = "TRANSFORM Max([Percent]) AS [MaxOfPercent] SELECT [Status] FROM [qryInspRpt54] GROUP BY [Status] PIVOT [Building Name];"
Me.Graph02.RowSource = strGraphRowSource
Me.Graph02.Requery
Me.Graph02.ChartTitle.Text = "Percentage Inspected By Building-All Customers"
In order to somewhat reduce the sheer number of queries, I'd like to build this as a string whereby the embedded "qryInspRpt54" which is:
PARAMETERS [Forms]![frmInspectionReports]![StartDate] DateTime, [Forms]![frmInspectionReports]![EndDate] DateTime;
SELECT DISTINCT qryInspRpt52.[Building Name], qryInspRpt52.Status, ([qryInspRpt52].[sumofsf]/[qryinsprpt51].[sumofsf])*100 AS [Percent]
FROM qryInspRpt52 INNER JOIN qryInspRpt51 ON qryInspRpt52.[BLDG Code] = qryInspRpt51.[BLDG Code]
GROUP BY qryInspRpt52.[Building Name], qryInspRpt52.Status, ([qryInspRpt52].[sumofsf]/[qryinsprpt51].[sumofsf])*100
HAVING (((qryInspRpt52.Status)="Inspected"));
becomes part of the whole Transform statement. I suppose I could build both strings, but then this query depends on several others. There is some logic to the madness that's giving me a headache here - but I'm guessing their is some way to simplify the problem.
