Problem with inner joins

[jdraw]

I have reorganized your relationships based on the db provided.

I notice that Orders is not related to anything? Same for Materials/Orders?
Purchasing 2, 3, 4a and 5 are independent from rest of model and Purchasing 3a and 4?

Some Orders have NULL OrderedBy, some have no Contact?

 

[ChrisSedgwick]

This could have been when I've needed to delete some of the data in order to send it!

Again, the set up of the database is poor and not something I've ever been involved with. It works for 95% of what we do, however in the near future could do with some major work.

However, it seems that the structure now poses a problem when trying to create this "simple" (manager's words) report.

 

[namliam]

Apologies, this is what it looks like now. I've read your previous message back...

Not quite, you are in particular intrested in the "in progress" part... instead of the Done part... Plus you are missing the Group by and sum parts....

SELECT Phase.[Phase ID]
     , Phase.Jobno
     , (IIf([Product].[% Done]=100,1,0)) AS Done
     , (IIf([Product].[% Done]=100,0,1)) AS InProgress
FROM Phase 
INNER JOIN Product ON Phase.[Phase ID] = Product.[Phase ID]
Group by Phase.[Phase ID]
     , Phase.Jobno
;

Is this the point where I need to join it back to my original query?

Yes this is where you join it back to the Original query.

 

[namliam]

However, it seems that the structure now poses a problem when trying to create this "simple" (manager's words) report.

It isnt all that hard, though the sql required is a bit more involved rather than a straightforward simple query.
Not to give myself the idea that I am so good... but it isnt that hard... Then again you dont want to see the 2 page SQLs that I have to deal with some times