Query to show counts across tables for different years

[Mattbro]

Hi all,
I have a table-TableCars. It has one column-[Cartype], in which the PK is all the car manufacturers I am repairing-say Fiat, Ford, Audi, Saab and BMW.
I have another table-Table2020 which has two columns-the PK is the job ref number-[Jobno] and the second column [Car] tells the car manufacturer from the above list that I have repaired in the year 2020.
I have two more tables-Table2021, and Table2022, both following the same pattern as Table2020.
Can anyone advise how I could write a query to show how many cars each year, so I can see increase/decrease etc?

	2020	2021	2022
Fiat	15	99	456
Ford	16	45	44
Audi	3	0	0
Saab	6	7	3
BMW	21	26	29

I am struggling with the code below:

SELECT TableCars.CarType, Count([Table2020].Jobno) AS CountOfJobno, Count([Table2021].Jobno) AS CountOfJobno1, Count([Table2022].Jobno) AS CountOfJobno2
FROM ((TableCars LEFT JOIN Table2020 ON TableCars.CarType = [Table2020].Car) LEFT JOIN Table2021 ON TableCars.CarType = [Table2021].Car) LEFT JOIN Table2022 ON TableCars.CarType = [Table2022].Car
GROUP BY TbSampleType.CarType;

Any input appreciated.


Thanks,
Mattbro451

[Welshgasman]

That is because your data is not normalised.
You should have one table and a date for when it was repaired. From that you can get the year and group.

You will need to create a Union query to get that into that format with and additional field with the year.

You are always going to have issues with a structure like you have. Best to fix it and move on.

[June7]

I agree, instead of 3 tables which means adding a new table for each year - where are 2023 and 2024? - should be one table.

Then a CROSSTAB query could produce desired output.

Otherwise, build 3 aggregate queries then join those queries to TableCars.

[Mattbro]

Thanks for the input all. consolidated all into a single table-TbData, then used the following SQL;

SELECT [TbData].YearX, Count(IIf([Car]="Ford","1")) AS ExpFord, Count(IIf([Car]="BMW","1")) AS ExpBMW, Count(IIf([Car]="Fiat","1")) AS ExpFiat, Count(IIf([Car]="Audi","1")) AS ExpAudi, Count(IIf([Car]="SAAB","1")) AS ExpSAAB
FROM TableCars INNER JOIN TbData ON TableCars.CarType=[TbData].Car
GROUP BY [TbData].YearX;

Thanks all for the suggestions.
Mattbro451

[Welshgasman]

Should not need anything as complicated.
Just group by Year and Car with a count on Car.

What happens when you start with another manufacturer? You would be in the same boat as you were at the start.

[Micron]

consolidated all into a single table-TbData,

Agreed. You have simply recreated the original issue but in a different fashion. Suggest you research db normalization.