If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database Design
Hi, I wonder if anyone can help. I am trying to create a bowls pairs league
database. At the moment I have 6 tables, league, fixture, game detail, team names, players names & score which are linked in relationships league (can have many fixtures), fixtures (can have many game details). The league table consist of league ID (primary key) & league. The fixture table consist of fixture ID (primary key, league foreign key, date, league, team 1 & team 2 (both team 1 & team 2 are look up's from the team names table( I know people say you should not do this but I can't get my head round it to do it any other way??)). It is the same with the games detail table which consist of game detail ID (primary key), fixture ID (foreign key), names 1, names 2, score, names 3, names 4 & score. Again the 4 names are from look up's, as are the score's!!! My main problem is that players (names) do not play every game & not always with same partner. I have tried joining the names with a union query?? (is this the right way to do it or is their some other way. By the way, the fixture game detail consist of 2 teams, each consiting of 2 pairs, the games are 21 up (hence the score table). If say team 1 has both winners then they get 3 points, one for each winner & 1 for the aggrigate Hope someone can help me in this matter. |
#2
|
|||
|
|||
Database Design
My head was spinning with confusion after reading this. My first guess is
that your issue is a cart-before-the-horse situation. (plus I don't know bowling) A couple thoughts that might be helpful. Recommend thinking through, which of your things are ENTITIES that you want to record (these will become your tables), which things are merely one-to-one attributes of those entities (those will be additional fields in your tables), and which things can be recorded simply as a relationship between entities. The main work of creating a relationship is placing a FK (with the correct data) in a table and then the proper data into the FK. Then the "join" / union is just the last 1% of the process. I wasn't able to absorb you post well enough to definitively know your situation, but my fist guess is that leagues, fixtures, teams, pairs and players. are your entities/tables, and in that "heirarchy" meaning that each subsequent items is the "many" in a many-to-one relationship. And that scores are attributes (1 to 1 type data) of games and thus fields in the games table. Hope that helps a little. Fred |
#3
|
|||
|
|||
Database Design
"Fred" wrote: My head was spinning with confusion after reading this. My first guess is that your issue is a cart-before-the-horse situation. (plus I don't know bowling) A couple thoughts that might be helpful. Recommend thinking through, which of your things are ENTITIES that you want to record (these will become your tables), which things are merely one-to-one attributes of those entities (those will be additional fields in your tables), and which things can be recorded simply as a relationship between entities. The main work of creating a relationship is placing a FK (with the correct data) in a table and then the proper data into the FK. Then the "join" / union is just the last 1% of the process. I wasn't able to absorb you post well enough to definitively know your situation, but my fist guess is that leagues, fixtures, teams, pairs and players. are your entities/tables, and in that "heirarchy" meaning that each subsequent items is the "many" in a many-to-one relationship. And that scores are attributes (1 to 1 type data) of games and thus fields in the games table. Hope that helps a little. Fred Sorry for not getting back earlier Fred, but have been laid up with a bug. Anyway, before I start, can I thank you for the reply. I will try to simplify things (if that is possible with me!!) My main problem is that I have 4 fields in my game detail table, name1, names2, names3 & names4. All of these fields are drawn from 1 table, players names via look ups. My main question is, is it possible to combine the 4 field into 1 via a query, i.e. 4 columns of say 10 rows into 1 column of 40 in the query. Once again thanks for the reply. |
#4
|
|||
|
|||
Database Design
Hello Harvey,
Taking your question literally, I don't know how to do that and it is rare because usually only a mis-designed database would require something like that. - - - Assuming that there is no difference between the 4 fields in your games table (and your "combining" question supports that presumption) changing your design to proper normalization will make what you want to do easy. Make a new "participation" table which has a record for each instance of a person participating in a game. It can have as few as two fields: PersonIDnumber GameIDnumber So, recording a typical game would add 1 record to your games table and then 4 teeny records to your participation table. After copying/moving the participation data over, and backing up, delete those 4 name fields from your games table. Now your lists are "pre-combined" and you can select for whatever game or people criteria that you wish and get the list that you describe. Sincerely, Fred |
#5
|
|||
|
|||
Database Design
On Fri, 2 Jan 2009 11:50:05 -0800, Fred
wrote: Taking your question literally, I don't know how to do that and it is rare because usually only a mis-designed database would require something like that. You can use a Union query. That joins tables (in this case your one table four times) "vertically" instead of "horizontally". That would give you your desired result of 1 column of 40 rows. Look up Union queries in Help to see how to do it. You can't design a Union query in the query designer - you'll need to switch to SQL view. Armen Stein Microsoft Access MVP www.JStreetTech.com |
#6
|
|||
|
|||
Database Design
"Armen Stein" wrote: On Fri, 2 Jan 2009 11:50:05 -0800, Fred wrote: Taking your question literally, I don't know how to do that and it is rare because usually only a mis-designed database would require something like that. You can use a Union query. That joins tables (in this case your one table four times) "vertically" instead of "horizontally". That would give you your desired result of 1 column of 40 rows. Look up Union queries in Help to see how to do it. You can't design a Union query in the query designer - you'll need to switch to SQL view. Armen Stein Microsoft Access MVP www.JStreetTech.com Thanks for that Armen, I tried the union query and it gave me near perfect results. I say near because at the moment I have a list of 71 players who have played in 25 games, not all the players have played equal games, some have played 1, some 2, and others even 4 or 5. Out of the list of 71 names in union query about 80% of the information was correct. Any ideas why? |
#7
|
|||
|
|||
Database Design
On Sun, 4 Jan 2009 09:04:00 -0800, Harvey
wrote: Thanks for that Armen, I tried the union query and it gave me near perfect results. I say near because at the moment I have a list of 71 players who have played in 25 games, not all the players have played equal games, some have played 1, some 2, and others even 4 or 5. Out of the list of 71 names in union query about 80% of the information was correct. Any ideas why? Hi Harvey, First, what newsgroup client are you using? Your reply is prefixed with a symbol, which mixes it in with quoted text. And your lines aren't word-wrapping, making them hard to read. Anyway, back to your question. When you say that 80% of the results are correct, what's wrong with the 20%? That sounds suspiciously close to one of your 4 Select statements in your Union query. Have you double-checked it to make sure that you are querying all four different columns, one for each Select? If you like, you can indicate which player the record came from by using literal field values, like this: Select Player1, "Player1" as PlayerNumber From MyGameTable UNION Select Player2, "Player2" as PlayerNumber From MyGameTable UNION ....etc. Also, Unions by their nature remove duplicates, unless you specify UNION ALL. You might want to try this to see the difference. Hope this helps you figure it out, Armen Stein Microsoft Access MVP www.JStreetTech.com |
#8
|
|||
|
|||
Database Design
"Armen Stein" wrote: On Sun, 4 Jan 2009 09:04:00 -0800, Harvey wrote: Thanks for that Armen, I tried the union query and it gave me near perfect results. I say near because at the moment I have a list of 71 players who have played in 25 games, not all the players have played equal games, some have played 1, some 2, and others even 4 or 5. Out of the list of 71 names in union query about 80% of the information was correct. Any ideas why? Hi Harvey, First, what newsgroup client are you using? Your reply is prefixed with a symbol, which mixes it in with quoted text. And your lines aren't word-wrapping, making them hard to read. Anyway, back to your question. When you say that 80% of the results are correct, what's wrong with the 20%? That sounds suspiciously close to one of your 4 Select statements in your Union query. Have you double-checked it to make sure that you are querying all four different columns, one for each Select? If you like, you can indicate which player the record came from by using literal field values, like this: Select Player1, "Player1" as PlayerNumber From MyGameTable UNION Select Player2, "Player2" as PlayerNumber From MyGameTable UNION ....etc. Also, Unions by their nature remove duplicates, unless you specify UNION ALL. You might want to try this to see the difference. Hope this helps you figure it out, Armen Stein Microsoft Access MVP www.JStreetTech.com Armen, thank you very much for that, union all did the trick. Now I have perfect players info i.e. games played, won & lost. Like you said, union on it's own removed info. Once again thank you very much. Also can I thank Fred for his input. Cheers everyone. |
Thread Tools | |
Display Modes | |
|
|