These SQL queries can be used as examples for learning and practice in MS Access.

SQL Query Q041. In case the IIF operator is used several times, it is more convenient to replace it with the SWITCH statement. The SWITCH operator (multiple-choice operator) is used to evaluate a logical expression and perform an action depending on the result. In the following example, the delivered batch is considered to be "small" if the quantity of the goods in the batch is less than 500. Otherwise, that is, if the quantity of the goods is greater than or equal to 500, the batch is considered "big":

SELECT dt, product_id, amount, 
SWITCH(amount<500,"малая",amount>=500,"большая") AS mark
FROM m_income; 

SQL Query Q042. In the next query, if the quantity of the goods in the received lot is less than 300, then the lot is considered "small". Otherwise, that is, if the condition amount & lt; 300 is not satisfied, then it is checked whether the quantity of goods in the batch is less than 500. If the lot size is less than 500, then it is considered "average". Otherwise, the lot is considered "big":

SELECT dt, product_id, amount, 
IIF(amount<300,"малая",
IIF(amount<1000,"средняя","большая")) AS mark
FROM m_income;

SQL Query Q043. In the next query, if the quantity of the goods in the received lot is less than 300, then the lot is considered "small". Otherwise, that is, if the condition amount & lt; 300 is not satisfied, then it is checked whether the quantity of goods in the batch is less than 500. If the lot size is less than 500, then it is considered "average". Otherwise, the lot is considered "big":

SELECT dt, product_id, amount, 
SWITCH(amount<300,"малая",
amount<1000,"средняя",
amount>=1000,"большая") AS mark
FROM m_income;

SQL Query Q044. In the following query, sales are divided into three groups: small (up to 150), medium (from150 to 300), large (300 or more). Next, for each group, the total is calculated:

SELECT Category, SUM(outcome_sum) AS Ctgry_Total
FROM (SELECT amount*price AS outcome_sum,
IIf(amount*price<150,"малая",
IIf(amount*price<300,"средняя","большая")) AS Category 
FROM m_outcome)  AS t
GROUP BY Category;

SQL Query Q045. DateAdd is used to add days, months, or years to a given date and to retrieve a new date. The following query:

1) to the date from the dt field adds 30 days and displays a new date in the dt_plus_30d field;
2) the date from the dt field adds 1 month and displays a new date in the dt_plus_1m field:

SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m
FROM m_income;

SQL Query Q046. The DateDiff function is used to calculate the difference between two dates in different units (days, months, or years). The following query calculates the difference between the date in the dt field and the current date in days, months, and years:

SELECT dt, DateDiff("d",dt,Date()) AS last_day, 
DateDiff("m",dt,Date()) AS last_months, 
DateDiff("yyyy",dt,Date()) AS last_years
FROM m_income;

SQL Query Q047. Calculate the number of days from the day the goods arrive (m_income table) to the current date using the DateDiff function and compare the expiration date (m_product table):

SELECT a.id, product_id, dt, lifedays, 
DateDiff("d",dt,Date()) AS last_days
FROM m_income AS a INNER JOIN m_product AS b 
ON a.product_id=b.id;

SQL Query Q048. Calculate the number of days from the day the goods were received until the current date, then check whether this expiration date is greater than the expiration date:

SELECT a.id, product_id, dt, lifedays, 
DateDiff("d",dt,Date()) AS last_days, IIf(last_days>lifedays,"Да","Нет") AS date_expire
FROM m_income a INNER JOIN m_product b
ON a.product_id=b.id;

SQL Query Q049. The number of months from the day the goods arrive to the current date. The month_last1 column calculates the absolute number of months, the month_last2 column calculates the number of full months:

SELECT dt, DateDiff("m",dt,Date()) AS month_last1, 
DateDiff("m",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
FROM m_income;

SQL Query Q050. Quarterly report on the number and amount of goods posted in 2011 is displayed:

SELECT kvartal, SUM(outcome_sum) AS Total
FROM (SELECT amount*price AS outcome_sum, month(dt) AS m, 
SWITCH(m<4,1,m<7,2,m<10,3,m>=10,4) AS kvartal 
FROM m_income WHERE year(dt)=2011)  AS t
GROUP BY kvartal;