How do I have a second table pull info from the first table?

[Guest]

I have what looks like it is a many-to-many relationship that I need help with.
I have a main table that has a team name, wins (#) and losses (#). I have a
second table that will have the team name, and every team on their schedule
w/ wins and losses. I'd like to avoid having to type in every teams win and
losses on the second table, since they are all listed in the first table. So
for a visual reference here

Table One:
Team Win Losses
Dragons 3 3
Knights 2 1
Monkeys 1 6

Table Two:
Team Game 1 G1:Wins G1:Losses
Dragons Knights 2 1
Monkeys Monkeys 1 6

Now, the reason why I need this data twice, is that the teams will be
playing 8-10 games, and I need some calculations done based on the schedule
of the teams that they are playing. It would save me a huge amount of time
for where it says Game 1 in Table two, to be able to pull down a teams name,
and have their wins and losses automatically placed in the next two fields
based on the information in the first table.

I hope that this has enough information, thanks for the help

 

[Douglas J Steele]

You're right that you don't want to duplicate the data in a second table.
However, the problem would appear to be that you're storing the information
in the incorrect table. You shouldn't be storing wins and losses in Table
One. Table One should only contain an identifier for the team, and any
additional indicative information that's related to the team. You could then
create a query that returns that information and calculates the wins and
losses dynamically by looking at Table Two. You can then use that query
anywhere you would otherwise have used the table.

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

 

[Guest]

Ok, I see what you are saying. I guess what I wonder then is, does Access
have a COUNTIF type feature where I can tell it that if a certain field is a
W or L to add it up in a query?

 

[John Vinson]

Ok, I see what you are saying. I guess what I wonder then is, does Access
have a COUNTIF type feature where I can tell it that if a certain field is a
W or L to add it up in a query?

Not builtin, but it's easy to create one.

Create a Query based on your table. Put into it two calculated fields
by typing the following in two vacant Field cells in the query grid:

CountWon: IIF([fieldname] = "W", 1, 0)

CountLost: IIF([fieldname] = "L", 1, 0)

Make the query a Totals query and sum these fields, grouping by TeamID
or whatever field is appropriate.

John W. Vinson[MVP]