Going crazy on Null returns

[ShouldBeFishen]

I have a query with a list of names called Resources

It contains 5 resource types
Plumber
Electrician
Painter
Carpenter
Tiler
I have another table with job names [Assignments] and assigned resource types with work hours IE:
Cut timber Carpenter 4 hours First floor
Fix pipes Plumber 2 hours Patio
Etc etc……
I have created a query that lists the resource type that is chosen [Select resource type] and shows all there jobs and hours. No probs!

If I choose a resource name that does not exist in the [assignments] query then I get a blank result.
I have tried the NZ function but I must be doing it wrong or I am on the wrong track.

What I would like to to see is a row of records like
Resource Hours Job Area
- - - First floor

I hope my explanation is clear enough not to bug people.
Your help would be appreciated

Cheers


John

 

[r937]

you'll want to use a LEFT OUTER JOIN in your query, to return each resource whether or not there are matching rows in the assignments table

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[ShouldBeFishen]

Thanks for that.
It just dawned on me I was calling the resource name from the wrong table and the outer left join was not showing any difference.
It helps when you call the field from the correct table
Thanks
JB