Compare tables

[Svenne]

Dear forum members,

I have a question concerning the same information in different tables. Assume the following:



In one table I have a membership register of some youngsters who plays ice hockey. In another table I have the same membership register of youngsters who play football. Some of these people plays both ice hockey and football and consequently appears in both tables.

Is there any way I could get a report of which ones who plays both football and ice hockey? Some kind of query or something else?

Regards,
Svenne

[Welshgasman]

Really should have a table for players, a table for games and a junction table that connects to both.

Look at creating a join in the query, where keys match. Use the query builder.
Hopefully the player keys are the same?

[Bob Fitz]

Create a query that has both tables and join them on the field(s) that identify each person, in each table.
So if you have a similar Primary Key value in each table you could join them on that field.
If not that then you will have to use whatever name fields you have, but the Bob Fitz in one table may not actually be the same Bob Fitz that is in the second table.
an you post a copy of the db.

EDIT
I've just seen Welshgasmans post and I have to agree that restructuring the tables would be your best option.

[isladogs]

Off topic but I'm wondering how this thread got 2 replies without ever being viewed ...

[Micron]

Off topic but I'm wondering how this thread got 2 replies without ever being viewed ...

I've seen that a gajillion times and learned to ignore it. Must be a quirk of Model T forum software.

[Welshgasman]

Now corrected.

[Svenne]

Thanks for all the answers. The following is an example:

Table 1: Football
Columns: Name, First Name, Birth year

Table 2: Icehockey
Columns: Name, First Name, Birth year

Table 1 consists of 40 posts, table 2 - 29.

15 people appears in both tables.

What I would want to do is to, if possible, have a report with the names of those 15.

[Welshgasman]

Use the query wizard and look at each of the join types.
I would expect you would need to join on name and year?

God help you if you have two of the same name in the same year.

[ssanfu]

@Svenne,
Welshgasman gave you a better design in Post #2.

Consider:

tblPlayers
------------
PlayerID_PK Autonumber
Firstname Text
LastName Text
DateOfBirth DateTime
(other fields to make each person unique)

tblGames
----------
GameID_PK Autonumber
GameName Text

jnctPlayersGames
-----------------
PlayerGameID_PK Autonumber
PlayerID_PK Number - Long Integer (Link to Player table)
GameID_PK Number - Long Integer (Link to Games table)
(other fields as desired)



Now you can have an an almost unlimited number of unique players and any/all the games you want.
If you add two date fields (StartDate and EndDate) in the junction table, you can have a history of who was in what game(s) and when.

You can have a main form of Players with a sub form to show/select the games.
Or you can have a main form of Games and a sub form of Players.

Then it should be easy to find the players that are in both football and Ice hockey.

[Svenne]

@Svenne,
Welshgasman gave you a better design in Post #2.

Consider:

tblPlayers
------------
PlayerID_PK Autonumber
Firstname Text
LastName Text
DateOfBirth DateTime
(other fields to make each person unique)

tblGames
----------
GameID_PK Autonumber
GameName Text

jnctPlayersGames
-----------------
PlayerGameID_PK Autonumber
PlayerID_PK Number - Long Integer (Link to Player table)
GameID_PK Number - Long Integer (Link to Games table)
(other fields as desired)



Now you can have an an almost unlimited number of unique players and any/all the games you want.
If you add two date fields (StartDate and EndDate) in the junction table, you can have a history of who was in what game(s) and when.

You can have a main form of Players with a sub form to show/select the games.
Or you can have a main form of Games and a sub form of Players.

Then it should be easy to find the players that are in both football and Ice hockey.

Hello Steve,

Thank you for this. I am a total beginner in this programme so if I ask silly questions, please forgive me. :-) You have explained it very well, but I have a question concerning the Players/Games table. When you say "Link to Player table" and "Link to Games table", do you mean creating a relation then?

Regards

[ssanfu]

Yes. The foreign key field links back to a primary key field. I name the primary key field of the one table with a suffix "_PK" and the many table has a field (foreign key field) with the suffix of "_FK". (one to many relationship)
And I always set RI.

Note that some developers do not set relationships in the relationship window.
I do because I set RI and when I create queries and add two tables that are related, the the relationship is automatically added in the query design grid. (I'm lazy???)

[isladogs]

I also set relationships using the relationships window.
Although having those appear in the query builder window is helpful, the main reason for setting relationships is to enforce referential integrity (RI).
Doing this gives greater control over what records can be entered and prevents orphaned objects
I wrote an extended 3-part article on the topic at https://www.isladogs.co.uk/relationships1/index.html. The first two parts will probably be useful to you.

[Svenne]

Yes. The foreign key field links back to a primary key field. I name the primary key field of the one table with a suffix "_PK" and the many table has a field (foreign key field) with the suffix of "_FK". (one to many relationship)
And I always set RI.

Note that some developers do not set relationships in the relationship window.
I do because I set RI and when I create queries and add two tables that are related, the the relationship is automatically added in the query design grid. (I'm lazy???)

Thank you for this. Considering I want to know only what players that play both football and icehockey (not what games) could this be an alternative solution?

tblFootball
------------
FootballID_PK Autonumber
FirstName Text
LastName Text
DateOfBirth DateTime
(other fields to make each person unique)

tblIcehockey
----------
IcehockeyID_PK Autonumber
FirstName Text
LastName Text

DateofBirth DateTime
(other fields to make each person unique)


jnctFootballIcehockey
-----------------
FootballIcehockeyID_PK Autonumber
FootballID_PK Number - Long Integer (Link to Football table)
IcehockeyID_PK Number - Long Integer (Link to Icehockey table)
(other fields as desired)

[Welshgasman]

No, terrible idea.
Just change Games for Sports as given by @ssanfu

[ArviLaanemets]

Considering I want to know only what players that play both football and icehockey...

In case you went with ssanfu's table structures (minus "_PK" part in my example, as ssanfu anyway mistyped in table jnctPlayersGames fields PlayerID_PK vs. PlayerID_FK and GameID_PK vs.GameID_FK ), you can get the list of players participating both football and icehockey running a query like (on fly):

SELECT p.FirstName, p.Lastname
FROM (((tblPlayers p LEFT JOIN jnctPlayersGames pg1 ON pg1.PlayerID = p.PlayerID) LEFT JOIN tblGames g1 ON g1.GameID = pg1.GameID) LEFT JOIN jnctPlayersGames pg2 ON pg21.PlayerID = p.PlayerID) LEFT JOIN tblGames g2 ON g2.GameID = pg2.GameID
WHERE pg1.GameName = "Football" AND pg2.GameName = "Icehockey" AND pg1.PlayerGameID Is Not Null AND pg2.PlayerGameID Is Not Null