Bar Graph

[scoobz1234]

Question... i Wanted to create a Graph that has a bar for each catagory or column that has dates.. i want it to filter to show only the dates greater than 12-31-2011 so basically all dates in 2012.. i figured out how to do a query but i was only able to do a query for ONE column at a time so i'll have like 30 querys...is there a way to do this with one query and have each bar on the graph be for each column and have the graph showing how many people have completed these after 12-31-2011

Relevant database attached in https://www.accessforums.net/showthr...tact-file-help

[scoobz1234]

would making individual queries for each catagory and then making one pivot chart with each bar pertaining to a query be possible?

[June7]

A graph can have only one RowSource. It can be a table or query or an SQL statement. The bars represent data of the RowSource.

What you want can be done but your data structure makes it difficult. For starters, the date fields are text, not date/time datatype. This must be changed in order to do filter/sort by date criteria. Fix that and post a new database and we can proceed. Can make a copy and delete all those reports not needed for this issue, run Compact & Repair, then zip.

[scoobz1234]

Tester DB.zip Here is the Database i changed the items to dates and i also set them to show as a medium date. pretty much anything with a date except birthdates i want to show on a bar graph, ULTIMATELY i would like it to show Percentage of the dates that are "Current" we call it Training Completion percentage. if not a simple number complete is great. also is there a way to show the number total of people on the data base so it can easily be seen how many people are missing the completion?

[June7]

The first step is to rearrange the data to a normalized structure. This requires a UNION query. There is no designer or wizard for UNION, must type in the SQL view of query designer. There is a limit of 50 lines in a UNION (unless this has changed with Access 2010). This example shows only 3 of the training categories from your table. Continue adding lines for the others you want.

SELECT ID, [Last Name], [First Name], Rank, "Swim" AS Category, [Swim Requal Date] As CatDate FROM [Main Database]
UNION SELECT ID, [Last Name], [First Name], Rank, "Orm", [Annual Orm Training-NKO] FROM [Main Database]
UNION SELECT ID, [Last Name], [First Name], Rank, "Basic", [Basic Info & Personal Security] FROM [Main Database];

Use the UNION query as basis for aggregate calculations and for a CROSSTAB query.

SELECT Category, Count(ID) AS CountOfID, (SELECT Count(ID) As Total FROM [Main Database]) AS TotalStaff
FROM MainUNION
WHERE CatDate>#12/31/2011#
GROUP BY MainUNION.Category;

That query I called MainSUM. Set the RowSource of a bar graph to SELECT Category, CountOfID FROM MainSUM; and a simple charting of the total completed for each category will show.

I am not sure what the percentage calc you want is. Is it the percentage of total categories each individual has completed meaning a chart that shows all 63 individuals, or an overall percentage of staff completed by category? For the latter try this as graph RowSource:
SELECT [Category], Sum([CountOfID]/[TotalStaff])*100 AS Percentage FROM MainSUM GROUP BY [Category];

I just noticed the table has dates greater than the current date (2013, 2014). These records are not excluded from the above queries. Apply whatever filter criteria is needed in the MainSUM query.

[scoobz1234]

some of the dates are drop dead dates, the current training is void on that date, some training is Fiscal Year, and some is Calendar Year. having said that, is there a way to make this do that for the specific ones.. i have added all my fields to the UNION that have a date (except the Birthdate one)...

[June7]

By 'specific ones' you mean the different date categories? How can we know which are 'drop dead', which are Fiscal Year, and which are Calendar Year?

Have you tried the graphs I suggested as a test?

[scoobz1234]

(PFT/NBC/ORM/BASIC INFO/ANTI TERRORSIM/COMBAT TRAFFICKING) These are all calendar year so Greater than 12-31-2011.. (RIFLE/PISTOL/INFORMATION ASSURANCE/SUICIDEPREVENTION/STD&HIV/ALCOHOL&SUBSTANCE/TOBACCO/NUTRITION/INJURY PREVENTION/PHYSICAL FITNESS) These are all Fiscal Year so Greater than 10-01-11.. the left over ones after this are the CFT which is Greater than 06-30-2011...and swim qual...swim qual is a tricky one, it has to be greater than Date() i believe the way to do it is, as long as its after the current days date its good.

[June7]

That does complicate things. Approach options:

1. Include WHERE clause in each SELECT line of the UNION query.

2. Build a public function that can be called from any query that uses the UNION as data source and will return the correct date parameter for each record based on the category for each record.

Never done either before but just tested No1 and seems to work.

If fiscal year starts 10/1/yyyy use >9/30/yyyy

Regardless of the approach, making the date parameter dynamic for the changing years is another issue.

[scoobz1234]

so it would probably be wise to set this up with seperate graphs, multiple unions? one for the Fiscal Year items and one for the Annual..

[June7]

One union should serve. Just build queries off of it for the graphing.

It can be done on one graph as I have described. I would go with option 2 but then I know how to write code.