Forum Discussion

amasikonde's avatar
amasikonde
Copper Contributor
Jul 14, 2020
Solved

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
    • George_Hepworth's avatar
      George_Hepworth
      Silver Contributor
      Jul 21, 2020

      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.

Resources