Cross-tab field naming problem after ranking

[Lightwave]

Dear All,

I'm trying to create a database to time races in Access 2003.

The background is that competitors (actually triathletes) start and then go round a lap or circuit coming back to the same location. Thus the raw information is race numbers and times. The race numbers are allocated to people and every lap a race number is given a time. I’ve managed to rank the times so that I get effective laps (using information from within here) my next stumbling block is having a (semi live cross tab query on the resultant lap table)

At a basic level I have two tables

TableParticipants
RaceNo
FirstName
Surname

And a further table which contains the times of each lap
TableLapTimes
RaceNo
LapTime

I then have a query that runs the following SQL
SELECT L1.*, (SELECT Count(L2.Raceno) from LapTimes as L2
WHERE L1.Raceno = L2.Raceno AND L1.Time> L2.Time)+1 AS LapNumber
FROM LapTimes AS L1;

This calculates the number of laps each competitor has done.
and returns the competitors
The Raceno
The LapNumber
and the LapTime

I was thinking that I would next create a cross tab query that would allow me to more efficiently identify how each competitor has done relative to the next.

This is where I’m hitting a problem there is something in the SQL that means that when I do this I get the following error message

The Microsoft Jet database engine does not recognize 'L1.Raceno' as a valid field name or expression.

The confusing thing is that the field marked RaceNo (subsequent to the SQL that calculates the laps) must have an alias nomenculture behind the scenes because if I do a make table query after the SQL and then run a cross tab on the made table there’s no problem. Does anyone have any pointers on how I could solve this problem. I’m guessing maybe re-write the SQL or possibly do it in a different way?

I haven’t got time at the moment but if people need I can post a simplified version of the database with error message.

Thanks in advance Mark

 

[jwhite]

Does your data as the Row Headers contain periods? If so, Access will convert them to underscores in the resulting field headers.

 

[Lightwave]

Well the SQL which names the headers does have dots but when you view the table the information produced by the SQL statement does not have dots or spaces either in the field names or the field values.

The SQL seems to be placing dots within the resultant information somewhere along the line although its not visible in the cross tab design query window or the SQL generated query.

 

[Lightwave]

I've managed to find a kind of work around to it where I have a button on the form that refreshes the make table and within the code I turn off the warnings. As the important information is actually in the initial two tables making and re-writing a table is not dangerous to the original information although I suspect that I am breaking some kind of rule by continually making a table rather than using a subset selection of information in the original table.

I'll keep you all posted as to how I get on..

I can afford to be a bit inefficient as record numbers are only likely to be small usually less than 300.