Access query

[GCS]

I am a novice and trying to design a database to track items for a benefit auction for our Christian school and calculate rewards for classes based on what donations they bring in.



I have an ITEMS PER STUDENT QUERY that totals the estimated value of each item brought in by each student. [SumOfEst_Value]

I have a SPONS/ADV PER STUDENT QUERY that totals the money brought in by student through the cost of sponsorship and advertising. [SumOfCost]

I need to add these two figures together [ITEMS PER STUDENT QUERY]![SumOfEst_Value] and [SPONS/ADV PER STUDENT QUERY]![SumOfCost] to give a total item value and spons/adv money brought in by each student. [DONATION_TOTAL]

I was able to write the query, but the only figure showing in the [DONATION TOTAL] is for a student that had a figure for the [SumOfEst_Value] and [SumOfCost]. Most students will only have a [SumOfEst_Value]. How do I get the query to display the [SumOfEst_Value] in the [DONATION TOTAL] column for students that don't have a [SumOfCost] but also add the [SumOfEst_Value] and [SumOfCost] for those students that have both?

[GCS]

I should have included that I don't know SQL and am trying to do this through query design.

[RayMilhon]

I'm going to make a couple of assumptions here. Each Query has a Student ID or name. The 2 queries are linked on the student id or name and it's an Inner join. What that does is limit your data to Student's that exist in both queries which is the result you're getting. Assuming Query 1 is the query with the SumofEst_Value and Query 2 is the query with the SumofCost Value use a left outer join from query 1 to query 2.

[GCS]

Thanks for your help, but I was able to figure it out by adding Nz to my expression which changed any null values in either queries to a 0 which fixed the problem. I appreciate your time. Thank you!