SOLVED

Help with MS Access query to return Brand sold 1 year after purchase date

Copper Contributor

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.

BrandPriceBodyMileageEngineVEngine TypeRegistrationModel YearModelPurchase DateSold Daate
Audi23000crossover2404.2Petrolyes2007Q71/3/20169/10/2016
Audi14200vagon2002.7Dieselyes2006A61/3/20166/14/2017
Audi9500vagon1652.7Gasyes2003A6 Allroad5/23/20166/19/2017
Audi3500vagon2502.5Dieselno1998A65/23/20168/10/2016
Audi6800sedan2252.4Gasyes1998A65/23/20168/13/2016
Audi24900sedan1634.2Dieselyes2008A85/23/20167/8/2017
Audi6500sedan3302.4Petrolyes1999A68/15/20157/10/2017
Audi2900sedan12.3Gasyes198910010/10/20167/14/2017
Audi33900other141.8Petrolyes2014TT10/18/20162/9/2018
BMW4200sedan2772Petrolyes19913201/3/20169/7/2016
BMW6100sedan4382Gasyes19973201/3/20169/13/2016
BMW2990other2032Petrolno20013185/23/20166/23/2017
BMW21500other723Petrolyes2007Z410/11/20167/15/2017
BMW28500crossover1604.8Gasyes2008X510/13/20162/4/2018
BMW6000sedan3002.8Petrolyes199652810/16/20162/7/2018
BMW67500crossover12.5Dieselyes2016X510/17/20162/8/2018
Mercedes-Benz7900van4272.9Dieselyes1999Sprinter 2121/3/20169/8/2016
Mercedes-Benz13300sedan3585Gasyes2003S 5001/3/20169/9/2016
Mercedes-Benz199999crossover05.5Petrolyes2016GLS 631/3/20169/12/2016
Mercedes-BenzNAvan2402.2Dieselyes2007Vito5/23/20168/8/2016
Mercedes-Benz13566other1712.2Otherno2011Vito8/15/20157/11/2017
Mercedes-BenzNAcrossover04.6Petrolyes2016GLS 5008/15/20157/13/2017
Mitsubishi10500crossover1302.4Gasyes2006Outlander5/23/20168/14/2016
Mitsubishi8500hatch651.3Petrolyes2010Colt8/15/20157/12/2017
Mitsubishi17900crossover873.8Gasyes2008Pajero Wagon10/12/20162/3/2018
Renault10799vagon1931.5Dieselyes2012Megane1/3/20166/15/2017
Renault11950vagon1771.5Dieselyes2011Megane1/3/20166/17/2017
Renault2500sedan2601.79Petrolyes1994191/3/20166/18/2017
Renault8600hatch841.5Dieselyes2012Clio5/23/20166/22/2017
Toyota18300crossover1202Petrolyes2011Rav 41/7/20169/11/2016
Toyota16000crossover2504.7Gasyes2001Land Cruiser 1005/23/20166/21/2017
Toyota26500crossover212Petrolyes2013Rav 45/23/20168/9/2016
Toyota38233other02.4Dieselyes2016Hilux5/23/20168/11/2016
Toyota14500sedan1472.4Gasyes2006Camry10/15/20162/6/2018
Volkswagen1400other2121.8Gasno1999Golf IV1/7/20166/16/2017
Volkswagen10500sedan1001.8Petrolyes2008Passat B65/23/20166/20/2017
Volkswagen7500hatch1321.4Dieselyes2006Polo5/23/20168/12/2016
Volkswagen20800crossover1513Dieselyes2008Touareg8/15/20157/9/2017
Volkswagen9800van1601.9Dieselyes2009Caddy10/14/20162/5/2018
14 Replies

@amasikonde What do you actually want to see as "brand sold"? A count of each brand? A simple list of unique brands? Or....?

@George Hepworth 

 

Also what is the meaning of "PurchaseDate"? What is the meaning of "SoldDate"? 


Are you buying and selling cars? 

 

Do you want a count of cars purchased and also sold within a year or less of the date they were purchased as opposed to cars still held one year or later?

