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

[amasikonde]

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 Type	Registration	Model Year	Model	Purchase Date	Sold Daate
Audi	23000	crossover	240	4.2	Petrol	yes	2007	Q7	1/3/2016	9/10/2016
Audi	14200	vagon	200	2.7	Diesel	yes	2006	A6	1/3/2016	6/14/2017
Audi	9500	vagon	165	2.7	Gas	yes	2003	A6 Allroad	5/23/2016	6/19/2017
Audi	3500	vagon	250	2.5	Diesel	no	1998	A6	5/23/2016	8/10/2016
Audi	6800	sedan	225	2.4	Gas	yes	1998	A6	5/23/2016	8/13/2016
Audi	24900	sedan	163	4.2	Diesel	yes	2008	A8	5/23/2016	7/8/2017
Audi	6500	sedan	330	2.4	Petrol	yes	1999	A6	8/15/2015	7/10/2017
Audi	2900	sedan	1	2.3	Gas	yes	1989	100	10/10/2016	7/14/2017
Audi	33900	other	14	1.8	Petrol	yes	2014	TT	10/18/2016	2/9/2018
BMW	4200	sedan	277	2	Petrol	yes	1991	320	1/3/2016	9/7/2016
BMW	6100	sedan	438	2	Gas	yes	1997	320	1/3/2016	9/13/2016
BMW	2990	other	203	2	Petrol	no	2001	318	5/23/2016	6/23/2017
BMW	21500	other	72	3	Petrol	yes	2007	Z4	10/11/2016	7/15/2017
BMW	28500	crossover	160	4.8	Gas	yes	2008	X5	10/13/2016	2/4/2018
BMW	6000	sedan	300	2.8	Petrol	yes	1996	528	10/16/2016	2/7/2018
BMW	67500	crossover	1	2.5	Diesel	yes	2016	X5	10/17/2016	2/8/2018
Mercedes-Benz	7900	van	427	2.9	Diesel	yes	1999	Sprinter 212	1/3/2016	9/8/2016
Mercedes-Benz	13300	sedan	358	5	Gas	yes	2003	S 500	1/3/2016	9/9/2016
Mercedes-Benz	199999	crossover	0	5.5	Petrol	yes	2016	GLS 63	1/3/2016	9/12/2016
Mercedes-Benz	NA	van	240	2.2	Diesel	yes	2007	Vito	5/23/2016	8/8/2016
Mercedes-Benz	13566	other	171	2.2	Other	no	2011	Vito	8/15/2015	7/11/2017
Mercedes-Benz	NA	crossover	0	4.6	Petrol	yes	2016	GLS 500	8/15/2015	7/13/2017
Mitsubishi	10500	crossover	130	2.4	Gas	yes	2006	Outlander	5/23/2016	8/14/2016
Mitsubishi	8500	hatch	65	1.3	Petrol	yes	2010	Colt	8/15/2015	7/12/2017
Mitsubishi	17900	crossover	87	3.8	Gas	yes	2008	Pajero Wagon	10/12/2016	2/3/2018
Renault	10799	vagon	193	1.5	Diesel	yes	2012	Megane	1/3/2016	6/15/2017
Renault	11950	vagon	177	1.5	Diesel	yes	2011	Megane	1/3/2016	6/17/2017
Renault	2500	sedan	260	1.79	Petrol	yes	1994	19	1/3/2016	6/18/2017
Renault	8600	hatch	84	1.5	Diesel	yes	2012	Clio	5/23/2016	6/22/2017
Toyota	18300	crossover	120	2	Petrol	yes	2011	Rav 4	1/7/2016	9/11/2016
Toyota	16000	crossover	250	4.7	Gas	yes	2001	Land Cruiser 100	5/23/2016	6/21/2017
Toyota	26500	crossover	21	2	Petrol	yes	2013	Rav 4	5/23/2016	8/9/2016
Toyota	38233	other	0	2.4	Diesel	yes	2016	Hilux	5/23/2016	8/11/2016
Toyota	14500	sedan	147	2.4	Gas	yes	2006	Camry	10/15/2016	2/6/2018
Volkswagen	1400	other	212	1.8	Gas	no	1999	Golf IV	1/7/2016	6/16/2017
Volkswagen	10500	sedan	100	1.8	Petrol	yes	2008	Passat B6	5/23/2016	6/20/2017
Volkswagen	7500	hatch	132	1.4	Diesel	yes	2006	Polo	5/23/2016	8/12/2016
Volkswagen	20800	crossover	151	3	Diesel	yes	2008	Touareg	8/15/2015	7/9/2017
Volkswagen	9800	van	160	1.9	Diesel	yes	2009	Caddy	10/14/2016	2/5/2018

