Query for charts

[Chipster]

Hello, I have a table with student data listed for both their primary training and advanced training. Once each student completes a phase their record is updated, but all the information is held in one table. We need to get percentages for several categories from this table, however I need it filtered to a specific fiscal year. For that, I included a calculated data field that returns the 4-digit year for each phase at the time the student completes [Pri FY] and [Adv FY].

Table Fields:
[Pipeline Assigned] = M, R, V, T
[1st Preference] = M, R, V, T
[Preference Granted] = Calculated field returns 1st Choice, 2nd Choice, & 3rd Choice based on each student's assignment
[System Assigned] = A, B, C, D
[Pri FY] = Calculated field
[Adv FY] = Calculated field

So far, I have been able to get some of the information I need filtered by using a query with criteria matching the FY however each time I open the report with the pie charts I have to enter my filter (FY) multiple times (once for each chart).

Charts needed:
(1) Shows % of students who were assigned each pipeline
(2) Shows % of students who wanted each pipeline as their 1st choice
(3) Shows % of students who received their 1st choice
(4) Shows % of students who were assigned each system (This chart needs to be filtered by [Adv FY], all others by [Pri FY])

There are a couple more charts, but if I can get help here then I may be able to figure out the rest.

Thanks,
Chip

Please let me know how I can help you help me. I

 

[plog]

I have to enter my filter (FY) multiple times (once for each chart)

That's because each chart runs the query for itself, even if based on a query you've already run. My advice for this is to create a form where you can input the year, then click a button to generate your charts. That way, every query execution can look at the input box and get the required data from there and not prompt you every time.

You didn't mention any other specific issues you are having, so let me give you general advice. I work backwards with charts, actually, I start in Excel. I create fake data to populate the columns I think I need to generate a chart. Then I try and make the chart with the characteristics I want (type, x-axis data, y-axis data, plot points, etc.). If it doesn't work, I add columns to the data until I am able to generate the chart I want. That let's me know which columns of data I need for each chart. Then I turn to Access and generate a query which will produce those columns of data. Once I have that query, I make my chart in Access because I've solved the data side of it and now just have to wrestle with Access to layout the chart.

If you still need help, let me know if you are having trouble with the data portion (query object) or the layout portion (report object). And then post what you are starting with and what you hope to end up with so I know where you are and where you are trying to get to.

 

[Chipster]

I do have a form that filters the query by FY, however after the form closes I am asked again for the parameter for each form. If I keep the filter form open, it works just fine but then I have to close the form manually. The event macro I created for the "OK" button is:
OpenQuery [Selectors Query]
OpenReport [Charts Report]
CloseWindow [Selectors Query]
CloseWindow [Fiscal Year Form]

How can I keep the focus on the report while the query runs with the filter in the form before the form closes?

Chip

 

[plog]

I believe you can use IsLoaded (https://msdn.microsoft.com/en-us/library/office/Ff194656.aspx) to see if a report is open. And Application.Wait (https://msdn.microsoft.com/en-us/library/office/Ff822851.aspx) to pause execution of your code.

Use them together and you can keep checking until all the reports are open and then close your form.