Hein- In other words, you want to find the ones that are NOT correct.
SELECT SpeedSkate.* FROM SpeedSkate WHERE EXISTS (SELECT * FROM SpeedSkate As S2 WHERE S2.Position < SpeedSkate.Position AND S2.Time > SpeedSkate.Time AND S2.TournamentID = SpeedSkate.TournamentID AND S2.DistanceID = SpeedSkate.DistanceID) Or SELECT SpeedSkate.*, S2.* FROM SpeedSkate INNER JOIN SpeedSkate As S2 ON SpeedSkate.TournamentID = S2.TournamentID AND SpeedSkate.DistanceID = S2.DistanceID WHERE SpeedSkate.Position < S2.Position AND SpeedSkate.Time > S2.Time John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ > -----Original Message----- > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of heinvdvegt > Sent: Sunday, August 07, 2005 2:17 PM > To: [email protected] > Subject: [ms_access] Re: counting rows > > > John, > The reason I want this column that counts 1, 2, 3 etc is to check if > (in a speedskating competition) the combination time/position is > correct. In other words: I want to check if position 1 has a lower > time than position 2 etc. > The columns in my query are tournamentID, distanceID, time, > position. > What I want to do is to get both the time and position from row 1, > put them on row 2 and check if "if time1 < time2 and > position1<position2;"";"check time or positon". > > The reason for adding the tournamentID and distanceID is that for > each new distance and for each new tournament the cycle repeats > itself. > Simple and straightforward, easy to make in Excel, not so easy in > Access. > I hope the answer is easier than my other question (counting rows in > several queries) > Hope you can help me > Thanks again > Hein > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Hein- > > > > The simplest way to get a counter is to put your query into a > report, add a > > text box with a Control Source set to =1 and set the Running Sum > property to > > Over All. To do it in a query involves using a subquery or > DCount, but I'd > > have to know the names of your columns and the sequence (ORDER BY) > of the > > query. Post the SQL of the query to which you want to add this > count. > > > > John Viescas, author > > "Building Microsoft Access Applications" > > "Microsoft Office Access 2003 Inside Out" > > "Running Microsoft Access 2000" > > "SQL Queries for Mere Mortals" > > http://www.viescas.com/ > > > > > -----Original Message----- > > > From: [email protected] > > > [mailto:[EMAIL PROTECTED] On Behalf Of heinvdvegt > > > Sent: Friday, August 05, 2005 7:59 AM > > > To: [email protected] > > > Subject: [ms_access] counting rows > > > > > > > > > Who can help me with a problem that I would like to be solved. > > > > > > Is it possible to add in a query a column that counts 1, 2, 3 > etc all > > > the way to the last record. > > > > > > I can imagine a solution like "counter = counter + 1", but I > don't > > > know how to put it in code. > > > > > > Is it possible that, everytime you open the query it starts with > 1. > > > It > > > is not really necessary for my solution but it "looks better". > > > > > > The goal I have for my question is that in my database for > > > speedskating times I want to check if a higher position in a race > > > also > > > has a higher time > > > > > > Thanks in advance > > > Hein > > > > > > > > > > > > > > > ------------------------ Yahoo! Groups Sponsor > > > --------------------~--> > > > <font face=arial size=-1><a > > > href="http://us.ard.yahoo.com/SIG=12hjfh31q/M=362329.6886308.7 > > > 839368.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123253972 > > > /A=2894321/R=0/SIG=11dvsfulr/*http://youthnoise.com/page.php?p > > > age_id=1992 > > > ">Fair play? Video games influencing politics. Click and talk > > > back!</a>.</font> > > > -------------------------------------------------------------- > > > ------~-> > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > ------------------------ Yahoo! Groups Sponsor > --------------------~--> > <font face=arial size=-1><a > href="http://us.ard.yahoo.com/SIG=12h7op9er/M=362329.6886308.7 > 839368.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123449432 > /A=2894321/R=0/SIG=11dvsfulr/*http://youthnoise.com/page.php?p > age_id=1992 > ">Fair play? Video games influencing politics. Click and talk > back!</a>.</font> > -------------------------------------------------------------- > ------~-> > > > Yahoo! Groups Links > > > > > ------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12h6m53pq/M=362131.6882499.7825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123451672/A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org ">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet Life - brought to you by One Economy</a>.</font> --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
