How do I change a field by entering information in another field?

[Guest]

I am running a statistics database for my football club and I have one table
for the players name and details and then several other tables for the
various statistical categories.

I am looking to be able to select the players surname from a drop down list
(or type it direct in to the cell) on the statistics table and for the table
to automatically update the unique number (effectively their roster number
and a prefic) and their first name.

The first three columns of each table are the same; Roster Number, First
Name, Surname.

I am really struggling with this and any help would be really appreciated.

Thanks

 

[Joseph Meehan]

I am running a statistics database for my football club and I have
one table for the players name and details and then several other
tables for the various statistical categories.

I am looking to be able to select the players surname from a drop
down list (or type it direct in to the cell) on the statistics table
and for the table to automatically update the unique number
(effectively their roster number and a prefic) and their first name.

The first three columns of each table are the same; Roster Number,
First Name, Surname.

I am really struggling with this and any help would be really
appreciated.

Thanks

Before getting into the question you ask, lets look at your tables.

You have three or more tables. You start with the player table with the
player's demographics. Then you have additional tables somehow related to
the first table. Do each of these other tables contain data like sets of
data from games that would result in more than one record for a given player
or do they have one record for each player?

Why do you have several other tables?

We need to know the exact relationships between the tables before
answering the question.

I suspect the answer to your question is going to be found in the form
of a query.

 

[Guest]

Thanks for your reply Joseph

I know it is unusual but the tables are one-to-one as each player has only
one entry on each statistical category.

The tables are split into individual categories for example career
receiving, career rushing, current defensive, etc.

The main purpose of this was to try to avoid errors and credit somebody with
somebody elses stats. I gave each player (historical all time roster) a
unique number which is their playing number followed by an alphabet
character. The stats are then linked to that alphanumeric but I was trying
to find a way to get the player's name to also appear so that I could be sure
no errors were taking place.

I have uploaded the start of the database to the team's website and I don't
know if it will be any help! It is a 1.2Mb download and the address is:
http://www.southernsundevils.org.uk/Sundevils.mdb#

Thanks again

Toast

 

[Guest]

You should not have a one-to-one relationship. If that is the case, you do
not need multiple tables. Have you thought about keeping the all stat
information in a one separate table where you would have the player ID, stat
type, actual stat and year? I am just trying to anticipate future reporting
and I would think that would be advantageous to think along these lines.

 

[Joseph Meehan]

Thanks for your reply Joseph

I know it is unusual but the tables are one-to-one as each player has
only one entry on each statistical category.

The tables are split into individual categories for example career
receiving, career rushing, current defensive, etc.

The main purpose of this was to try to avoid errors and credit
somebody with somebody elses stats. I gave each player (historical
all time roster) a unique number which is their playing number
followed by an alphabet character. The stats are then linked to that
alphanumeric but I was trying to find a way to get the player's name
to also appear so that I could be sure no errors were taking place.

I have uploaded the start of the database to the team's website and I
don't know if it will be any help! It is a 1.2Mb download and the
address is: http://www.southernsundevils.org.uk/Sundevils.mdb#

Thanks again

Toast

Do read Jackie's response.

I will add something as well. Again I don't know your data, but it
would appear that you might be better off recording just the individual
statistical information for each individual for each event. Then use
queries, forms or reports to computer the seasonal and overall averages.
Storing those averages is generally a bad idea. Not only is Access usually
happier that way, but if you update an error, you don't need to go and find
all the results of calculations based on that one number.

I like the idea of a player table and a event results table. Unless you
have a lot of variables for the events (like maybe weather, location, other
team name etc.) I doubt if you would need more than two tables.

 

[Guest]

Thanks guys, I went for the one table approach. It is huge but I think in
the long run it is going to make updating the statistics easier ... I also
wisely left out things like averages and some of the totals that can be
calculated via queries.

Now for the next challenge, I dont suppose you guys know much about
connecting a database to a webpage?

Thanks

Toast

 

[Joseph Meehan]

Thanks guys, I went for the one table approach. It is huge but I
think in the long run it is going to make updating the statistics
easier ... I also wisely left out things like averages and some of
the totals that can be calculated via queries.

Now for the next challenge, I dont suppose you guys know much about
connecting a database to a webpage?

Not me, but there are people who do.