Total Credit not calculating

[tjones]

I have a form (UnitTotal) as a subform on the main form (CourseTaken) which is run from a query (UnitTotalAll).

The UnitTotalAll query is has 2 fields from the main form (frmCourse Taken /Units & Status) and 4 queries to calculate the different units tied to a specific status (3 units - Completed-Required status) etc.

qryUnitTotalReq:
SELECT Sum(tblCourseTaken.Units) AS TotalCreditsReq, tblCourseTaken.[790ID]
FROM tblCourseTaken
GROUP BY tblCourseTaken.[790ID], tblCourseTaken.CourseStatus
HAVING (((tblCourseTaken.CourseStatus)="Completed-Required"));

qryUnitTotalCore:
SELECT Sum(tblCourseTaken.Units) AS TotalCreditsCore, tblCourseTaken.[790ID]
FROM tblCourseTaken
GROUP BY tblCourseTaken.[790ID], tblCourseTaken.CourseStatus
HAVING (((tblCourseTaken.CourseStatus)="Completed-Core"));

qryUnitTotalContent:
SELECT Sum(tblCourseTaken.Units) AS TotalCreditsCont, tblCourseTaken.[790ID]
FROM tblCourseTaken
GROUP BY tblCourseTaken.[790ID], tblCourseTaken.CourseStatus
HAVING (((tblCourseTaken.CourseStatus)="Completed-Content"));

qryUnitTotal:
SELECT Sum(tblCourseTaken.Units) AS TotalCredits, tblCourseTaken.[790ID]
FROM tblCourseTaken
GROUP BY tblCourseTaken.[790ID], tblCourseTaken.CourseStatus
HAVING (((tblCourseTaken.CourseStatus)="Completed-Required" And (tblCourseTaken.CourseStatus)="Completed-Core" And (tblCourseTaken.CourseStatus)="Completed-Content"));

When you run the individual queries 3 work (required, core, content) but the query for total does not.

The other problem is that the subform UnitTotal is not displaying on the main form it shows in design view but not form view or layout view. Any suggestions?

 

[plog]

HAVING (((tblCourseTaken.CourseStatus)="Completed-Required" And (tblCourseTaken.CourseStatus)="Completed-Core" And (tblCourseTaken.CourseStatus)="Completed-Content"));

You most likely need to replace the 'And's with 'OR's. That field can only have one value but you have told it that it needs to be all those values at the same time.

And because I hate unneeded queries, I would use this one query to replace them all:

SELECT SUM(iif(CourseStatus="Completed-Required", Units,0)) AS TotalCreditsReq, SUM(iif(CourseStatus="Completed-Core", Units,0)) AS TotalCreditsCore, SUM(iif(CourseStatus="Completed-Content", Units,0)) AS TotalCreditsReq, SUM(Units) AS TotalCredits, [790ID]
FROM tblCourseTaken
GROUP BY [790ID]
HAVING (((tblCourseTaken.CourseStatus)="Completed-Required" OR (tblCourseTaken.CourseStatus)="Completed-Core" OR (tblCourseTaken.CourseStatus)="Completed-Content"));

 

[pbaldy]

A status can not have more than one value, I suspect. You want OR instead of AND.

 

[tjones]

Thanks!! the subform is displaying now and the req, core, content are correct, but the total is not calculating the three correctly. it is just showing the total for content.

What i need it to do is total the previous three status/credits.

 

[pbaldy]

It's because you're grouping on status. I'd probably just have the last query that displays all 3, and add a sum in the subform footer.

 

[tjones]

I have not added a footer to a from before, is it the same as adding one to a report?

=Count(*) as the control source?

 

[pbaldy]

Presumably:

=Sum(TotalCredits)

 

[tjones]

Another problem I encountered when deleting the information and re-entering from scratch. The total footer field displays. but the other three (query) fields do not display until there is an entry for all three. though the information is saving. and I still can not get the unit total to work. I have included the database.
View attachment GPH Minor Student Database(credit).accdb

 

[tjones]

Finally managed to get the total to work. Now the only problem is that the first three fields will not display unless all three (status') have an entry though the information is saving.:banghead:

 

[plog]

Wow, someone read up on normalization and went way overboard.

First, most of those course tables should be in 1 table. Name shouldn't be seperate from description which shouldn't be seperate from course number, which probably should be your primary key instead of an artificial autonumber.

Also, tblYesNoChoice is worthless, you should use a checkbox field whenever you want to obtain this type of data

For your problem: You are using this:

=Sum([TotalCredits])

However that form is based on the query qryUnitTotalAll which doesn't have a field called TotalCredits which means you are trying to sum a field that doesn't exist.

My suggestion would be to scrap the sub-form idea, move these boxes to the actual form and use Dlookups to get the values.

 

[tjones]

I set the course tables up as a cascading combo boxes. That was the way I had read the instructions to do that.

The reason I used a subform is that it was the only way i knew to set a query as the source instead of adding the fields to the table. I am willing to add the fields to the table if i can get them to work but have no idea how to use Dlookup.

 

[plog]

You don't add them to the table, you can use a query as your source for the DLOOKUP. Here's a link: http://www.everythingaccess.com/tutorials.asp?ID=Getting-a-value-from-a-table%3A-DLookup()

 

[tjones]

That link just takes me to the main page with 203 entries. Where do I go exactly?

Like Queries /.......

 

[plog]

Sorry. I googled it and copied the url of the page I was on and it was different than what I got when I clicked on it.

Try this link:

http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[tjones]

Sorry to be such a pain, but this is totally over my head. I understand the verbs but the nouns escape me.

Basics:
1. Create an unbound text field on the CourseTaken form (UnitTotalReq)
2. in After Update of the CourseStatus field add:
Private Sub CourseStatus_AfterUpdate()
'get the total credits for required courses
=DLOOKUP (UnitTotalReq,qryUnitTotalRequired, CourseStatus=Completed-Required)
End Sub

Which is not working.

 

[plog]

Your DLOOKUP needs to use quote marks to tell Access which parts are fields and which parts are actual data you typed. You also need to connect them by ID. Try this for your Dlookup:

=DLOOKUP ("UnitTotalReq","qryUnitTotalRequired", "[CourseStatus]='Completed-Required' AND [790ID]=" & [790ID])

 

[tjones]

I had added the quotes, but copied yours exactly and still get this error.

 

[plog]

You need to set the left side of the equals sign to the name of the text box on the form

 

[tjones]

If i'm ready to scream I can imagine how you feel trying to help.

I still cant get it to work so made this graphic hoping you can see what I am doing wrong.

Again thanks for all your help.

 

[plog]

I think your close. You've referenced the text boxes, but you also need to reference the form they are on. Put 'Me!' in front of all of those field names like this:

Me!UnitTotalReq =

Me!UnitTotalCore =

Me!UnitTotalCont =

Me!UnitTotal =

Me! just tells Access to use the current form being viewed.