Solved Help with Charts (1 Viewer)

Weekleyba

Registered User.
Local time
Yesterday, 20:20
Joined
Oct 10, 2013
Messages
586
I have a small db for scoring projects.
It has an input form, with one record per project.
As part of the record, I have five categories to score.
Namely, Life Safety Code, Other Codes Standards, Backlog Of MI, Reg Accreditation, & Program Improvement.
The user inputs a value of 0-100 for each Category.

Everything seemed to be fine until the boss requested a report with charts...
One of the charts I wish to create is a column chart with the Category as the x axis, & the # of times the value is > 0, for the y axis.
I can get the # of times the value is > 0 by using another query with an expression for each category. LS: IIf([LifeSafetyCode]>0,1,0) Then sum these up.

Below is the form where each project is scored.

1609028380146.png


The form's record source is a query called, AllScoreSheet_Q.

1609028561548.png



So the question is, how do I create a column chart?
I realize I do not have a field for Categories, which would've helped but, even if I did, I can't see how that would have integrated into my form. ??
Can anyone lend a hand?
I have feeling I have not set this up right but, I can't figure out how to proceed.
Thanks.
 

plog

Banishment Pending
Local time
Yesterday, 20:20
Joined
May 11, 2011
Messages
11,613
What you effectively did was store data that should be in a field as the name of fields. Youve done this in just about all your tables that you showed, but let's just use the 5 fields you highlighted with yellow as an example of how to fix this. You do that by making a new table, let's call it "ProjectScores", it would be structured like this:

ProjectScores
ps_ID, autonumber, primary key
ps_ProjectID, number, foreign key to Project_T.ProjectID
ps_Category, text, this will hold what you have as field names now (Life Safety Code, Regulatory Accredditation, etc.)
ps_Score, number, the actual value that is now in each of those 5 fields.

That table will now hold all the scores, 1 in each row. That means instead of those 5 existing fields, to input 5 scores you would add 5 records to ProjectScores.

The same issue exists in FY_Funding_T and possibly Weight_T. Looking at your form, whatever the Additives section is based on does as well.

When building forms you use subforms when you encounter a 1-many relationship in your table structure. So, the main form would be based on Projects_T and then you would have a subform to interact with data in any ohter table that is on the many side of a 1-many with Projects (e.g. ProjectScores)


As for a chart, check out this link:

 

Weekleyba

Registered User.
Local time
Yesterday, 20:20
Joined
Oct 10, 2013
Messages
586
Thanks Plog. I will chew on this and put another table together.
For some reason this concept keeps eluding me.
 

Weekleyba

Registered User.
Local time
Yesterday, 20:20
Joined
Oct 10, 2013
Messages
586
Plog,
Here's where I get a bit lost on how this will work.
I've created another table called ProjectScores_T as you suggested.
1609035678562.png


Then I created a subform called Category_F with ProjectScores_T as the record source.
At this point, I get a bit confused on what to do.
I made the subform, Category_F, continuous, with a combobox for the Category. I had to create another table called Categories_T, for the row source of the combobox.

Is this what you had in mind?
I don't like the combobox for this, but not sure how else to generate the 5 records.
How would you set this up?
Thanks for your help. I feel I'm getting close to what you were saying.

1609035910625.png
 

plog

Banishment Pending
Local time
Yesterday, 20:20
Joined
May 11, 2011
Messages
11,613
Yes that's close. Since you built another table for the category ps_Category should not be text but numeric since your storing an ID in there.

As for the subform, you can build an APPEND query that automatically creates those 5 records for a project when a project is created. That way no combo box is needed, each category is populated and the user just types in the correct value next to each. Further, you can style your subform such that it looks exactly as you had it when it wasn't a subform. It will take some googling and experimenting but you can get it to look just as you had it.
 

Weekleyba

Registered User.
Local time
Yesterday, 20:20
Joined
Oct 10, 2013
Messages
586
So cool!
I'll work on this tomorrow and let you know how it turns out.
Thanks for the hand!
 

Weekleyba

Registered User.
Local time
Yesterday, 20:20
Joined
Oct 10, 2013
Messages
586
Plog,
I guess I need some additional assistance.

I created the append query, that was easy. And changed my combo box, back to a textbox on the subform, Category_F.
1609092240898.png

I'm using this code to run the append query, although not sure what event to put in on.
Code:
DoCmd.SetWarnings False

DoCmd.OpenQuery "APPEND_Cat_Q"
DoCmd.Close acQuery, "APPEND_Cat_Q"

DoCmd.SetWarnings True

So now, I have a lot of questions.
I first need to create a project (the parent form) prior to running the append query for the subform, Category_F (the child form). Correct?
What event do I put the code on? Would the event go on the parent form or child form? I'm thinking the parent form, after update but not sure.
I tried putting this code on a command button in the Category_F, but no luck. It would append the data but, it does not associate it with the link, so it doesn't show up in the subform. I really don't want a button anyway, but just trying to get this to work.
I'm sure I'll also have to deal with duplicates. Not sure how to do that either.

Can you lend a hand again?
Thanks.

1609093484458.png

1609093540900.png
 

plog

Banishment Pending
Local time
Yesterday, 20:20
Joined
May 11, 2011
Messages
11,613
Yes you need to create a Project prior to running the APPEND because you need to populate ps_ProjectID in ProjectScores_T. So, the query you created is not complete--your just adding 5 unassigned records to ProjectScores_T. Part of the append must inclued a value for ps_ProjectID. It may be easier to not have an actual query object for the APPEND but do it all through VBA using the Docmd.RunSQL method:


You can do it on the AfterInsert (https://docs.microsoft.com/en-us/office/vba/api/access.form.afterinsert(even)). Or you could make the user click a button which would run the append query VBA and then refresh the subform and have those 5 records appear.
 

Weekleyba

Registered User.
Local time
Yesterday, 20:20
Joined
Oct 10, 2013
Messages
586
I think I have it. How does this look?

Code:
Private Sub Form_AfterInsert()
'This generates the Categories in the subform Category_F
Dim SQL As String

DoCmd.SetWarnings False

SQL = "INSERT INTO ProjectScores_T (ps_Category, ps_ProjectID) " _
& "SELECT Categories_T.Categories, Project_T.ProjectID " _
& "FROM Categories_T,Project_T " _
& "WHERE(((Project_T.ProjectID)=[Forms]![ProjectScoreSheet_F]![ProjectID]))"

DoCmd.RunSQL SQL
DoCmd.Requery

DoCmd.SetWarnings True

End Sub

Now I have to do the same for the other groups in the form.
Now..... I can produce some charts, I believe.
I guess normalization is a good thing, but it is a bit more work!
 

Users who are viewing this thread

Top Bottom