sum units via query

[tjones]

I need to separate the units into type and then program total. this will allow me to know which type of course is missing at a glance.

I have four unbound field ReqUnit, CoreUnit, ContentUnit, TotalUnit for query input.

Drawing from these field.

Field 1 = cboCourseType (Required, Core, Content)
Field 2 = cboCourseStatus (Completed)
Field 3 = Units (number field)
Field 4 = StudentID (so it only searches records for that student)

Using the Required field as an example

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

ReqUnit field Control Source =
=Nz(DLookUp("[TotalCreditsReq]","qryUnitTotalRequired","[790ID]='" & [790ID] & "'"),"")

But when I make a record I only get #ERROR

 

[Alansidman]

Perhaps you should post your db, with some sample data for examination. Make sure that you dummy up all confidential data and run a compact and repair before uploading.

 

[tjones]

Ok I have pared down the database to just the relevant main (basic) and relevant form (Course).

If you will notice that when I set the query for "Required - Completed" it give and #Error in the requiredunit and totalunit field.

But if i entered say "Core-CoreCompleted" i get a the correct units for the CoreUnit field. but doing it seperate i can not get total. :banghead:

Thanks for the help it is really appreciated. View attachment GPHDatabaseTESTsm.accdb

 

[boblarson]

Simple really. Your query of qryUnitTotalRequired needs to have the CourseTypeID field's criteria changed to the number 1 instead of the text value you currently have.

 

[tjones]

Thank you so much. I even had that down to try in my notes and just missed it, thinking that would not make so much difference. Ah well, still learning.

Again I can't thank you enough for all the help you and everyone on here has given me.

 

[boblarson]

Oh, by the way, your form is looking sharp. Good job.

 

[tjones]

Thanks. Due to a lot of hard work, research to find best methods and how to implement them, and definitely a lot of help from the terrific people on here.

 

[tjones]

Bob,

Hoping you can help me with this small glich.

I have added another type of course "Non-Degree" with a designation of 4. The problem arises only in the Total Unit query.

Since I have put this in as "completed" it adds the course to the total.

SELECT Sum(tblCourseTaken.Units) AS TotalCredits, tblCourseTaken.[790ID], tblCourseTaken.CourseTypeID, tblCourseTaken.CourseTypeID
FROM tblCourseTaken
GROUP BY tblCourseTaken.[790ID], tblCourseTaken.CourseTypeID, tblCourseTaken.CourseStatus, tblCourseTaken.CourseTypeID
HAVING (((tblCourseTaken.CourseStatus)="Completed") AND ((tblCourseTaken.CourseTypeID)=1 And (tblCourseTaken.CourseTypeID)=2));


It does not give me an error it just does not display the total unit that have type 1&2 with status completed.

the clarification is located here: http://www.access-programmers.co.uk/forums/showthread.php?t=238035

with the 2 that work and the one attempting to use those to to get overall total.

 

[tjones]

FIXED!

here is the solution

SELECT Sum(tblCourseTaken.Units) AS TotalCredits, tblCourseTaken.[790ID], tblCourseTaken.CourseStatus
FROM tblCourseTaken
WHERE (((tblCourseTaken.CourseTypeID)=1 Or (tblCourseTaken.CourseTypeID)=2))
GROUP BY tblCourseTaken.[790ID], tblCourseTaken.CourseStatus
HAVING (((tblCourseTaken.CourseStatus)="Completed"));