Joining on Calculated Field - Is This a Problem?

[datAdrenaline]

>> your query did not work, sorry. it gave me a 'syntax error in the FROM clause'. not sure why <<

My parenthesis seem unbalanced, also was missing a character (PropIDBx4 instead of PropIDBx14)! ...

>> it's a bit too complex for me.. <<

Mine is no more complex than yours! ...

Here is mine again ... (I think I fixed it, but with out tables to test ... its still AIR CODE!) ...

SELECT vTbl1.*
FROM [COLOR=magenta](SELECT tbl1.[Prop ID][/COLOR]
[COLOR=magenta]             , tbl1.[Date][/COLOR]
[COLOR=magenta]             , IIf(Right(tbl1.[Prop ID],1)="o"[/COLOR]
[COLOR=magenta]                   ,Left(tbl1.[Prop ID][/COLOR]
[COLOR=magenta]                         ,Len(tbl1.[Prop ID])-1)[/COLOR]
[COLOR=magenta]             ,tbl1.[Prop ID]) AS PropIDbx14[/COLOR]
[COLOR=magenta]      FROM tbl1 [/COLOR]
[COLOR=magenta]           INNER JOIN FeatureRatings[/COLOR]
[COLOR=magenta]           ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID][/COLOR]
[COLOR=magenta]      WHERE tbl1.[Inspection Year]=2009[/COLOR]
[COLOR=magenta]            AND tbl1.Season = "spring"[/COLOR]
[COLOR=magenta]            AND tbl1.Round = 2[/COLOR]
[COLOR=magenta]            AND tbl1.Date >= #7/1/2006#) As vTbl1[/COLOR] 
                                            [COLOR=orange]INNER JOIN[/COLOR] [COLOR=blue]tbl1[/COLOR]
                                            ON [COLOR=magenta]vTbl1.PropIDBx14[/COLOR] = [COLOR=blue]tbl1.[Prop ID][/COLOR]
ORDER BY [COLOR=magenta]vTbl1.[Prop ID][/COLOR], [COLOR=magenta]vTbl1.[Date][/COLOR] DESC

Explaination: Everything is purple is a query, which has a SQL statemtent that could be saved as an Access Query Object, but it is a "sub-query" in its use here and the purple is "Aliased" as "vTbl1". So when refering to the data/fields that comes from the purple, we can fully qualify the value with vTbl1. As you can see, I throw as many filters as I can on the purple, plus I evaluate the expression for PropIDBx14 in that sub-query. With the evaluation of the PropIDBx14 done in the Sub-Query (vTbl1). We can now refer to PropIDBx14 from the MAIN level of the query through the aliased source of vTbl1, so there is NO need to have two representations of the same expression.

Going further, I INNER JOIN tbl1 with the sub-query aliased as vTbl1 on vTbl1.PropIDBx14 and tbl1.[Prop ID] being equal. Note that since PropIDBx14 is "available" at the MAIN query level through the vTbl1 alias, there is no need to duplication the expression that generates the value for PropIDBx14.

Did that make sense ...

............................

Here is your query, and I have marked the colors as representation as to where I put the component from your query into my query ...

SELECT [COLOR=magenta]tbl1.[Prop ID][/COLOR]
[COLOR=magenta]       , tbl1.Date[/COLOR]
[COLOR=magenta]       , IIf(Right([tbl1]![Prop ID],1)="o"[/COLOR]
[COLOR=magenta]             ,Left([tbl1]![Prop ID][/COLOR]
[COLOR=magenta]                   ,Len([tbl1]![Prop ID])-1)[/COLOR]
[COLOR=magenta]       ,[tbl1]![Prop ID]) AS PropIDbx14[/COLOR]
[COLOR=magenta]FROM tbl1[/COLOR]
[COLOR=magenta]     INNER JOIN FeatureRatings[/COLOR]
[COLOR=magenta]     ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID][/COLOR]
[COLOR=magenta]WHERE tbl1.Date >= #7/1/2006#[/COLOR]
      AND [COLOR=magenta]IIf(Right([tbl1]![Prop ID],1)="o"[/COLOR]
[COLOR=magenta]              ,Left([tbl1]![Prop ID],Len([tbl1]![Prop ID])-1)[/COLOR]
[COLOR=magenta]              ,[tbl1]![Prop ID])[/COLOR] [COLOR=darkorange]In[/COLOR] (SELECT tbl1.[Prop ID]
                                     FROM [COLOR=blue]tbl1[/COLOR]
                                     WHERE [COLOR=magenta]tbl1.[Inspection Year]=2009[/COLOR]
[COLOR=magenta]                                           AND tbl1.Season="spring"[/COLOR]
[COLOR=magenta]                                           AND tbl1.Round=2[/COLOR])
ORDER BY [COLOR=blue]tbl1.[Prop ID], tbl1.Date DESC[/COLOR];

