Table help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I considered myself to be mediocre when it comes to using Access. There are
some things that I know, however I wish that I could learn so much more from
someone online. Anyhow, I have three identical tables with many fields; I’ve
tried many ways to see if I could break down this table into smaller tables,
though I just get so frustrated & continue working with this large table.
Now I have duplicated the table into 2-other tables & I would like to know
how to connect (or link) these 3-tables together whereby if I make changes in
one table automatically the changes will occur in the other 2-tables
 
I considered myself to be mediocre when it comes to using Access. There are
some things that I know, however I wish that I could learn so much more from
someone online. Anyhow, I have three identical tables with many fields; I’ve
tried many ways to see if I could break down this table into smaller tables,
though I just get so frustrated & continue working with this large table.
Now I have duplicated the table into 2-other tables & I would like to know
how to connect (or link) these 3-tables together whereby if I make changes in
one table automatically the changes will occur in the other 2-tables
Click to expand...

WHOA.

If you have the same fields in three different tables, and want
changes in one table to be reflected in other tables... and ALSO if
you have "many fields" (more than 30 say) in any table... you are off
on the wrong track.

Try reading some of the links at

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

in particular the Database Design 101 links on Jeff's site; or if you
wish, post some representative fieldnames, datatypes and significance
from your tables.

Note that information should be stored ONLY ONCE; if you have "the
same" information in two different tables, that is redundancy, and
redundancy should be avoided, because it's redundant and you want to
avoid redundant redundancy! <g>

John W. Vinson [MVP]
 
Unless I misread your response, I believe that you offered me to give some
examples of my feilds, data types, & descriptions of my table.

For purely recreational I enjoy following NASCAR & tracking my own stats by
using Access. Here is one of my Tables:

Race Results
1. ID -- AutoNumber
2. St -- Number -- *Starting Position
3. Fin -- Number -- *Finishing Position
4. Car-# -- Text
5. R -- Text -- *Indicates Rookie Driver
6. Driver -- Text
7. Make -- Text
8. Sponsor -- Text
9. Points -- Number
10. Bonus -- Number -- *For leading a lap or leading the most laps
11. Laps -- Number
12. Claps -- Number -- *Indicating ran all of designated laps in race
13. Winnings -- Currency
14. Status -- Text -- *I.E. was the car Running, accident, engine failure,
etc
15. DNF -- Number -- *Indicates Did Not Finish (the race)
16. Pole -- Number -- *Started the race in the first position
17. Win -- Number -- *Winner of the Race
18. Top 5’s -- Number -- *Finished within the Top 5 position
19. Top 10’s -- Number -- *Finished within the Top 10 position
20. Owner -- Text -- *Owner of the Car #

• = Description

Can this one big table be broken down into smaller multiple tables & might
someone be willing to make suggestions?
 
Unless I misread your response, I believe that you offered me to give some
examples of my feilds, data types, & descriptions of my table.

For purely recreational I enjoy following NASCAR & tracking my own stats by
using Access. Here is one of my Tables:

Race Results
1. ID -- AutoNumber
2. St -- Number -- *Starting Position
3. Fin -- Number -- *Finishing Position
4. Car-# -- Text
5. R -- Text -- *Indicates Rookie Driver
6. Driver -- Text
7. Make -- Text
8. Sponsor -- Text
9. Points -- Number
10. Bonus -- Number -- *For leading a lap or leading the most laps
11. Laps -- Number
12. Claps -- Number -- *Indicating ran all of designated laps in race
13. Winnings -- Currency
14. Status -- Text -- *I.E. was the car Running, accident, engine failure,
etc
15. DNF -- Number -- *Indicates Did Not Finish (the race)
16. Pole -- Number -- *Started the race in the first position
17. Win -- Number -- *Winner of the Race
18. Top 5’s -- Number -- *Finished within the Top 5 position
19. Top 10’s -- Number -- *Finished within the Top 10 position
20. Owner -- Text -- *Owner of the Car #

• = Description

Can this one big table be broken down into smaller multiple tables & might
someone be willing to make suggestions?
Click to expand...

Yes, it certainly can.

A Car is one type of Entity. It has a make, an owner, etc.
A Race is not a Car. It is a different type of entity - it has a
RaceDate, a location, etc.
An Entry is not a Race, nor is it a Car - it is the relationship
between a Car and a Race, that is, what *this* car did in *this* race.

I'd suggest at least the following tables:

Owners
OwnerID <Autonumber Primary Key>
OwnerName
<other information about the owner as a person or company>

Drivers
DriverID <Autonumber Primary Key>
LastName
FirstName
Rookie Y/N
<other biographical data, e.g. birthdate, contact information, etc.>

Cars
CarNumber <primary key; if NASCAR has one use it>
Make
ModelYear
<other information about the car, e.g. engine size, horsepower, etc.>

Venues
VenueName <Text Primary Key>
(information about this particular track if appropriate)

Races
RaceID <Autonumber, Primary Key>
VenueName <Text>
RaceDate <Date/Time>
<other fields about this race as an event>

Entries
EntryID <Autonumber Primary Key>
RaceID <link to Races>
CarID <link to Cars>
DriverID <link to Drivers>
Sponsor -- Text
Points -- Number
Bonus -- Number -- *For leading a lap or leading the most laps
Laps -- Number
Racetime -- Float or Double number, seconds on the track
Winnings -- Currency
Status -- Text


Other fields, such as Claps, DNF, Win, can be calculated on the fly.

John W. Vinson [MVP]
 
Back
Top