sql - Complex sorting in access -

i trying pull out course funding information database, using ms access. funding attached each student in db. number of students on more 1 course. funding attached student, causing displayed against several courses, totals high. funding needs shown against main course student on.

i have hirachy of course codes (the opening digit tells sort of code is). need able specify, example, when student on 'n*' code course , 'm*' course, funding should come through against m course. there 7 different combinations of course codes.

at moment have 2 queries. first pulls out: student id, funding, course code. second counts student id, , sums funding, grouping course code.

how , should go implementing sql required stop funding being duplicated?

thanks

query 1 (efa funding base query):

select        [learner id],        [learner funding],        [course code]           learner           inner join learningdelivery ld             on learner.learner_id = ld.learner_id             inner join providerspecdeliverymonitoring psdm                 on ld.learningdelivery_id = psdm.learningdelivery_id               [course code] not "c*"        , [course code] not "f*"; 

query two:

select        cstr([course_code]) c_code,        count([efa funding base query].lrndv_onprogpayment_efa) countoflrndv_onprogpayment_efa,        sum([efa funding base query].lrndv_onprogpayment_efa) sumoflrndv_onprogpayment_efa           [efa funding base query]    group        cstr([course_code]); 

what i'm getting @ moment:

student x, y , z have £5000 of funding attached them.

x & y courses m1, n2 , n3, z on n2

at moment, then, first query(below) return:

x 5000 m1 x 5000 n2 x 5000 n3 y 5000 m1 y 5000 n2 y 5000 n3 z 5000 n2 

my second query total returning count of students , sum of funding, thus:

m1, 2, 10000 n2, 3, 15000 n3, 2, 10000 

however, there atually 15000 of funding coming school. in hirachy of courses, m higher. if student on m course, funding should appear next it. if on n course, funding should against one. should return is:

m1, 2, 10000 n2, 3, 5000 n3, 2, 0 

note, there 4 different course codes in reality, , students can on variety of different combinations of courses, different rules course funding should display against. basically, what's best way of implementing these rules?

thanks