Access SQL Conditional Join

[Inspired]

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

 

[Guus2005]

This question is not very clear to me.

TblMonth is the name of the table?
Month MonthlyTotal are fieldnames?
Dec-08 is a record in the table?
Jan-09
Feb-09
Mar-09
etc

TblSeason is the name of the table?
Season SeasonalValue are fieldnames?
Winter09 200 is a record?
Summer09 100
Winter10 212
Summer10 200
etc

You might want to add an additional table where you store the months and their seasons

Table: TblSeasonMonth
Fields: Season, Month
Records:
Winter, December
Winter, Januari
etc

also if you split the problem in more queries each query will be better readable.

Now you don't need to update the tblMonth because the information is already there in the tblSeason. Storing the information in that table will denormalize the design.

HTH

 

[Inspired]

Yes, you are correct.

After much searching it does seem like the conditional join functionality is not available in Jet SQL so your suggestion is the best course of action. Thanks