Solved Calculating Overall Grades (1 Viewer)

[axiri]

Hi Everyone,

I am on a basic level with Access but have a good understanding of tables, queries, forms, etc.
My weakness is not knowing where to begin when trying to calculate.

So I already have an EnrollmentTable that has the participant's info and the modules that they took along with the max points and the actual points they received. But I would like to get the total points of all modules by Core. Maybe the picture below can help to understand what I would like.
Currently, I am stuck with not knowing if to do another query, or to use DSUM, or maybe some other way I don't know. I successfully ran a query BUT it calculates ALL modules together (so Core 1 & 2) which I will need eventually but for now, I need to have them separate.

Below is more info on my tables and relationships:

My final goal will be to eventually create a report like this for each participant:

Any insight and help would be greatly appreciated.

 

[bastanu]

You don't need a dSum. To do it in a query create a totals query in which you group by ParticipantID and CoreID and you sum ModMaxPoints and Grade. But to do your report you will not even need that as it is done in the report itself if you add a report group for CoreID and in its header you sum the grade and modmaxpoints.

Cheers,

 

[axiri]

You don't need a dSum. To do it in a query create a totals query in which you group by ParticipantID and CoreID and you sum ModMaxPoints and Grade. But to do your report you will not even need that as it is done in the report itself if you add a report group for CoreID and in its header you sum the grade and modmaxpoints.

Cheers,

Oh my goodness! you made it sound so simple! I don't know why I didn't even consider SUM with ModMaxPoints.
Thank you so much!
It is exactly what I was thinking of.

But for the report part, I got confused. I tried but I would need the report to be more of a transcript of record for each participant. Similar to what is below. So now that I can group the scores per Core now I need to do it for every participant.

Thanks so much for your help! I will continue to play with it some more

 

[bastanu]

In reports you do what you want by using groups. You would need a Group for the Participant with a Header where you add the top part specific to each individual. Then you add a group for each core and in its header you add a text box for the Core name and a textbox to hold the grades. For that you can use =Sum([ModMaxPoints & "/" & Sum([Grade]) which are the corresponding textboxes in the Detail of the report.
Cheers,

 

[axiri]

In reports you do what you want by using groups. You would need a Group for the Participant with a Header where you add the top part specific to each individual. Then you add a group for each core and in its header you add a text box for the Core name and a textbox to hold the grades. For that you can use =Sum([ModMaxPoints & "/" & Sum([Grade]) which are the corresponding textboxes in the Detail of the report.
Cheers,

ok, that made more sense!
But now I have the problem where on both core groups it repeats the same core Instead of listing Core 1 then in the second group Core 2.
How can I keep the group for each core to not repeat itself?

 

[axiri]

ok, that made more sense!
But now I have the problem where on both core groups it repeats the same core Instead of listing Core 1 then in the second group Core 2.
How can I keep the group for each core to not repeat itself?

OMG, I don't know how but I got it to do it!

Now I am almost done but need a suggestion.

So I have one more field I need to incorporate which is "Title: Title of PMP"

I tried adding it under the field "ModuleName" but it then "Title" shows up after every module, but for this situation only one module has a "Title" this is kinda like the title to a bachelor/masters thesis for example.

Any ideas on how I can incoporate this field? I tried adding another group but that group header gets added directly under CoreName Header.

 

[bastanu]

Not sure I get it, is Title associated with the participant or a Core. If it is with the participant as I think you're saying and you want it at the bottom of the report you can use the report footer which will not repeat. Or add group for it and in design view click the Group And Sort and use the buttons on the right to move the new group up until is between the participant and CoreID. Now change it so it only has a footer and add the PMP fields there.

Cheers,
Vlad

 

[axiri]

Not sure I get it, is Title associated with the participant or a Core. If it is with the participant as I think you're saying and you want it at the bottom of the report you can use the report footer which will not repeat. Or add group for it and in design view click the Group And Sort and use the buttons on the right to move the new group up until is between the participant and CoreID. Now change it so it only has a footer and add the PMP fields there.
View attachment 90704
Cheers,
Vlad

man you are on a roll! yes each participant wrote a PMP which is unique to each participant.
that worked! but now the Title isn't showing. The label shows but not the actual info from the field.

I chcked to make sure the control source was the TitleofPMP source which it matched up but still not showing the full title.

 

[bastanu]

Does it show in the report's record source?

 

[axiri]

Does it show in the report's record source?

Yes I just checked again and it is also in the report's record source.

 

[bastanu]

Can you show the report in design view please (or better if you could create a small db sample with the table and report, no sensitive data please)?

 

[axiri]

Can you show the report in design view please (or better if you could create a small db sample with the table and report, no sensitive data please)?

ok so i tried to upload a sample but I keep getting the message "The uploaded file is too large." So here is a screenshot of the report in design view.

But also check this out:
Here the "Title:" is still not showing

BUT when I deleted all except the Personal Mastery Project module, now the Title shows up.
I deleted the module records from a Table called Enrollment. This table is where I have what participant took which module and the grade they got.

Before I delete the records:

After I delete the records:

 

[bastanu]

Now it becomes easier to understand the issue... Because you only have it in one module (ModuleID=1) you need to force the report to display that one and not any of the others. There are a couple of ways to do that but probably the easiest is to open the report in design view, click on the builder (the three dots) on the right of the record source line in the Properties to open the query the report is based on in design view. Now change the query to a totals query (by clicking the sigma button on the ribbon), leave in the Totals row Group By for all the fields but TitleOfPMP where you change it to Max (because all others are empty the PMP one will show). Now in the control on the report change the control source to MaxOfTitleOfPMP.

For future reference you could try to run a Compact and Repair on the file in Access then zip it and should become small enough to be uploaded to this forum.

Cheers,
Vlad

 

[axiri]

Now it becomes easier to understand the issue... Because you only have it in one module (ModuleID=1) you need to force the report to display that one and not any of the others. There are a couple of ways to do that but probably the easiest is to open the report in design view, click on the builder (the three dots) on the right of the record source line in the Properties to open the query the report is based on in design view. Now change the query to a totals query (by clicking the sigma button on the ribbon), leave in the Totals row Group By for all the fields but TitleOfPMP where you change it to Max (because all others are empty the PMP one will show). Now in the control on the report change the control source to MaxOfTitleOfPMP.

For future reference you could try to run a Compact and Repair on the file in Access then zip it and should become small enough to be uploaded to this forum.

Cheers,
Vlad

Ok so still no luck.
But I was able to Compact & repair.

 

[bastanu]

Here you go, created another query to get the title and used that in the report. Another option would have been using a dLookup in the control source (dlookup=("TitleOfPMP","Enrollment","[ParticipantID]=" & [ParticipantId] & " And [ModuleID]=1")

Cheers,
Vlad

 

[axiri]

Here you go, created another query to get the title and used that in the report. Another option would have been using a dLookup in the control source (dlookup=("TitleOfPMP","Enrollment","[ParticipantID]=" & [ParticipantId] & " And [ModuleID]=1")

Cheers,
Vlad

Finished!
Thank you so much Vlad! If I could, I would send you a coffee, beer, chocolate, etc. But a virtual high five will have to do!

 

[bastanu]

That is great news, I'm glad to hear




Cheers,