Missing Data in Multipart Query

[die_laffin]

Hi,
I have created 2 queries qryPrivate & qryPublic that give the desired results
when run individually but I cannot manage to combine them into a 3rd query to
base the report on, when I try I am only given the results that occur in the
same Divisions.

eg:
Division Private Cost

Newcastle Yes 100
Newcastle No 20
Sunderland Yes 50

When the combined query is run I am only given:

Division Private Public Total Cost

Newcastle 100 20 120


The query does not show the results for Sunderland.

Any Help would be appreciated as this is driving me mad & I only said I would
do this to help a friend out!
PS. I hope my description makes sense

 

[die_laffin]

Hi

(missed off half of my description - heres the full thing...)

I have inherited a database from which I need to produce a report.

There are 3 tables: Divisions, Main & Year

Divisions is linked to Main through a "Division" field.
Main also has a "Cost" field and a Yes/No field called "Private".

What I need to do is be able to produce a report that will check the
"Private" field and add the value of the "Cost" field to one of two columns
depending on the "Private" yes/no fields value. I also then need a third
column in the report that totals the "Cost field.

I have created 2 queries qryPrivate & qryPublic that give the desired results
when run individually but I cannot manage to combine them into a 3rd query to
base the report on, when I try I am only given the results that occur in the
same Divisions.

eg:
Division Private Cost

Newcastle Yes 100
Newcastle No 20
Sunderland Yes 50

When the combined query is run I am only given:

Division Private Public Total Cost
Newcastle 100 20 120

The query does not show the results of Sunderland.

Any Help would be appreciated as this is driving me mad & I only said I would
do this to help a friend out!
I hope my description makes sense

 

[Guest]

based on the example try this query i used a table as the input but it can
just as easy be a query

Division Private Cost

Newcastle Yes 100
Newcastle No 20
Sunderland Yes 50

TRANSFORM Sum(Team.Cost) AS SumOfCost
SELECT Team.Division, Sum(Team.Cost) AS Total
FROM Team
GROUP BY Team.Division
PIVOT Team.Private;