Hard Problem (long, sorry) Sum totals = too much

[Access_guy49]

I have a database i created for our education department. They need a bunch of reports created, most of them work fine with the exception of this one report for school programs. The idea is this.

A school can purchase a school program from us (Conservation Authority)
Our educators teach the kids about nature, yada yada yada...

A school can sign up for multiple programs in a day, but when they collect information like the number of kids attending and the revenu generated they only calculate it on the day and not on each individual program. So i have a many to many relationship. One table being the School Program Event table, and the other table being the table to hold the information on the program they choose.

I need to report showing each School, Each day, and give the totals. But the problem is that the sum finctions will double count.

if school A purchased 2 programs and had 50 kids. My report will show for school A that there was 100 kids that attended. I'm sure this is because of the relationships. BUT, here is the kicker, I need to show in the report that the school purchased the two programs of choice, I can't just say they attended our programs and paid x dollars.
I have included pictures that I hope will help.
My relationship picture shows where i think the problem is outlines in red. and the fields i pull are in blue.
I also have a picture of my design view of the report and of the final product.

PLEASE HELP!! i've been stuck on what to do with this for 2 days now.

 

[Alisa]

I have had that happen to me before. Is your report based on a query? It might help you trace your problem if you make a select query that shows the correct records (i.e., no doubles) and then base your totals query on that. You might need to set unique records = true.

 

[Access_guy49]

Unfortunatly it is based on a query. But the kicker here is that I don't actually want unique records because If there are 2 programs (both with the same name) in a given day, I want that to show up twice. I think it's just a flaw in the logical design of the database.

 

[Alisa]

Unfortunatly it is based on a query. But the kicker here is that I don't actually want unique records because If there are 2 programs (both with the same name) in a given day, I want that to show up twice. I think it's just a flaw in the logical design of the database.

Did you try it? From your explanation of the problem, you DO want unique records. If 2 programs show up in the same day, that would still be 2 unique records, assuming you have a key field. If you do not have a key field, then you are out of luck.