Access sql display values from second table

Question

I'm still actively learning MS Access/sql (needs to be Office 2003 compliant). I've simplified things down as best as I can.

I have the following table (Main) comprising 2 fields (Ax and Ay) and which extends to several thousand records. This is the primary database and requires to be searchable:

table: Main

Ax  Ay
1   6
5   9
3   3
    7
5   5
7   2
2   
4   4
    3
6   5
7   6

etc.... Blank entries above are simply null values. Ax and Ay values can appear in either field.

There is a second table called Afull which comprises 2 fields called Avalid and Astr:

table: Afull

Avalid  Astr
1
2
3
4
5
6
7
8
9

Field Astr is initialised to Null at the start of each run.

The first use of this table is to store all valid values for Ax and Ay in field Avalid. The second use is to allow for the selection, by the user, of search critera. To do this, table Afull is added as a subform in the user search form. The user then selects an Avalid value to search for by inputting any value >0 into Astr - next to the value to be searched.

An sql query string is then built up whose purpose is to return all records carrying any 'permutation' of user-selected Avalid values:

SELECT Main.Ax,Main.Ay
FROM Main
WHERE (Main.Ax In(uservalues) OR Main.Ax Is Null) AND (Main.Ay In(uservalues) OR Main.Ay Is Null)

"uservalues" is converted into the list of Avalid values to be searched. This is fine and works as expected (double Null records don't exist).

Question: I would like to include the Astr value, itself, in the results - one field for Ax Astr values and one for Ay Astr values. I've tried a few things including adding the following to the SELECT statement:

strSQL = strSQL & ",IIF((Main.Ax In(uservalues)),Afull.Astr AS Axstr"
strSQL = strSQL & ",IIF((Main.Ay In(uservalues)),Afull.Astr AS Aystr"
strSQL = strSQL & "FROM Main,Afull"

...but this doesn't work. Is there any relatively simple method to achieve this?

Ultimately, I will also be using the Astr values to sort Ascending. Think of Astr as the 'strength' of the selected Avalid value.

Answer 1

Thank you, GB, greatly appreciated and working perfectly. Missing right bracket in the question also fixed, and Axstr Aystr --> improved readability AxStr AyStr noted (highlighting the Strength aspect).

An extension of the SELECT statement to get the totals of AxStr+AyStr i.e. ,AxStr+AyStr as TOTAL only works when both the respective Main.Ax and Main.Ay have non Null values.

Since Null values are permissable in the Main table I've extended the SELECT statement to get the total of AxStr+AyStr as follows:

strSQL = strSQL & ",IIF(AxStr>0 AND AyStr>0,AxStr+AyStr,IIF(Main.Ax Is Null,AyStr,
                    IIF(Main.Ay Is Null,AxStr,0))) AS AStrTOTAL"

Answer 2

Whatever works at the end of the day, but here is an alternative, basically it selects the value for the first expression that evaluates true. if A?Str is null or less than zero it uses the zero value so that you can just add the two results together.

Switch(AxStr>=0, AxStr,AxStr<0,0,isnull(AxStr),0) + Switch(AyStr>=0, AyStr,AyStr<0,0,isnull(AyStr),0)

Answer 3

to paraphrase a bit, users can select values for Ax & Ay and you only want to return records where both Ax & Ay are in the list of selected values or either of them could be null but not both.

now you want to add Astr for both Ax and Ay. you could use a co-related sub-query as below or you could join to Afull twice, once on Ax = Avalid and once on Ay = Avalid.

If you are going to build a SQL string like in your example, check your brackets too.

SELECT 
    Main.Ax,
    Main.Ay,
    (select Astr from Afull where Avalid = Main.Ax) as AxStr,
    (select Astr from Afull where Avalid = Main.Ay) as AyStr
FROM Main
WHERE (Main.Ax In(uservalues) OR Main.Ax Is Null) 
AND (Main.Ay In(uservalues) OR Main.Ay Is Null)