Crosstab query with multiple values producing ambiguous outer joins

[GJ Kits]

I have joined to seek some help to re-write the SQL in Crosstab query. I am self taught and have limited experience in Access 2003. I have spent a couple of hours searching this site in the hope finding a resolution to my problem.



I have the following Crosstab query:

TRANSFORM Sum([Step 2 Resources].[Site 1 Qty]) AS [SumOfSite 1 Qty]
SELECT [Step 2 Resources].Resource
FROM ([Step 1 Functions] INNER JOIN [Step 2 Resources] ON [Step 1 Functions].Function = [Step 2 Resources].Function) LEFT JOIN Sites ON [Step 2 Resources].[Site 1] = Sites.Address
GROUP BY [Step 2 Resources].Resource
ORDER BY Sites.Address
PIVOT Sites.Address;

The above works well for the first field 'Site 1'.

When I add the second field 'Site 2' in Query design mode, an error message is produced “The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to perform first, create a separate query that performs the first join and then include that query in your SQL statement”. I would prefer to use SQL mode to redesign the query, although this is currently beyond me, and frankly proving to be extremely challenging. I understand that the error states that I must separate the SQL into multiple query statements, and then join them, although it is proving very challenging to figure out.

Seeking any help to re-write the SQL to include the second field ‘Site 2’.

Many thanks for your time and feedback.

[June7]

Don't understand data relationships. If you want to provide db for analysis, follow instructions at bottom of my post.

[GJ Kits]

Hi June7,

I have attached db1 (mock up) containing the same relationships. In summary, there are a number of functions preformed one or more possible sites. Each function has multiple resources at each site, required so that each function can be preformed. The crosstab query I have developed only provides the detail I seek for ‘Site 1’. I need to be able to combine the second site ‘Site 2’ in the query.

I need to know:
How many resources are at each site, by resource?
How many resources are required by each function, by site?

Thanks in advance.

[June7]

You did not provide attempted query in the db. I copy/pasted the query from your post, dragged field [Site 2] to the design grid. Query runs without error.

[GJ Kits]

What are you talking about? The attached db1 has the exactly same SQL statement as the post. Acutally, I copy and pasted from the post into db1. I have no idea what you mean when you say "dragged field [Site 2] to the design grid. Query runs without error". Please attach so I can see what you mean. Are you suggesting that the query runs with field [Site 2] only as a value? If so, I already know that. I need to be able to run the crosstab query including with both fields [Site 1] & [Site 2] as value's.

[June7]

Sorry, my quick review of db was too quick. I read the names of the two queries and jumped to conclusion they weren't your attempt for the posted query.

I see now that both queries have the same tables but each sums a different field.

A crosstab can't have multiple VALUE fields.

What you can do is a query that joins crosstab queries on common RowHeading. If each crosstab has the same RowHeading values, then all records from both queries will be retrieved with this one-to-one relationship.

[GJ Kits]

Easier said, perhaps you could provide me an example? I am asking for help, if I knew how to do it I would have already.

[June7]

This would be a simple SELECT query with JOIN.

You already have the two crosstab queries.

Just open the query designer, select the two queries, join on the two Resource fields, drag fields to the design grid.

Very basic Access functionality. Access Help has guidelines on using query designer or Google for tutorials.

The resulting sql statement would be like:
SELECT [Crosstab for Site 1].*, [Crosstab for Site 2].*
FROM [Crosstab for Site 2] INNER JOIN [Crosstab for Site 1] ON [Crosstab for Site 2].Resource = [Crosstab for Site 1].Resource;

[GJ Kits]

I have already achieved what you have suggested prior to joining the forum. The problem is that each row has ‘two’ values per unique ‘site’. I need the query to produce ‘one’ value per unique ‘site’.

[June7]

Need to add each pair of address values?

Like:

SELECT Resource, [Crosstab for Site 1].[Site Address A]+[Crosstab for Site 2].[Site Address A] AS SiteA
FROM [Crosstab for Site 2] INNER JOIN [Crosstab for Site 1] ON [Crosstab for Site 2].Resource = [Crosstab for Site 1].Resource;

[GJ Kits]

Does not work, see attachement.

[June7]

I typed in the Resource field to the posted sql, forgot there were two in the query. Pick one of the Resource fields and drag to the design grid:

SELECT [Crosstab for Site 1].Resource, [Crosstab for Site 1].[Site Address A]+[Crosstab for Site 2].[Site Address A] AS SiteA FROM [Crosstab for Site 2] INNER JOIN [Crosstab for Site 1] ON [Crosstab for Site 2].Resource = [Crosstab for Site 1].Resource;

Alternative query sequence:

SELECT [Step 2 Resources].Resource, Sites.Address, Sum([Site 1 Qty]+[Site 2 Qty]) AS SiteTot FROM Sites RIGHT JOIN ([Step 1 Functions] INNER JOIN [Step 2 Resources] ON [Step 1 Functions].Function = [Step 2 Resources].Function) ON Sites.Address = [Step 2 Resources].[Site 1] GROUP BY [Step 2 Resources].Resource, Sites.Address;

TRANSFORM Sum([SiteTot]) AS Expr1
SELECT Query1.Resource
FROM Query1
GROUP BY Query1.Resource
PIVOT Query1.Address;

[GJ Kits]

Now that is what I am talking about! Great! Works just the way I need it, THANKS.

Question, if I wanted to omit from the query any row returning a null value in the SiteTot, how I would do this? I attempted to use ‘Is Not Null’ in the criteria of the source crosstab queries that did not work, nor did it work on the query that you wrote. In both attempts, the query returned an error.

[June7]

Not Is Null worked in the alternative query:

SELECT [Step 2 Resources].Resource, Sites.Address, Sum([Site 1 Qty]+[Site 2 Qty]) AS SiteTot
FROM Sites RIGHT JOIN ([Step 1 Functions] INNER JOIN [Step 2 Resources] ON [Step 1 Functions].Function = [Step 2 Resources].Function) ON Sites.Address = [Step 2 Resources].[Site 1]
GROUP BY [Step 2 Resources].Resource, Sites.Address
HAVING ((Not (Sum([Site 1 Qty]+[Site 2 Qty])) Is Null));

[GJ Kits]

I have re-written the SQL (attached) to include (6x) site fields instead of the (2x) site fields in my initial inquiry. The statement also contains a few additional fields not previously discussed. In the end I went with a Union query. It meets my needs and provides a basis to develop more queries. Thanks for your help; you certainly got me heading in the right direction. I'll wait 24 hours then close the thread as solved.