Forum Discussion
Help with MS Access query to return Brand sold 1 year after purchase date
I'm having problem writing a query to return Brands of vehicles sold in less than 1 year from the purchase date. The data I'm using is as shown below. Brand Price Body Mileage EngineV Engine...
amasikonde
Is this what you want?
Re: Problem Converting Fiscal Year to Calendar Dates
George, I was actually able to write equations to return the respective columns. for FY i wrote Planned_Year_Sales:iif([Planned Sales Year]="FY21",2021,([Planned Sales Year]="FY22",2022,([Planned Sales Year]="FY23",2023)))
and for the Quarter I wrote Planned_Month_Sales:IIF([Planned Sales Quarter]=Q,12,IIF([Planned Sales Quarter]=Q2,3,IIF([Planned Sales Quarter]=Q3,6IIF([Planned Sales Quarter]=Q4,9))))
and for the date I picked the last date of the month of the Planned_Month_Sales and I wrote Planned_Date_Sales:IIF([Planned_Month_Sale]=12,1,IIF([Planned_Month_Sale]=3,31,IIF([Planned_Month_Sale]=3,6,IIF([Planned_Month_Sale]=4,9))))
Planned Sales Year Planned Sales Quarter Planned_Year_Sales Planned_Month_Sales Planned_Date_Sales Actual Planned Date
FY21 Q 2021 12 31
FY21 2Q 2021 3 31
FY21 3Q 2021 6 30
FY21 4Q 2021 9 30
FY22 Q 2022 12 31
FY22 2Q 2022 3 31
FY22 3Q 2022 6 30
FY22 4Q 2022 9 30
FY23 Q 2023 12 31
FY23 2Q 2023 3 31
FY23 3Q 2023 6 30
FY23 4Q 2023 9 30
Now I'm struggling to combine the numbers into a date in "Actual Planned Date" column to come up with example 2021/12/31, I would love and appreciate if you can help me
George, I was actually able to write equations to return the respective columns. for FY i wrote Planned_Year_Sales:iif([Planned Sales Year]="FY21",2021,([Planned Sales Year]="FY22",2022,([Planned Sales Year]="FY23",2023)))
and for the Quarter I wrote Planned_Month_Sales:IIF([Planned Sales Quarter]=Q,12,IIF([Planned Sales Quarter]=Q2,3,IIF([Planned Sales Quarter]=Q3,6IIF([Planned Sales Quarter]=Q4,9))))
and for the date I picked the last date of the month of the Planned_Month_Sales and I wrote Planned_Date_Sales:IIF([Planned_Month_Sale]=12,1,IIF([Planned_Month_Sale]=3,31,IIF([Planned_Month_Sale]=3,6,IIF([Planned_Month_Sale]=4,9))))
Planned Sales Year Planned Sales Quarter Planned_Year_Sales Planned_Month_Sales Planned_Date_Sales Actual Planned Date
FY21 Q 2021 12 31
FY21 2Q 2021 3 31
FY21 3Q 2021 6 30
FY21 4Q 2021 9 30
FY22 Q 2022 12 31
FY22 2Q 2022 3 31
FY22 3Q 2022 6 30
FY22 4Q 2022 9 30
FY23 Q 2023 12 31
FY23 2Q 2023 3 31
FY23 3Q 2023 6 30
FY23 4Q 2023 9 30
Now I'm struggling to combine the numbers into a date in "Actual Planned Date" column to come up with example 2021/12/31, I would love and appreciate if you can help me
amasikonde SInce this is a different table (planned sales as opposed to actual sales), it would be helpful to have sample data in that planned sales table.
thanks.
That's the table I need to complete the column "Actual Planned Date"George_Hepworth