Access SQL Conditional Join

[Marco123]

Hi All,

I am trying to run a query that joins 2 tables, however, the joins will be different depending on certain criteria. I have 2 tables as follows:

TblMonth
Month MonthlyTotal
Dec-08
Jan-09
Feb-09
Mar-09
etc

TblSeason
Season SeasonalValue
Winter09 200
Summer09 100
Winter10 212
Summer10 200
etc

I am trying to update the MonthlyTotal column in tblMonth with the SeasonalValue. For example, to update January I use the following query:

UPDATE tblMonth INNER JOIN tblSeason
ON RIGHT(tblMonth.Month,2) = RIGHT(tblSeason.Season,2)
SET tblMonth.MonthlyTotal = tblSeason. SeasonalValue
WHERE LEFT(tblMonth.Month,3) = ‘Jan’

However, to update December I need to use the following:

UPDATE tblMonth INNER JOIN tblSeason
ON RIGHT(tblMonth.Month,2) + 1 = RIGHT(tblSeason.Season,2)
SET tblMonth.MonthlyTotal = tblSeason. SeasonalValue
WHERE LEFT(tblMonth.Month,3) = ‘Dec’

To summarise, if the month is December I need to use the following join:

ON RIGHT(tblMonth.Month,2) + 1 = RIGHT(tblSeason.Season,2)

However, if the month is January I use the following:

ON RIGHT(tblMonth.Month,2) = RIGHT(tblSeason.Season,2)

Is there a way to combine the 2 joins into one SQL query as opposed to having separate SQL queries for December and January?

Thanks

 

[Andrzejek]

What values do you want to get for Dec-09, Jan-09, etc?
[tt]
TblMonth
Month MonthlyTotal
Dec-08 [red]???[/red]
Jan-09 [red]???[/red]
Feb-09 [red]???[/red]
Mar-09 [red]???[/red]
etc

TblSeason
Season SeasonalValue
Winter09 200
Summer09 100
Winter10 212
Summer10 200
etc [/tt]
Wouldn't it be easier to add a column to TblMonth, something like Season?


Have fun.

---- Andy

 

[lameid]

So you are saying that a season is sort of like Fiscal years and might span accross calendar years?

Sounds like you want a User-defined function or table to determine what season a particular month is in.... So something like the belwo....

Function Season (dtMonth as datetime) As string
    dim lngYear as Long
    dim lngMonth as Month
    
    lngMonth = Month(dtMonth)
    lngYear = Year(dtMonth)

Select Case lngMonth
Case 11, 12
    Season = "Winter" & Right(intYear + 1,2)

Case 1,2,3
     Season = "Winter" & Right(intYear + 1,2)
Case 4,5,6,7,8,9,10
    Season = "Summer" & Right(intYear,2)
End select

End function

I do agree with Andy that storing the season with the month and doing the join that way will probably be faster... Just use something like the above to populate that table when you add values. In my example November through March is defined as Winter and everything else is Summer. November and December belong to the next year season. This does not make intuitive sense to me (I tend to think of of the November to March heating season from previous Natural Gas industry experience) but seemed to be what you were asking for... tweek for your own purposes.

 

[romeerome368]

Hello Marco123,

I agree with Andrzejek and lameid, but it's almost like having a primary and foreign key in your tables. The easiest way would be to put your seasonalvalue in your TblMonth, and tie them together with a join in your query (one to one or one to many).

 

[Marco123]

Thanks for the replies. It appears, then, that Jet SQL does not actually contain the functionality to do conditional joins. I'll do something along the lines suggested above and I quite like the code provided by lameid. As it happens, I work in the energy trading industry too and the winter season does span across a calendar year.