CODEWITHSUNDEEP
sqlms-access
bebongtheshark
bebongtheshark

Reputation: 37

If Then Else in WHERE clause in MS Access SQL Query

I'm trying out MS Access SQL Query. My Data is structured like this Rent Table

The idea is I want to split the table to collect the latest start up to 12 months back using [Year_Start] and [Month_Start] as basis. So my rough code will be:

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start])
ORDER BY [Renter_Name];

Subsequently, other month tables will conceptually be coded like this:

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start]) - 1
ORDER BY [Renter_Name];

And then subsequent months will be adjusted using the minus sign.

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start]) - 2
ORDER BY [Renter_Name];

I'm also considering a case where [Month_Start] = Max([Month_Start]) - x will be zero (0) or a negative number so a theoretical code will be:

    SELECT [Renter_Name], [Amount]
    FROM RentTable1
    IF Max([Month_Start]) - X <= 0 THEN 
       WHERE [Year_Start] = Max([Year_Start]) - 1 AND [Month_Start] = Max([Month_Start]) - X
    ELSE
       WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start]) - X
    END IF
    ORDER BY [Renter_Name];

*** X being the months backward from the latest start month and year.

Clearly, you see my SQL coding skills are really weak. Pardon me as I'm really a beginner. So there are some touches other standard programming like If-Then-Else statements.

I was hoping someone could propose to correct the above codes.

Thanks! Appreciate everyone who stumble upon this question.

EDIT 1:

Just to clarify, this is the expected thought:

In the example the latest period is 2016 and 4. So it should pick it up for TABLE1.

A subsequent query is to be made to minus one month from the latest period so the result should be 2016 and 3. This goes on until 2016 and 1.

When 4 - 4 happens which equals 0, the query should be able to skip through this illogical step and go through (2016 - 1) and the get the max month using the result of (2016 - 1) which is 2015 and 12.

Upvotes: 2

Views: 4079

Answers (1)

cha
cha

Reputation: 10411

First of all, here is my advise: when dealing with dates use the DATE data type. You can specify the StartDate as 2016-04-01 and the EndDate as 2017-04-31. Even better: specify the EndDate as 2017-05-01 and always remember that you need to use >= for the StartDate and < for the EndDate.

Now, to your problem. You need to convert the columns to the proper date using the [DateSerial()][1] function, like this:

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE DateSerial([Year_Start], [Month_Start], 1) = 
    (SELECT Max(DateSerial([Year_Start], [Month_Start], 1) as dt FROM RentTable1)
ORDER BY [Renter_Name];

To get the details for the previous month use DateAdd() function. Here is the example for the previous month:

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE DateSerial([Year_Start], [Month_Start], 1) = 
    (SELECT DateAdd('m', -1, Max(DateSerial([Year_Start], [Month_Start], 1)) as dt FROM RentTable1)
ORDER BY [Renter_Name];

And here is the universal query to get the details for the X months ago:

SELECT [Renter_Name], [Amount]
FROM RentTable1
WHERE DateSerial([Year_Start], [Month_Start], 1) = 
    (SELECT DateAdd('m', [X] * (-1), Max(DateSerial([Year_Start], [Month_Start], 1)) as dt FROM RentTable1)
ORDER BY [Renter_Name];

Upvotes: 1

Related Questions