Database spaz needs Hayelp! Plzzzz I can haz?

[riddlemethis]

Hi techy types! I have a stoopidz rotten assessment piece that I have to do for Uni, building a very small Access dbase. I've populated two tables, allocated primary & foreign keys and have come to do a query & realise that I have NO FUCKING IDEA what I am doing here!!

One table holds data on Tennis Players (Name (primary key), age, Nationality, Rank, Tournament (foreign key)), the other holds Tournament data (Name (primary key), country, Date, Venue, Most recent winner).

Here's what I want to do - link the two so that when linked to Excel (a requ of the assessment) I can produce graphs/charts that show who of my player list won the most tournaments.

Either I've got my 1 to many relationship wrong to produce such an outcome, or I have no idea what a query is supposed to do. . . .either way HAYELP!!! :(

[cyclist]

I'm not a DB Expert, but I think that you are going to have to use a Join Statement of some description.

There is a reason I didn't continue with my DB Course this semester.

James

[robertkd]

so how have you defined the relationship of the tables?

Name is not the best choice of field for primary key, however from memory you can define the relationship between the two tables you'll want the table with the name and personal information to be on the one side and the table with tournaments to be on the many side.

the other way is simply use the join command (probably iner join) in your SQL query to join the tables for the sake of the query or even. ;)

[GenericBox]

YOur best bet would be to make a new field, "PID"/"PlayerID" -- set type to INT, and make that the UNIQUE PRIMARY ID.

Then also make the same field on te other table.

[Deanus-Maximus]

YOur best bet would be to make a new field, "PID"/"PlayerID" -- set type to INT, and make that the UNIQUE PRIMARY ID.

Then also make the same field on te other table.
I agree. Set your tables up with an independent unique identifier. e.g. Players.PlayerID(int, not null, unique) and TournamentResults.TournamentResultID(int, not null, unique). I believe Access has a means of controlling these internally as data is added. Also note the name change as this is more the function of the table you're proposing.

I'd question if having only these 2 tables is sufficient, were I doing it, I'd have FK'd tables on Country/Nationality, Tournament Name and Venue - all of which also use similar unique identifiers as described above. You'd also need to think about how these tables are related in terms of setting up the constraints on the FK's:


can one tournament have multiple results?
can each venue have multiple tournaments?
can each player win multiple tournaments?
can each tournament have multiple winners?
etc etc


While this complicates the structure, it makes for more accurate data storage and easier management of sublists (e.g. country, venues etc)
In your ODBC link to Excel you'd use one query with multiple joins and use the built in filters and pivot tables/graphs to do the rest:

SELECT *
FROM TournamentResults
INNER JOIN Players on TournamentResults.PlayerID = Players.PlayerID
INNER JOIN Tournament on TournamentResults.TournamentID = Tournament.TournamentID
etc etc...Spot my office's resident SQL guru :cool:

[riddlemethis]

Hey guys! Thanks so much for all of your responses via the questions they provoked I've been able to make it work & give the result I was after, so win! The hardest part about it is the simplicity that the tutors are asking for - they have to mark 600 of them, so I guess they don't want everyone getting tricky.

Deanus you made me laugh - do they give you a special t-shirt to wear with your title on it?!

[Deanus-Maximus]

Hey guys! Thanks so much for all of your responses via the questions they provoked I've been able to make it work & give the result I was after, so win! The hardest part about it is the simplicity that the tutors are asking for - they have to mark 600 of them, so I guess they don't want everyone getting tricky.

Deanus you made me laugh - do they give you a special t-shirt to wear with your title on it?!
Database design FTW! :p Glad to hear you got it finished

I wish they would, but for now I'm happy with my "imaginary sql guru hat" - I put that on every now and then :D