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 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 |
amasikonde
Is this what you want?
amasikonde What do you actually want to see as "brand sold"? A count of each brand? A simple list of unique brands? Or....?
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?
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 just want to see the Brand name and dates of all vehicles sold George Hepworth
amasikonde
Is this what you want?
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
