Include a field that's not related into a query

[ChrisSedgwick]

Hi,

How can I include a field from a table in to a query - that has no relationship?

For example...

I want to know the cost for all materials on each job - I can get that answer. However, I only want to know this for jobs that have been despatched (indicated by having a '2' in the field)

The 'Despatch' field is in a seperate table called 'Product', because we despatch multiple products for any ONE order at different times, so it needs to be against the Product and not the full job itself.

However, when I include the 'Product' table into the query and insert the 'Despatch' field into my query, and run it comes up with an error...

"The SQL Statement could not be executed because it contains ambiguouse outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then iclude that query in your SQL statement"

Here's the current SQL statement

SELECT [Materials Requested].[Job No], [Materials/Orders].Total, Jobs.[Project Name], Product.Despatch
FROM Product, Orders INNER JOIN (((Customer RIGHT JOIN Jobs ON Customer.[Customer ID] = Jobs.[Customer ID]) INNER JOIN [Materials Requested] ON Jobs.[Job No] = [Materials Requested].[Job No]) INNER JOIN [Materials/Orders] ON [Materials Requested].[Materials Requested ID] = [Materials/Orders].[Materials Requested ID]) ON Orders.[Orders ID] = [Materials/Orders].[Orders ID];

Any ideas?

 

[ChrisSedgwick]

SELECT [Materials Requested].[Job No], [Materials/Orders].Total, Jobs.[Project Name], Product.Despatch
FROM Product, Orders 
INNER JOIN (((Customer RIGHT JOIN Jobs ON Customer.[Customer ID] = Jobs.[Customer ID]) 
INNER JOIN [Materials Requested] ON Jobs.[Job No] = [Materials Requested].[Job No]) 
INNER JOIN [Materials/Orders] ON [Materials Requested].[Materials Requested ID] = [Materials/Orders].[Materials Requested ID]) 
ON Orders.[Orders ID] = [Materials/Orders].[Orders ID];