creating query

[Guest]

I am getting a message about having an ambiguious outer joinin one attempt
and duplicate records in another.

I have a database containing information from all of the football plays in a
game.

I have many tables but 4 of them are "main", "formation", "play",
"playdiagram"

The main table has the following fields
[mainid]
score
time
formationid
runid

the formation table has many fields including
[formationid]
formationfamily
backfield
variation
strength

the play table has the following fields (it consists of both a formation and
a play)
playid
formationid
runid
playdiagramid

the playdiagram table has the following fields (there can be many different
ways to run the same play)
playdiagramid
playdiagram

This is really a query so that I can have one form and enter all of my
information on that one form. There for I am trying to link the tables
together. The main table should be the table with many records, the formation
table should be linked to the main table via [formationid] on both tables.
This I have done and have had no problem with.
The play table should be linked to the main table with both the
[formationid] fields as well as the [runid] field. This is where my problem
is. I have tried to create a relationship with arrows on a one to many
relationship and it will not work.

The last step would be to link the play and playdiagram fields together.
This would be done through the [playdiagramid] field.

In the query that I preformed I got duplicates for each record in my
results. (2 records of each record) or when I get real number of records
entered in the database I am not able to enter information into the play
table or the playdiagram table.

I hope this makes sense and I greatly appreciate your suggestions for a
better way to do it or your input. Thanks for your time! This problem is
driving me close to crazy!

 

[Steve Schapel]

Fipp,

Before you go crazy...

First of all, I think your table design is going to need a revision. I
am not clear about exactly what you are working with here. For example,
I don't knw how runid comes into anything. But it appears that the play
table is on the "many" side of relationships with 3 other tables. As
such, I am surprised to see the foreign keys to two of those tables also
in the main table. I would have expected to see the playid field in the
main table instead.

But in any case, the main point here is that when you have a number of
tables related beyond a simple one-many-many configuration, any query
based on these tables is unlikely to be updateable. Your goal to use
one unifying query as the basis of your data entry into all these tables
at once, is unrealistic. The most common way to represent such table
relationships for data entry via forms is to use subforms for the
many-side data.

--
Steve Schapel, Microsoft Access MVP

I am getting a message about having an ambiguious outer joinin one attempt
and duplicate records in another.

I have a database containing information from all of the football plays in a
game.

I have many tables but 4 of them are "main", "formation", "play",
"playdiagram"

The main table has the following fields
[mainid]
score
time
formationid
runid

the formation table has many fields including
[formationid]
formationfamily
backfield
variation
strength

the play table has the following fields (it consists of both a formation and
a play)
playid
formationid
runid
playdiagramid

the playdiagram table has the following fields (there can be many different
ways to run the same play)
playdiagramid
playdiagram

This is really a query so that I can have one form and enter all of my
information on that one form. There for I am trying to link the tables
together. The main table should be the table with many records, the formation
table should be linked to the main table via [formationid] on both tables.
This I have done and have had no problem with.
The play table should be linked to the main table with both the
[formationid] fields as well as the [runid] field. This is where my problem
is. I have tried to create a relationship with arrows on a one to many
relationship and it will not work.

The last step would be to link the play and playdiagram fields together.
This would be done through the [playdiagramid] field.

In the query that I preformed I got duplicates for each record in my
results. (2 records of each record) or when I get real number of records
entered in the database I am not able to enter information into the play
table or the playdiagram table.

I hope this makes sense and I greatly appreciate your suggestions for a
better way to do it or your input. Thanks for your time! This problem is
driving me close to crazy!