Need help with query join configuration (i think)

[beggerboi]

I am using microsoft access or doing anything related to databases for the first time for my dissertation, can't get my query to work because the end result has alot of duplicates of table three
Table one : 1234abcd
table two : 1234 efcg
table three: 1234 hijk
I am using a query with one - two - three joined together. all 3 tables shares the fields 1234, but i can't just join them by just 1 object each because there are so many so i have to join them by all 1234 to avoid duplicates.
What i need for my final product is 1234 from table one, ef from table two, and hijk from table three. When i do run the query however, 1234ef are all uniques, but some row values of hijk are duplicates. I have tried swapping the order and such for like 4 hours but i can't fix this problem. Might sound confusing as heck, i found it confusing just reading this text i typed but i dont know how to word it better. Below is a screen shot.
"imgur.com/a/W2Kz7"
Thanks

 

[plog]

I think you've painted youself into a corner with your field names and data examples. Everything is just too generic for me to grasp.

I think the best way to get us to help you is to post 2 sets of data:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results based on data in A. Show what you expect the results to be of your query when you feed it data from A.

 

[beggerboi]

Thanks for the reply!

So this data set is records of tennis matches

table 1 (games): ID of player 1, ID of player 2, Score results, tournament name, round number
table 2 (odds): ID of player 1, ID of player 2, tournament name, round number, odds for player 1, odds for player 2, 8 other kind of stuff i don't need (odds for more specific events than just who wins the series).
table 3 (stats): ID of player 1, ID of player 2, tournament name, round number, amount of serves of p1, amount of scores from the serves of p1, amount of serves of p2, amount of scores from the serves of p2, 6 more things i need, about 20 other things i dont need.)

What i want from the queries:
from table 1 - ID of player 1, ID of player 2, tournament name, round number
from table 2 (based on what comes out of table 1, table 2 and 3 has more data points than table 1) - odds for player 1 and 2 from the games of table 1
from table 3 - serves of player 1 & 2 plus the 6 things i need`

right now i have ID1, ID2, ID T, ID R all inner joined, my intuition is to have the query design flow from game -> odds -> stats as per the image linked, but i cant have the direction because of the ambiguous inner joints error. If i don't have any direction for the 3 tables in the query design, there are alot of duplicates of table 3 data, possibly due to the fact there are alot more data in table 3 than table 2 and 1 (as shown in image 2)

From youtube tutorials I see mostly that people only uses one inner join inbetween two tables, but the problem is that for this data you really need all ID1, ID2, IDT and IDR to differentiate between different game, sometimes even that snot enough (for example, there can be more than one set of data for 2 given players, in the same tourney, in the same round , i.e two dudes playing more than one game in a round)
Thanks again!

 

[plog]

You didn't provide me with what I wanted (sample data), but you did give me a better understanding of your structure, so I will critique that:

0--overall: This has been done a lot of times over. Search the google to see if you can find existing databases that either do what you want, or give you an idea of how to do it.

_______

1--table1:
A. I don't think you store players/scores in their own columns in the same record. Instead, you have 2 records--one for player1 and another for player2. Doing it that way let's you easily count how many rounds a player has played by checking just 1 field.

B. Because of A, you will need a new table to hold match ups. It would hold the tournament ID, the round and possible other data (court, date, time, etc.). It would have a primary key of an autonumber and then that ID (MatchID) would go into table 1 and tournament name and round number would come out of table1.

C. You most likely need a new table for tournament data (tournament name, tournament dates, etc).

______

2--table2:
A. odds of a player winning a match should go into the revised table1 I described above.

B. I'd really have to know what the other data points are to advise where they go.

____

3--table3:

A. Every table should have an autonumber primary key ID. That means the revised table1 gets one and then that ID becomes a foreign key in table3. With that key in table 3 you can now link it back to a player in table1 and would be the only filed you need to link table3 to table1.

B. Would need to know the other 26 things to advise where they would go.

_______

4--more overview:

A. Put down the queries. You need to get your tables structured correctly before moving onto queries and reports.

B. Read up on normalization (https://en.wikipedia.org/wiki/Database_normalization). Do a few online tutorials and then tackle the process with your data. You don't have it set up properly and need to before moving on.

 

[beggerboi]

Sorry, what does sample data mean? Is it screenshot of the tables or do you mean uploading the actual database? The database is like 140MB, not sure if you would actually want to download that big thing.

I tried so hard to try to understand what you mean for your tips but i am so confused T_T

Here are the screenshots of the tables "` imgur.com/a/aG1TF"

The reason i do this is because I want to analysis the relationships between betting odds and serving percentages. I found a paid software to do it called oncourt

Screenshots of the table i can export to excel " imgur.com/a/0h2J9"

You see on the table there is almost everything i want and need, except for the match stats such as serving percentages. To get to the match stats of each game, i need to right click the games. Since i can't input this manually thousands of times, I emailed them and they gave me the password to their database.

From my understanding every table is already kinda linked somehow, because of the fact that you can actually open the match stats from each game, its just not able to put it on the same table

Thanks I appreciate the help

 

[plog]

Sample data means data that is a sample of what you have. Suppose you had an employee directory database, a sample of it might look like this:

Employees
emp_ID, emp_Fname, emp_Lname, emp_Dept
1, Jane, Smith, Accounting
2, Bob, Jones, Sales
3, Steve, Fisher, Sales
4, Larry, Nichols, Accounting

As for my tips, the one you need to focus on is learning about normalization. All the other tips are specific examples of how you should normalize your data.