Ambiguous joins

[murgatroyd]

I have an application that includes a "Parameters" table containing a single record (no key) with a number of fields that represent alarm thresholds. I want to use a couple of these fields in a report, for conditional formatting when certain values exceed the thresholds; however, when I add the Parameters table to the underlying query, I get an "ambiguous outer joins" error message. As there is nothing to join, how can I get the field values from the table into the query so I can use them in the report?

 

[CJ_London]

you cannot mix outer/left/right joins with no joins.

try changing existing query into a nested subquery e.g.

SELECT *
FROM (original query) Orig, paramtable

There may be a better way by incorporating the paramatable into a nested query within your original query but depends on your query

 

[murgatroyd]

Thanks for your reply; however, I'm not very familiar with SQL code. Is there a way to do this in the Access query grid?

Alternatively, as the single record in the Parameters table only needs to be read once to obtain a couple of fields from it to use as constants in a couple of conditional formatting expressions, is it possible to put some code in the report header to load the values from the Parameters table?

 

[CJ_London]

it can't be done in the access grid directly but in the sql view

go to sql view and type

SELECT * FROM (

before the first SELECT

and at the end, before the semi colon put

) Orig

then return to the access grid where you can then add your parameters table

Alternatively create a new query and drag onto the access grid your existing query and the parameters table

is it possible to put some code in the report header to load the values from the Parameters table?

possibly, not sure how and potentially a lot more complex

 

[murgatroyd]

OK thanks, I've got it now.