Working from a single table

[Poruchan]

Hi,

 

I believe I'm having a problem with an unidentified key concept. I hope to get pointed in the right direction.

 

I am attempting to build an English horse racing form here in Tokyo. I import all the data from Excel and am working from a single table (I believe this might be the root of my problem). 

 

I did a few tutorials and I felt pretty comfortable with the basics. At my first attempt (and I thought it was strange at the beginning), I gave every piece of info that would appear on the form its own field. With a possible 18 horses running in a race, and 5 lines of past performances per horse, I ended up with almost 2,000 fields. 

 

I filled in each past performance line through look-ups from the race id. It was pretty cool and at the same time obviously wrong. 

 

So I bought FMP12 The Missing Manuel, and while I feel as if I am getting through the text well enough (I can build an invoice!), when I come back to my project I have been lost -- to a point of great frustration. I moved to trial and error for a few days and decided to come here.

 

I have attached a sample form I borrowed from the Daily Race Form (and simplified it) to show what the end product would look like. I see it as a Mast Head, 18 Sub-headers (1 for each horse), and then 5 lines of past performances for each horse.

 

To get away from 2000 fields I figured I must have multiple TOs (18?) of the single table I have, then I figured I need portals for each horse to display the 5 past performances.

 

I've been working this angle to no avail and figured I must not have grasped a key concept.

 

From what I've written -- is the missing concept apparent?

 

Thanks,

Paul 

race_form_model.pdf

[comment]

To get away from 2000 fields I figured I must have multiple TOs (18?) of the single table I have

 

No. What you should have is at least two different tables (not occurrences of the same table): Horses and Performances, related by HorseID. In the Performances table, each performance (i.e. the participation of a horse in a race) needs to be a separate record.

 

Ideally, you should also have a third table, Races, so that you don't need to repeat the race data needlessly in the Performances table.

[Poruchan]

Thanks, I had gone down that line of thought quite a bit. I felt more comfortable working with more than one table in the tutorials and felt I could relate the concepts to my project only if I had my info separated out into different tables. I guess that's the missing key concept -- thinking that I could work out the problem from one table. 

 

Actually, I just culled the race data (track, date, distance, surface, class) from the excel worksheet the other day. And I believe I can continue that each week when I get the new race data.  I don't mind the extra prep work, but separating the horses is a mystery I need to solve. I have one source -- one file with new race results each week info each week.  

 

I'm going to get back to that -- I hope to report good news later. Thanks again -- I think you put me back on the right track.

[comment]

IMHO, you have two separate issues to work out here:

 

1. The correct structure of your solution;

 

2. Importing external data (which may not be structured as conveniently as you would like it to be) into your solution.

 

Once you have worked out the first issue, feel free to ask about the other one, preferably in a separate thread.