Linking

[cheeser89]

I have created 2 forms which both link together. I have a table in the one form which creates a grade for 2 tests and the grade totals the averag grade between both. ONly thing is that I am trying to make it so one grade as per year, so when I add another grade for the same course, it doesnt add it in the current grade.

[June7]

Do you need to show the totals on form? Reports are best for this. Either way need to either filter the form/report recordset to exclude the old records or build expression in textbox that adds only the latest for each course. The filter option probably easier.

[cheeser89]

No, each course is made up of 2 assignments, worth 50% each, so when I add a score for both assignments for the year 2011, the grade = ( whatever the scores are ), but if i enter a score for 2012, it adds onto the 2 assignments making the grade double. I need to be able to have the grades differentiate depending on the year. I have a query that generates the grade from the score and the percent of grade. Im thinking add the year able into the query somehow to make it so depending on year, thats what the grade is.

[June7]

Do you want to show score for all years or just the latest year for each student and course? I presume the scores will not be mixed accross years for each course.

Sum scores by grouping on student, course, and year.

Provide sample data for analysis if you need more help.

[cheeser89]

How would I do that? In a query? ( the Sum ) , and ya on the one form you choose which employee from a drop down, and in the subform shows course title year, grade, qualified, ( Grade being calculated using Percent of grade, max points. ) but when I enter in on the other form course info for either assignments ( 2 assignments make up a course), and when i choose 2012 instead of 2011 ( just testing ), the grades for 2012 are calculated into 2011, and I get duplicate entries, 2 for 2011 and 2 for 2012, when I should only have 1 each. I am ready to rip my hair out

[cheeser89]

OR could i add that in the current query sum function. here is the function:

Grade(%): Sum([Results].[Score]/[Module].[MaximumPoints]*[Module].[PercentofGrade])

( modules meaning assignments). could I somehow add the year in there?

[June7]

Suggest using the report's Grouping & Sorting with aggregate calcs functionality. Access Help has guidelines on building reports with G&S and calcs.

If you want to provide project without confidential data, will look at.

[cheeser89]

unfortunatley, it needs to be done within a form, not a report.

[June7]

Very unfortunate. Aggregate calcs on form is always tricky. I don't have enough info on your project to walk you through. Again, if you can provide your database, don't need real data, will look at.

[cheeser89]

It contains confidental data , Am I able to add a Agg Calc or Sum func TO the query i already have posted above. I basically just need it to say, take the grade(%) from each module completed by each employee based on the year it was completed.

[June7]

You could make copy, remove confidential data, leave enough dummy records to test, run Compact & Repair.

As I said, this is not so easy on forms. Why is it necessary to show on form? Do you want the form to still be editable (data entry/edit)? I need to know data structure and form setup to advise. Flying blind.

[cheeser89]

Yes it needs to be able to be edited/data entry. What exactly do you need to know as per the set up of the form? I am new to access so bare with me as for the terms .

I am heading home for the day, as I am at work. I will attach the dummy file tomorrow. Thank you so much for your help.

[cheeser89]

The dummy file, if I attach , is basically a read only file.. I can answer any questions about the form thoroughly, as well as take screen shots

[June7]

Why would it be a read only file? I am not able to help further for 2 weeks (vacation).

[cheeser89]

I am not sure. Do you know which function I could use to input into my query?