John Thanks for your answer Your solution is complety different from what I had in mind! I know that there is a fenomenon that's called subquery, but I've never used it before, so now it's a good time.
Thanks again Hein --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > 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=12hc704b4/M=362131.6882499.7825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123497245/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/
