Can't figure out relationships to get query to work for data between two tables

[ITguy1981]

I'm trying to do a database for basketball that has two tables. One table is Teams and the other is Players. The Team table has a field "Team Name" and the players table has a field "This Year's Team". I'm trying to do a report based of a query. The query has five fields "Last Name", "First Name", "This Year's Team" which are from the Players table and "Conference" and "Division" from the team table. When entering criteria for lets say a conference I would like the query to list the first name, last name, conference entered, the division, and teams associated with the conference entered as the criteria. I just keep getting all blank fields when I run the query. I'm sure it's because I have no relationship between the tables and I'm not sure the best way to make one.

 

[plog]

Can you upload your database? Barring that, post the SQL of your query.

 

[ITguy1981]

Here is a copy of what I've done so far. Yes, I'm away it's probably not fundamentally built as it should be. I can make any suggested changes if needed and there's some help. I couldn't upload the zip file so here a link to my dropbox file of it. https://www.dropbox.com/s/l95lwvgljmczriv/NWBA Database.7z?dl=0

 

[plog]

Sorry can't open .7z files

 

[ITguy1981]

It's not opening or you don't have the program to open it? It's 7zip. I've tried uploading a folder and it's content to dropbox unzipped and it doesn't keep things in the right folders. The database has icons, images, etc with it. Can you open a rar file?

 

[ITguy1981]

Here is the SQL Code if you can go by it.
SELECT Players.[Last Name], Players.[First Name], Teams.Division, Teams.Conference, Players.[This Year's Team]
FROM Teams, Players
WHERE (((Players.[Last Name])="Is Null" Or (Players.[Last Name]) Like "*" & [Forms]![Roster_Search]![RosterLastNameSrch] & "*") AND ((Players.[First Name])="Is Null" Or (Players.[First Name]) Like "*" & [Forms]![Roster_Search]![RosterFirstNameSrch] & "*") AND ((Teams.Division)="Is Null" Or (Teams.Division) Like "*" & [Forms]![Roster_Search]![RosterDivisionSrch] & "*") AND ((Teams.Conference)="Is Null" Or (Teams.Conference) Like "*" & [Forms]![Roster_Search]![RosterConferenceSrch] & "*") AND ((Players.[This Year's Team])="Is Null" Or (Players.[This Year's Team]) Like "*" & [Forms]![Roster_Search]![RosterTeamNameSrch] & "*"));

A form with unbound text boxes is used to enter criteria or no critera which passes on to the query.

 

[plog]

How come your Teams and Players tables aren't related? Usually there is one table in the FROM clause and then others are added by joining them (LEFT JOIN, INNER JOIN). You've created a cartesian product, where every player will show on every team.

Also, what happens in year 3? You have 2 fields for what team the player is on, what happens each year? You should probably have a TeamAssignment table. That would allow you to store every player on every team for every year.

 

[ITguy1981]

Right now they are using an excel spreadsheet to keep track of the information and they keep the team information the same unless a player changes a team. Then they change last year and this year team. I don't see how players would show up on every team since you have a players table and "this years team" is the team that they play on. As of now there is no connection between the tables. That's what I need help with. At some point I should have had a one to many relation ship between one team and many players, but I can't figure out how to do it right.

 

[plog]

First, you need some primary keys. I would add an autonumber to both tables and name them [Team_ID] and [Player_ID] respectively. Then, in the Players table the [This Year's Team] (horrible name by the way, more on that in an inch) would become an numeric field and store the [Team_ID] value from Teams. That way your Primary key in teams becomes a foreign key in Players, thus linking them.

Then, in your SQL you would replace your FROM clause with this:

FROM Players INNER JOIN Teams ON Team.Team_ID = Players.[This Year's Team]

[This Year's Team] is a poor name because it has special characters in it (space and an apostrophe). You should only use alphanumeric characters and underscores in table and field names. Makes coding and querying a lot easier down the line.


Do you need historical data? Obviously you want the prior year's data for team assignments ([Last Year's Team]), but do you want to go futher back? Also, do you want a history of if a Team changes Divisions/Conferences?

 

[ITguy1981]

I'm thinking of just making a roster table and adding the primary keys from my current tables in it as foreign keys.

 

[ITguy1981]

Thanks for the reply. I'll get rid of the special character. I didn't like it myself, but they already had it that way and Access didn't complain about it so I left it. I don't believe they are doing much of the database for history, but more for just keeping track of current season rosters, team, and players.

 

[ITguy1981]

Actually, I don't know about a third table because even though I know what data I would want in it from both tables I don't have a primary key for a rosters table as rosters are more of a report generated from players and the team they play for.

 

[plog]

A roster table would look like this:

Rosters
Roster_ID, autonumber, primary key
Team_ID, number, links to Teams.Team_ID
Player_ID, number, links to Players.Player_ID

It would also have at least one field to denote when this occured. You could have a [Roster_Year] field which would hold a number to denote a year (2016, 2017, etc.). Or you could do it open-ended with 2 date fields. [Roster_Start] which would denote when a player first was put on the roster and [Roster_End] which would denote when a player left the roster--with a Null value meaning they are currently on the roster.

 

[ITguy1981]

Yay. That's exactly what I did minus the year, but I'll add that. I'm trying to do the relationships now, but the way the join type is worded is confusing. It was easier when I showed the infinite symbol on one side.

 

[ITguy1981]

Ideally I would like to be able to enter players in one table, enter team data in another table, and be able to print do a query to print out rosters based off query information such as print rosters of only a certain division, conference, etc. If you can help me with the base tables and report I can fill in the additional stuff. I'm really good at reverse engineering databases and adding to them. I wish I understood the concepts better to just build the thing the right way the first time without having to go back and change things. I have been making databases without relationships and just using queries to pull all of the info I need, but it's tricky when you need to query data from two different tables in to the same report.

 

[plog]

Your initial query should work with the updated FROM/INNER JOIN I provided:

SELECT Players.[Last Name], Players.[First Name], Teams.Division, Teams.Conference, Players.[This Year's Team]
FROM Players INNER JOIN Teams ON Team.Team_ID = Players.[This Year's Team]
WHERE (((Players.[Last Name])="Is Null" Or (Players.[Last Name]) Like "*" & [Forms]![Roster_Search]![RosterLastNameSrch] & "*") AND ((Players.[First Name])="Is Null" Or (Players.[First Name]) Like "*" & [Forms]![Roster_Search]![RosterFirstNameSrch] & "*") AND ((Teams.Division)="Is Null" Or (Teams.Division) Like "*" & [Forms]![Roster_Search]![RosterDivisionSrch] & "*") AND ((Teams.Conference)="Is Null" Or (Teams.Conference) Like "*" & [Forms]![Roster_Search]![RosterConferenceSrch] & "*") AND ((Players.[This Year's Team])="Is Null" Or (Players.[This Year's Team]) Like "*" & [Forms]![Roster_Search]![RosterTeamNameSrch] & "*"));

I'm just afraid this isn't properly structured for what you are going to ask of it in the future.