The last two columns are the "Purchase Date" and Sold Date but I have modified the data

BrandPriceBodyEngineVEngine TypeModel YearModelPurchase DateSold Daate
Audi23000crossover4.2Petrol2007Q71/3/20169/10/2016
Audi14200vagon2.7Diesel2006A61/3/20166/14/2017
Audi9500vagon2.7Gas2003A6 Allroad5/23/20166/19/2017
Audi3500vagon2.5Diesel1998A65/23/20168/10/2016
Audi6800sedan2.4Gas1998A65/23/20168/13/2016
Audi24900sedan4.2Diesel2008A85/23/20167/8/2017
Audi6500sedan2.4Petrol1999A68/15/20157/10/2017
Audi2900sedan2.3Gas198910010/10/20167/14/2017
Audi33900other1.8Petrol2014TT10/18/20162/9/2018
BMW4200sedan2Petrol19913201/3/20169/7/2016
BMW6100sedan2Gas19973201/3/20169/13/2016
BMW2990other2Petrol20013185/23/20166/23/2017
BMW21500other3Petrol2007Z410/11/20167/15/2017
BMW28500crossover4.8Gas2008X510/13/20162/4/2018
BMW6000sedan2.8Petrol199652810/16/20162/7/2018
BMW67500crossover2.5Diesel2016X510/17/20162/8/2018
Mercedes-Benz7900van2.9Diesel1999Sprinter 2121/3/20169/8/2016
Mercedes-Benz13300sedan5Gas2003S 5001/3/20169/9/2016
Mercedes-Benz199999crossover5.5Petrol2016GLS 631/3/20169/12/2016
Mercedes-BenzNAvan2.2Diesel2007Vito5/23/20168/8/2016
Mercedes-Benz13566other2.2Other2011Vito8/15/20157/11/2017
Mercedes-BenzNAcrossover4.6Petrol2016GLS 5008/15/20157/13/2017
Mitsubishi10500crossover2.4Gas2006Outlander5/23/20168/14/2016
Mitsubishi8500hatch1.3Petrol2010Colt8/15/20157/12/2017
Mitsubishi17900crossover3.8Gas2008Pajero Wagon10/12/20162/3/2018
Renault10799vagon1.5Diesel2012Megane1/3/20166/15/2017
Renault11950vagon1.5Diesel2011Megane1/3/20166/17/2017
Renault2500sedan1.79Petrol1994191/3/20166/18/2017
Renault8600hatch1.5Diesel2012Clio5/23/20166/22/2017
Toyota18300crossover2Petrol2011Rav 41/7/20169/11/2016
Toyota16000crossover4.7Gas2001Land Cruiser 1005/23/20166/21/2017
Toyota26500crossover2Petrol2013Rav 45/23/20168/9/2016
Toyota38233other2.4Diesel2016Hilux5/23/20168/11/2016
Toyota14500sedan2.4Gas2006Camry10/15/20162/6/2018
Volkswagen1400other1.8Gas1999Golf IV1/7/20166/16/2017
Volkswagen10500sedan1.8Petrol2008Passat B65/23/20166/20/2017
Volkswagen7500hatch1.4Diesel2006Polo5/23/20168/12/2016
Volkswagen20800crossover3Diesel2008Touareg8/15/20157/9/2017
Volkswagen9800van1.9Diesel2009Caddy10/14/20162/5/2018

I just want to see the Brand name and dates of all vehicles sold @George Hepworth 

This being a car dealership business, the "Purchase Date" is the date when the business bought the merchandise/vehicle and "Sold Date" is when the Vehicle was sold.  @George Hepworth 

I would love the query to return the brand name, price, and the date sold@George Hepworth 

best response confirmed by amasikonde (Copper Contributor)

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 

@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.

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

@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 is the table I need to complete the column Actual Planned Date@amasikonde 

That's the table I need to complete the column "Actual Planned Date"@George_Hepworth 

1 best response

Accepted Solutions
best response confirmed by amasikonde (Copper Contributor)