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.