[#2]

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

[#3]

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

[#4]

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

Brand	Price	Body	EngineV	Engine Type	Model Year	Model	Purchase Date	Sold Daate
Audi	23000	crossover	4.2	Petrol	2007	Q7	1/3/2016	9/10/2016
Audi	14200	vagon	2.7	Diesel	2006	A6	1/3/2016	6/14/2017
Audi	9500	vagon	2.7	Gas	2003	A6 Allroad	5/23/2016	6/19/2017
Audi	3500	vagon	2.5	Diesel	1998	A6	5/23/2016	8/10/2016
Audi	6800	sedan	2.4	Gas	1998	A6	5/23/2016	8/13/2016
Audi	24900	sedan	4.2	Diesel	2008	A8	5/23/2016	7/8/2017
Audi	6500	sedan	2.4	Petrol	1999	A6	8/15/2015	7/10/2017
Audi	2900	sedan	2.3	Gas	1989	100	10/10/2016	7/14/2017
Audi	33900	other	1.8	Petrol	2014	TT	10/18/2016	2/9/2018
BMW	4200	sedan	2	Petrol	1991	320	1/3/2016	9/7/2016
BMW	6100	sedan	2	Gas	1997	320	1/3/2016	9/13/2016
BMW	2990	other	2	Petrol	2001	318	5/23/2016	6/23/2017
BMW	21500	other	3	Petrol	2007	Z4	10/11/2016	7/15/2017
BMW	28500	crossover	4.8	Gas	2008	X5	10/13/2016	2/4/2018
BMW	6000	sedan	2.8	Petrol	1996	528	10/16/2016	2/7/2018
BMW	67500	crossover	2.5	Diesel	2016	X5	10/17/2016	2/8/2018
Mercedes-Benz	7900	van	2.9	Diesel	1999	Sprinter 212	1/3/2016	9/8/2016
Mercedes-Benz	13300	sedan	5	Gas	2003	S 500	1/3/2016	9/9/2016
Mercedes-Benz	199999	crossover	5.5	Petrol	2016	GLS 63	1/3/2016	9/12/2016
Mercedes-Benz	NA	van	2.2	Diesel	2007	Vito	5/23/2016	8/8/2016
Mercedes-Benz	13566	other	2.2	Other	2011	Vito	8/15/2015	7/11/2017
Mercedes-Benz	NA	crossover	4.6	Petrol	2016	GLS 500	8/15/2015	7/13/2017
Mitsubishi	10500	crossover	2.4	Gas	2006	Outlander	5/23/2016	8/14/2016
Mitsubishi	8500	hatch	1.3	Petrol	2010	Colt	8/15/2015	7/12/2017
Mitsubishi	17900	crossover	3.8	Gas	2008	Pajero Wagon	10/12/2016	2/3/2018
Renault	10799	vagon	1.5	Diesel	2012	Megane	1/3/2016	6/15/2017
Renault	11950	vagon	1.5	Diesel	2011	Megane	1/3/2016	6/17/2017
Renault	2500	sedan	1.79	Petrol	1994	19	1/3/2016	6/18/2017
Renault	8600	hatch	1.5	Diesel	2012	Clio	5/23/2016	6/22/2017
Toyota	18300	crossover	2	Petrol	2011	Rav 4	1/7/2016	9/11/2016
Toyota	16000	crossover	4.7	Gas	2001	Land Cruiser 100	5/23/2016	6/21/2017
Toyota	26500	crossover	2	Petrol	2013	Rav 4	5/23/2016	8/9/2016
Toyota	38233	other	2.4	Diesel	2016	Hilux	5/23/2016	8/11/2016
Toyota	14500	sedan	2.4	Gas	2006	Camry	10/15/2016	2/6/2018
Volkswagen	1400	other	1.8	Gas	1999	Golf IV	1/7/2016	6/16/2017
Volkswagen	10500	sedan	1.8	Petrol	2008	Passat B6	5/23/2016	6/20/2017
Volkswagen	7500	hatch	1.4	Diesel	2006	Polo	5/23/2016	8/12/2016
Volkswagen	20800	crossover	3	Diesel	2008	Touareg	8/15/2015	7/9/2017
Volkswagen	9800	van	1.9	Diesel	2009	Caddy	10/14/2016	2/5/2018

[#5]

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

[#6]

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 

[#7]

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

[#8]

@George Hepworth 

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

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

[#9]

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

[#10 · Accepted solution]

@amasikonde 

Is this what you want?

[#11]

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 

[#12]

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

[#13]

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

[#14]

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

[#15]

That is the table I need to complete the column Actual Planned Date@amasikonde 

[#16]

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