Try this out - I renamed the columns in the query for my own simplicity:
select r.min & ':' & r.sec & ':' & r.milli
from event e, results r
where e.heat = r.heat
and e.min & ':' & e.sec & ':' & e.milli > r.min & ':' & r.sec & ':' & r.milli
This will give you any records from the results table in which the results are less than the events (or event is greater than result). If an event heat is faster than the results heat, it will not be shown, per your requirements.
if the world record(events tbl) has been beaten by the result(results tbl) show any beaten records on screen.
Originally posted by forcer
i ended up with this but i think its still wrong.
SELECT Results.Min & ':' & Results.Sec & ':' & Results.Milli
FROM Events e, Results r
WHERE Events.Heat = Results.Heat
AND Events.Min & ':' & Events.Sec & ':' & Events.Milli > Results.Min & ':' & Results.Sec & ':' & Results.Milli
FROM Events e, Results r <this line i dindt understand much so i dont think i put that right.
apart from that i ran the query and i just get alot of boxes popping up where i'm supposed to enter data then once thats done it returns no results.
Ok - I assume you dont know SQL, so I should have explained this better. If you are going to put an alias in the FROM statement (e for events and r for results), in the SELECT statement, use those aliases instead of the actual table name per my example above.
The bottom line with the > is saying "only give me back records where MIS:MS from the results table is less than the MIS:MS from the Events table. Again, since you have an alias, use E and R respectively instead of the actual table name. Copy my query exactly, but just simply change the column names, dont change the aliases-
If you start a new query, go to SQL view, and paste this in it will work. i tested it this morning.