Forum Discussion

amasikonde's avatar
amasikonde
Copper Contributor
Jul 14, 2020

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...
access
  • amasikonde's avatar
    amasikonde
    Copper Contributor
    Jul 14, 2020

    Thank you so much that worked. I am very new when it comes to Access, and I have to use it for now. Is it possible to use the same approach to get data of vehicles less than six months after purchase date? George Hepworth 

    • George Hepworth's avatar
      George Hepworth
      Steel Contributor
      Jul 15, 2020

      amasikonde 

       

      Absolutely, the DateAdd() function can be used with a wide variety of arguments, from minutes to years. To do the same thing with a 6-month time frame, you can change the "yyyy" to "m" and the -1 to -6 and so on.

      • amasikonde's avatar
        amasikonde
        Copper Contributor
        Jul 20, 2020
        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

Resources