So ... just leaving your criteria as it is, I will push most of the purple from above (except the criteria from your select) into to a sub-query as the main source, shown in purple below ..

SELECT [COLOR=magenta]vTbl1.*[/COLOR]
FROM [COLOR=magenta](SELECT tbl1.[Prop ID]
            , tbl1.Date
            , IIf(Right([tbl1]![Prop ID],1)="o"
                  ,Left([tbl1]![Prop ID]
                        ,Len([tbl1]![Prop ID])-1)
            ,[tbl1]![Prop ID]) AS PropIDbx14
     FROM tbl1
          INNER JOIN FeatureRatings
          ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID]) As vTbl1[/COLOR]
WHERE [COLOR=magenta]vTbl1.Date[/COLOR] >= #7/1/2006#
      AND [COLOR=magenta]vTbl1.PropIDBx14[/COLOR] [COLOR=orange]In[/COLOR] (SELECT tbl1.[Prop ID]
                             FROM tbl1
                             WHERE tbl1.[Inspection Year]=2009
                                   AND tbl1.Season="spring"
                                   AND tbl1.Round=2)
ORDER BY [COLOR=magenta]vTbl1.[Prop ID], vTbl1.Date DESC[/COLOR];

Next ... I take the WHERE clause from IN clause SQL, as well as the WHERE clause at the MAIN query level and place it in the purple sub-query of vTbl1

[/COLOR]
SELECT [COLOR=magenta]vTbl1.*
[/COLOR]FROM [COLOR=magenta](SELECT tbl1.[Prop ID]
            , tbl1.Date
            , IIf(Right([tbl1]![Prop ID],1)="o"
                  ,Left([tbl1]![Prop ID]
                        ,Len([tbl1]![Prop ID])-1)
            ,[tbl1]![Prop ID]) AS PropIDbx14
     FROM tbl1
          INNER JOIN FeatureRatings
          ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID]
     WHERE tbl1.[Inspection Year]=2009
           AND tbl1.Season="spring"
           AND tbl1.Round=2
           AND tbl1.Date >= #7/1/2006#) As vTbl1[/COLOR]
WHERE [COLOR=magenta]vTbl1.PropIDBx14[/COLOR] [COLOR=orange]In [/COLOR](SELECT tbl1.[Prop ID]
                             FROM tbl1)
ORDER BY [COLOR=magenta]vTbl1.[Prop ID], vTbl1.Date DESC[/COLOR];

Then ... I replace the IN clause with an INNER JOIN ...

SELECT vTbl1.*
FROM [COLOR=magenta](SELECT tbl1.[Prop ID]
            , tbl1.Date
            , IIf(Right([tbl1]![Prop ID],1)="o"
                  ,Left([tbl1]![Prop ID]
                        ,Len([tbl1]![Prop ID])-1)
            ,[tbl1]![Prop ID]) AS PropIDbx14
     FROM tbl1
          INNER JOIN FeatureRatings
          ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID]
     WHERE tbl1.[Inspection Year]=2009
           AND tbl1.Season="spring"
           AND tbl1.Round=2
           AND tbl1.Date >= #7/1/2006#) As vTbl1[/COLOR]
[COLOR=orange]INNER JOIN[/COLOR] [COLOR=blue]tbl1[/COLOR] [COLOR=orange]ON[/COLOR] [COLOR=magenta]vTbl1.PropIDBx14[/COLOR] = [COLOR=blue]tbl1.[Prop ID]
[/COLOR]ORDER BY [COLOR=magenta]vTbl1.[Prop ID], vTbl1.Date DESC[/COLOR];

I think that is about it .... off to bed for me! ... I hope the description helps! ...

 

[lemo]

thanks again, datAdrenaline.

i think i get the idea.. almost.. next time i need to employ this type of design, i should be able to do it, i sure hope..

l