January 20, 2005 at 7:30 am
Hello
I'm not sure if my question belongs in this forum (1st thread on this site) but I give it a go anyway.
In Access I'm building a database for holding speedskating times.
For those of you who are not familair with this kind of sport: there are single distance races and there are allround tournaments. The latter consists (usually) of 4 races (distances: 500, 5.000, 1500 and 10.000 meters). Besides these distances there are also races of 1.000, 3.000 etc meters. For easy filling in data I have made a table (TbTimesTemp) which holds all the different distances. This works by copy and paste from Excel
Design:
Distance1 Position 1 Distance 2 Position2 etc
RiderName1
RiderName2
However: since not everybody races all distances in 1 tournament this means that there are a lot of empty cells. To correct this I move (append) the data to a table (TbTimes) with a different design:
Rider1 distance1 position1
rider1 distance2 position2
rider1 distance3 position3
rider2 distance1 position1
rider2 distance2 position2 etc
For e.g. reports, however, I need to go back to the first setup (all distances and positions from 1 rider back on 1 row)
Question: can it be done, and How?
I hope somebody can help me
Thank
Hein
PS if more info is neede Im happy to make a demo ewhich will (hopefully) clarify things
January 20, 2005 at 9:00 am
You need to normalise your tables so that each entity is independent which would allow for you to create queries for any reports you might need. I will not get in to normalisation theory here but there are many available references. The main point is to separate your data into independent tables so that you will not have all the blank cells. As well it allows you to maintain the list of racers and events independently.
My suggestion is to create 3 tables
1. tablename: Rider
columns: riderId, riderName, riderdemographic....
2. table name: Event
columns: eventId, eventName, eventLength...
3. table name: Race
columns: riderId, eventId, raceDate, raceTime, racePosition....
(the 3rd table contains the id fields from the first 2 tables)
Now you can create queries, for example
a list of riders and there races:
select a.riderName, b.eventName, c.raceDate, c.raceTime, c.racePosition
from Rider a, Event b, Race c
where a.riderId = c.riderId and b.eventId = c.eventId
This can be done by using query designer to create the query graphically or by entering the sql view of a query and typing in the SQL statement like above. The basic idea is to join the 3 table together to produce your output.
This design should provide the flexibility to answer any questions about your data (through queries) as well as eliminate data redundancy and the 'blank cells'.
Good luck
January 21, 2005 at 7:11 am
Hello,
Using your table [TbTimes], you can run a CrossTab Query to get 1 line for each rider.
Heading Line: Rider Distance1 Distance2 ... etc, on colum for each distance
Data line 1: Rider1 Position1 Position2 ... etc, with NULL where Rider 1 did not race
Data line 2: Rider2 Position1 Position2 ... etc, with NULL where Rider 2 did not race
etc.
If your table [TbTimes] has these fields:
[rider]
[distance]
[position]
The query below will do the trick. In Access, open up a new Query, flip in to SQL design mode, and paste this code:
TRANSFORM Max(TbTimes.position) AS MaxOfposition
SELECT TbTimes.rider
FROM TbTimes
GROUP BY TbTimes.rider
PIVOT TbTimes.distance;
Bob Monahon
January 23, 2005 at 7:46 am
Thank you "golfer" and Bob for your reply.
Something has gone wrong with my previous answer (preview destroyed my entire text!!), so I will try again (without preview this time).
The last few days I've been busy and I've come up with the following solution
I've made 3 tables: TbRijdersTimes, TbRiders and TbTimes. Apart from these "core-"tables there is a TbTournamentData.
TbRidersTimes links to TbRiders. TbTimes links to TbRidersTimes on TimesTempID.
Both tables are fed from TbTimesTemp with queries.
To put data back together I don't use a crosstab query (why didn't I think of trying that??). The reason for this is that for each distance I need 3 dimensions (Time, position and remarks). In row heading you can have 3 dimensions but as far as I know in column headings you can have only 1 (?). Instead I made a query for each seperate distance.
For combining data I start with TbTournament, than TbRidersTimes, than TbRiders. After this I add the query for each seperate distance. I join each directly to TbRidersTimes, so if a rider didn't race each distance, the others are still in the result. After adding the queries I change each join-property to a left join. It works! For large amounts of data it is slow, but its use is intended for each seperate tournament and the amount of riders per race is limited, so performance will not suffer.
Problem is that I cannot change data, but using a crosstab query would have the same problem.
For changing data I need an other solution. I have already an idea so I have to work on that!
Thanks again
Hein
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply