One of my fields isn't joined correctly

[#1]

The setup:

I have three tables: Projects, PurchaseOrders and ChangeOrders.

http://i46.tinypic.com/qrlma8.jpg

Within Projects, I have ProjectNumber and CurrentPM.  CurrentPM (project manager) contains values like PM 1, PM 2, ... etc.

Within ChangeOrders, I have ProjectNumber and CONo (the change orders number).

What I want:

I want to get a distinct count of CONo (Count (DISTINCT CONo)) and display them among CurrentPM.

The problem:  It doesn't seem like the tables are joined correctly.  I am not getting a count of CONo among different PM's, rather I'm only getting a TOTAL count at the end.

http://i49.tinypic.com/ld6yf.jpg

PONumber seems to be working correctly.

What am I doing wrong?

[nagaiank · Accepted solution]

Try the following:

1. Get the count of CONo from the ChangeOrders table

Temp1:

load ProjectNumber, Count(CONo) as CONoCount

resident ChangeOrders group by ProjectNumber;

2. Add the CONoCount field to Projects table by left join

Left Join (Porjects) load * resident Temp1;

Drop table Temp1;

You can now display ProjectNumber, CurrentProjectNumber and CONOCount in a table.

[#3]

Thank you.  Exactly what I wanted.