Using a Query as a field control source

[tjones]

I have a field that I need a calculation in. All records are based on the students 790#

All fields are from the CourseTaken table. the Units and TotalCreditEarned (are numeric set) but each 790# can have multiple courses, and I need to total the units per 790# and "completed" status (another field).

how do I get the results to show in the TotalCreditsEarned field on the form? or am i going about getting the calculation wrong .

I have built a query that seems to work: but don't know how to make it attach to the field to show the sum.
SELECT Sum(CourseTaken.Units) AS SumOfTotalCreditsEarned, CourseTaken.[790ID], CourseTaken.CourseStatus
FROM CourseTaken
GROUP BY CourseTaken.[790ID], CourseTaken.CourseStatus
HAVING (((CourseTaken.CourseStatus)="Completed"));

I also need to divide the total units by courses passed ( A=4, B=3, C=2, D=1) for a GPA.

 

[Bryan]

Is your form's record source the query? If so, just add the field from the query just as you would add a field from a table.

 

[tjones]

No the form's record source is the CourseTaken table. I just need that field to either use the query to calculate the information or figure out how to make the field do so within the form/table itself.

 

[Bryan]

If I read the query properly, you are totaling the Units for each 790ID. There probably isn't any reason to group by the CourseStatus since you're only considering the Completed CourseStatus.

If you are trying to build a form that simply shows the 790ID and the totals, say, to scroll through them, then set the form's record source to the query. This is really the sort of thing that should go into a report, though. If you're trying to do something more, then you're going to have to explain what you're trying to accomplish.

 

[tjones]

I have included a graphic of the CourseTaken form. It records several bits of information (and is also used for advising purposes) so there are several "CourseStatus" available. Whit i would like to do (but am unsure how) is take ONLY the courses that are completed and total them so you know at a glance how many credits (units) are completed.
From there I need to divide the total credits by grade (A-4, B-3, C-2, D-1) to get the GPA.
I figure there is a way to do so in the table/form field using a total() or sum() but have not figured it out which is why i went with a query.

 

[tjones]

I'm thinking I may need to remove the fields from the table and then add them to the form as an 'unbound' field with the control source being the query.

 

[boblarson]

I'm thinking I may need to remove the fields from the table and then add them to the form as an 'unbound' field with the control source being the query.

That statement, right there, shows you do not understand the concept of unbound. If a control has ANYTHING in its control source, it is bound, not unbound.

What it appears to me is that you either need to use a subform for displaying the value, or use the domain aggregate DSUM to calculate the total for the currently displayed records.

And just to note - the value will ONLY be displayed and should only be displayed and not stored.

 

[tjones]

Your right I have no idea, I am still attempting to teach myself (painfully) how access works.

That domain aggregate, how does it work. If I can base it to only display that works as I discoverd having it in the table made it show for every record which is not necessary.

 

[boblarson]

Your right I have no idea, I am still attempting to teach myself (painfully) how access works.

That domain aggregate, how does it work. If I can base it to only display that works as I discoverd having it in the table made it show for every record which is not necessary.

If you want to use the DSum, you can do so like this:

=DSum("Units", "CourseTaken", "CourseStatus=" & Chr(34) & "Completed" & Chr(34))

 

[tjones]

So remove from the table and remove the control source from the field. Then where do I put the DSum statement?

Sorry i'm not getting this. The book I have (Access 2010 In Depth) doesn't seem to cover this type of thing or I have not found it.

 

[boblarson]

The code I gave you goes into the control source of the text box on the form.

 

[Bryan]

If you have calculated fields in your table, then yes, you should remove them as it is not proper to store the results of calculations in a table.

The code that Bob gave you is the essentially equivalent of a SQL query in VBA form so that you can use it as the Control Source for a field on your form.

Look HERE for DSUM info.

 

[tjones]

Thank you boblarson. The calculation worked. Even though the field still shows on the split form it is not stored in the table and I can live with that (unless there is some way to make it not show except in the form section). Bryan thank you for the link to more info, I will read more on the DSUM before attempting to do the GPA field! The SQL and VBA parts really have me tearing my hair out.