Filter query for consectuive data

[JimO]

I have a query called ConsectutivePolesQ. It lists the Indy 500 pole starters by year, one per year. I am trying to produce a list of drivers who had back-to-back poles. In excel I would just say if the driver equaled the previous driver that is a match. How can I produce a similar result in Access?



the query is ConsectutivePolesQ, and the fields involved are [Year], [Driver].

Thanks
Jim O

[CJ_London]

With databases, there is no such thing as first, next, previous, last without a definition of order. Even your statement

In excel I would just say if the driver equaled the previous driver that is a match

requires your data to be sorted (ordered) by year

I suspect the query would be more efficient working off the source to your ConsectutivePolesQ query, but would be something like

SELECT *, Exists(SELECT Driver FROM ConsectutivePolesQ Q WHERE Driver=ConsectutivePolesQ.Driver AND RaceYear= ConsectutivePolesQ.RaceYear-1) AS BacktoBack
FROM  ConsectutivePolesQ
ORDER BY RaceYear

Note Year is a reserved word and should not be used as a field name

[JimO]

Getting nowhere.

Set up a query of pole winners listed in order by race year and added an expression:

Consecutive: IIf(([Driver]=[Driver] And [RaceYear]=[RaceYear]-1),"yes","no")

I am getting a return value of 'no'.

Jim O

[CJ_London]

not sure what you are doing - I didn't suggest an iif function

Assuming you provided correct details, with the exception of year/raceyear, all you should need to do is copy and paste the code I provided into a new query

[JimO]

Ajax,
Thanks for the response.

I tried that with no success. I made a new query called PoleQ with [raceYear] and [Driver] and [Start] filtered as 1, made the appropriate changes for source (PoleQ) and I get the same list as PoleQ with the addition of a new field BackToBack with zeros as the value. So the query as it stands now has fields of [BacktoBack], [RaceYear], [Start], and [Driver]

I started playing with another query and an IIf expression just to see what would happen so that can be ignored.

Jim O

[CJ_London]

would be a big help if you posted the code you actually used

[JimO]

Here it is.

SELECT *, Exists(SELECT RaceYear, Driver FROM PoleQ Q WHERE Driver=PoleQ.Driver AND RaceYear= PoleQ.RaceYear-1) AS BacktoBack
FROM  PoleQ
ORDER BY RaceYear

[CJ_London]

looks OK to me, the exists function returns a Boolean of -1 or 0 (for true/yes or false/no) so now need to see some data which shows you have drivers in pole position for two consecutive years which are appearing as 0 (false/no)

[JimO]

Thanks for the breakdown of the code (the exists function).

How can I produce a value to show consecutive poles? That is what I was trying to do with the IIf expression. My very limited knowledge is near its limit.

Jim O

[CJ_London]

if your data is set up as described (i.e. Raceyear is an integer or long) then the code provided should do that. If RaceYear is a date datatype then you need to extract the year part of the date using the year function. i.e.

.....Year(RaceYear)= year(PoleQ.RaceYear)-1....

As previously suggested - show the results of your PoleQ query and indicate which ones should be marked as backtoback - i.e. the code I provided should return -1

[JimO]

Ajax,

Thank you very much for your time and help with this. I think it is now something I can work with.

Again Thanks!
Jim O

I tried to mark this as Solved but for some reason I cannot access the drop down in the advanced section. If the admin could mark this as solved I would appreciate it. Thanks.