Chart ranges based on 3rd fields value

[Kemidan2014]

I have an access query that exports to Excel. what i would like to do is apply a simple horizontal bar graph that will graph number of days an item is late
thing is i need to generate 3 of these graphs based on the 3 business units we have. not only that but want to graph only items in the table of data that have reached a certain status piont

I am aware i can just make 3 queries to break this down but id like to limit that so i dont have to have 3 additional command buttons on my Access forms. so i would prefer to achieve this with 1 querie and then parse the data using the excel export.

Field 1 is name of the item which would be the Axis label. Field 2 would be Number of days, value i want graphed, Field 3 would be Status, what i want to validate and field 4 would be Business unit, value i want to group with.

Knowing the export is going to drop the data in any order. in another sheet is where i want to do all the formulas or other work. what would be a strategy/functions i could do with the data in excel to arrange it in the way i need knowing the next time i run the export the data will be different and thus automatically updated.

 

[excel_learnerz]

You can achieve this by using a combination of Excel features such as PivotTable, PivotChart, and Slicers. Here's how you can set this up:

1. Export your Access query data to Excel.
2. Create a PivotTable using the exported data: a. Click on any cell within the exported data. b. Go to the Insert tab and click on PivotTable. c. Choose where you want the PivotTable to be placed (either a new worksheet or an existing one). d. Click OK.
3. Set up the PivotTable: a. Drag Field 3 (Status) to the Filters area. b. Drag Field 4 (Business unit) to the Rows area. c. Drag Field 1 (Name of the item) to the Rows area below Field 4 (Business unit). d. Drag Field 2 (Number of days) to the Values area.
4. Filter the PivotTable based on the status point: a. Click on the dropdown arrow in the Status filter. b. Select the desired status point and click OK.
5. Create a PivotChart: a. Click on any cell within the PivotTable. b. Go to the Insert tab and click on PivotChart. c. Choose the Bar chart type and click OK.
6. Create Slicers for Business units: a. Click on any cell within the PivotTable. b. Go to the PivotTable Analyze tab (or Analyze, depending on your Excel version) and click on Insert Slicer. c. Check Field 4 (Business unit) and click OK. d. Use the slicer to filter the PivotTable and the PivotChart by each Business unit.

Now, when you update your exported data, the PivotTable, PivotChart, and Slicers will also update automatically. Just make sure to refresh the PivotTable after updating the data by clicking on any cell within the PivotTable, then go to the PivotTable Analyze tab and click on Refresh.