Advanced Group By

[houseofturner]

Is there any way that I can perform a function based on a variable within a group. So for example, I have a table with

Make: BMW
model: 5 Series
year: 2014

There might be 5 records like this (for different 5 series derivatives) and 5 where the year is 2015.

I want to combine the model and year but only where there are records with different years.

So for example, if I had 5 records with a model of 5 Series and year of 2015 and no other records I would want to leave the model as 5 Series. But if there was 5 with 2014 and 5 with 2015 I would want to show the model as 5 Series 2014 or 5 Series 2015 (whichever was appropriate).

I can do it with multiple queries but I am trying to do it one as I need to port it across to a mysql database.

 

[CJ_London]

not sure what your issue is - you would group by make, model and year. Perhaps provide some sample data plus the required result to clarify

If you are not summing, counting etc you could use SELECT DISTINCT instead

 

[houseofturner]

Hi, thanks for response. The attached XLS may explain it better. I simply want to update the model with the year only where there are different years for the same model. So for example:

BMW 5 Series 2104 would stay as BMW 5 Series 2014 (there may be 5 rows like this for the different 5 Series derivatives).

But if there 7 Series 2014 and 7 Series 2015 these would have their model updated to 7 Series 2014 and 7 Series 2015 respectively.

 

[vbaInet]

Here are the steps:
1. Create a query that counts how many years there are per model
2. In the query from step 1, use an IIF() function to say, if the count > 1 then return the "Model" & "Year" otherwise return the "Model".

 

[CJ_London]

this is one way to do it

 SELECT modtext & iif(dcount("modtext","myTable","modtext='" & modtext & "'")=1,"",model_Intro)
 FROM myTable

 

[houseofturner]

Hi

That's close, but as you can see from the attached, where there are multiple records with the same year, the year is incorrectly appended. I only want to append the year if the two years are different.

It's doing my head in

 

[CJ_London]

you've changed the dataset, there is an additional column so I'm not surprised.

I really don't want to advise a solution and you then move the goalposts again so please confirm this is your final dataset and you will not be adding any more 'ah buts'. If it is not your final dataset, please provide it with all the 'ah buts'

 

[houseofturner]

Hi

Sorry for any confusion, no more changes - promise!

 

[CJ_London]

try this

SELECT myTable.model_intro, myTable.modtext, myTable.Derivative, [mytable].[modtext] & IIf([countofmodtext]=1,""," (" & [model_intro] & ")") AS NewModel
FROM myTable INNER JOIN (SELECT T.modtext, Count(T.modtext) AS CountOfmodtext
FROM (SELECT DISTINCT modtext, model_intro
FROM myTable)  AS T
GROUP BY T.modtext)  AS CT ON myTable.modtext = CT.modtext;

 

[houseofturner]

That's great thanks!