Synchronize TWO bombo boxes to One

[jdncoke99]

Hi
I promise I have trawled the web, trying to self educate myself but to no avail. I am a complete beginner and desperate to build myself an expansive football database, from which I can run whatif scenarios based on criteria. I have built two tables, neither of which have a primary key and so are not related.

The first table (named Leagues) has two columns named; League and Team. I have popolated the table with 278 different team names from 16 different leagues, so the first 20 fields of the League column have Premier League in them and the first 20 fields of the Team column have the names of the 20 premiership teams and so on.

The second table has many columns for entering match statistics. The second column is named League, the third named Home Team and the fourth named Away Team. I wish to create a form with combo boxes for the above 3 columns. I want the League combo box to show the different leagues available (premier, league 1 etc.) not (premier, premier x20, then League 1, League 1 x24 etc.). I want the Home Team combo box to show the teams from that league and the Away Team combo box to do the same.
As I have said I have read numerous articles about synchronizing boxes using many different methods, but unfortunately I am not conversant enough to adapt the advice for my situation. I'm no longer even sure whether to use the form wizard lol. I have built and deleted this form many times, before finally asking for help on this very popular subject, so any pity would be gratefully recieved.
Sincerely
Kev

 

[ajetrumpet]

Have you looked at this???

 

[rainman89]

First things first. i would look into normalization. i think most of your headaches will go away if you do this.

a properly normalized database would allow you to do what you are speaking of quite simply

 

[jdncoke99]

Hi
Thanks for the speedy response. Rainman, I've looked up normalization and read about the wizard, this thought scares me, as it might take me down a completely new learning curve to the one I've been on. I built the table "Leagues" specifically to populate the three combo boxes, the table will never be added to it's just a reference. If you really think I should go down this route, could you elaborate a little more as to what will be the outcome and what will be required of me.
Ajetrumpet, thanks for the link. It does clear some of the confusion that has arisen from seeing all these examples in various places, but unfortunately I'm unable to gleen the knowledge from it, to apply to my particular needs.
kev

 

[Rabbie]

Hi
I promise I have trawled the web, trying to self educate myself but to no avail. I am a complete beginner and desperate to build myself an expansive football database, from which I can run whatif scenarios based on criteria. I have built two tables, neither of which have a primary key and so are not related.

It is not a good idea to rely on the position of a record in a table to match with a record in another table. Access tables have no intrinsic order of records. It all depends on how you index them or how you wish to show the data. In this case It woud be sensible in the team record to have the key of the corresponding league record stored as a FK(foreign key).

As Access is a relational database system it makes sense to use related tables. It is going to make your task easier.

Good luck!

 

[jdncoke99]

When I started the database, I could not think of a way of using a primary key as there are no unique values to any of my fields, are there?

 

[Rabbie]

When I started the database, I could not think of a way of using a primary key as there are no unique values to any of my fields, are there?

Surely the Teamnames are unique? Common practice is to have a separate field for the primary key like teamID or something similar defined as an autonumber to ensure uniqueness.

 

[jdncoke99]

So i give each team its own id number on one table. what happens on the second table when two teams will be on the same row? This is what steered me away from relating the two.

 

[Rabbie]

Without seeing your data model it is impossible to give you detailed advice. But from what you say, the second table would have the relevant teamID stored in the Home and the Away fields.
As with all Access databases it is important to get your table structure right before you you start designing forms, combo boxes etc. This will make your task easier in the long run.

As a general rule Access tables should be tall and thin rather than short and fat. By this I mean it is usually better to have a lot of short records in a table rather than a smaller number of long records.

You really must make sure your data is normalised. Failure to do so will just give you problems later on.

 

[jdncoke99]

My first understanding of this concept was that the primary key field had to be displayed on each table, which is what confused me. Can I use the normalization wizard as things stand or will i need to start from scratch and redesign my tables?
Thanks

 

[Rabbie]

My first understanding of this concept was that the primary key field had to be displayed on each table, which is what confused me. Can I use the normalization wizard as things stand or will i need to start from scratch and redesign my tables?
Thanks

That depends on how they are at present. I see you have read up on normalisation so you know what you are looking for. In practical terms you shouldn't need to normalise more than to 3NF. I am sure if you post your table structure here then people will give you constructive criticism.

 

[jdncoke99]

Thank you Rabbie. When you say "post your table structure" I'm not entirely sure what you mean, should I type a list of the field names in my tables or do you need to know how i've made them. Sorry, i'm not as dumb as i'm sounding right now, just the light bulb hasn't come on yet lol. And after four days of trying different approaches, things are starting to get blurred around the edges. What does 3NF mean?

 

[rainman89]

what he means by table structure is this...

table1
ID (pk)
fieldname

table2
ID(pk)
tbl1id(fk)
fieldname

etc

basically how do you have your tables set up currently?

 

[ajetrumpet]

What does 3NF mean?

Are you sure you've read up on normalization??? "3NF" is a common phrase when reading an article on it...

 

[jdncoke99]

Ok Ajetrumpet you got me. I read as far as "split your tables up" and that's when I got scared lol. I now know that table 1 would satisfy 3NF, not sure about table 2 yet as I'm not sure that all the fields are dependant on the primary key.
I think I should start again.
I want to create a database that will allow me to analyze trends in football match results. I have the data required, going back 3 seasons for 278 teams, in 16 different leagues (1,938 individual Records). I started by creating a table in design view with the following fieldnames;
Match Date, League, Home Team, Away Team, Home Form, Away Form, Form Difference, Home Index, Away Index, Index Difference, Home Current Index, Away Current Index, Current Index Difference, Home Trend, Away Trend, Trend Difference, Home Odds, Away Odds, Draw Odds, Total Goals, Supremacy, Home 1st Half Goals, Home 2nd Half Goals, Away 1st Half Goals, Away 2nd Half Goals, Home Score, Away Score, Score Difference, Total Score.
I was going to type the data into the table, until I discovered Forms and Combo Boxes. The "??? Difference" fields, will be calculated from the two preceeding fields. The rest will be typed in (from an outside source), apart from "League", "Home Team" and "Away Team", which I now realise, I can select from Combo Boxes. Whence my journey began.
So please, What do you all think of my structure? Should I be splitting this table up? Is my two table approach right? Should I start again and give the teams, ID No.s in the first table?
Thank you

 

[ajetrumpet]

I don't really think there is anything wrong with your table the way you have it now. Basically, you have a MATCH table, and along with the first column that specifies the match date, every other field gives detailed data about the match. That's really what creating tables is all about. So, go with it. You might want to assign an autonumber to the matches though, because I don't see any field in the list you provided above that is not subject to duplication at some point in time. A primary key value cannot be duplicated, and the autonumber would guarantee you this.

Also, you should probably set up your table with every field you have that is not going to be a calculated field. Then, query the entire table, along with the expressions to create the extra fields you want (for the calculations). It is probably best to then use the query as a source for any form you might use to input data into the table. If you manipulate data in a query, it changes in its source (the table) as well. The only kind of data that you cannot manipulate directly is the calculated stuff.

 

[jdncoke99]

I Normalized!

tblTeam
ID (PK)
Team

tblLeague
League
tblTeam_ID (FK)?

tblResults
Match Date, League, Home Team, Away Team, Home Form, Away Form, Form Difference, Home Index, Away Index, Index Difference, Home Current Index, Away Current Index, Current Index Difference, Home Trend, Away Trend, Trend Difference, Home Odds, Away Odds, Draw Odds, Total Goals, Supremacy, Home 1st Half Goals, Home 2nd Half Goals, Away 1st Half Goals, Away 2nd Half Goals, Home Score, Away Score, Score Difference, Total Score.

When I ran the analyze tool, it split the table and put in the ID fields. It also set up the relationship between the two new tables but not to my third.
tblResults is not normalized, as it has no data.
I've used the wizard to create my Form and my three combo boxes...

Name... cboLeague
Control Source... League
Row Source Type... Table/Query
Row Source... SELECT DISTINCT tblLeague.League FROM tblLeague ORDER BY [League];
Column Count... 1
Column Widths... 2.831
Bound Column... 1
After Update... ?????

Name... cboHomeTeam
Control Source... Home Team
Row Source Type... Table/Query
Row Source... SELECT tblTeam.ID, tblTeam.Team FROM tblTeam ORDER BY [ID];
Column Count... 2
Column Widths... 0;2.54
Bound Column... 1

Name... cboAwayTeam
Control Source... Away Team
Row Source Type... Table/Query
Row Source... SELECT tblTeam.ID, tblTeam.Team FROM tblTeam ORDER BY [ID];
Column Count... 2
Column Widths... 0;2.54
Bound Column... 1

I now want cboHomeTeam and cboAwayTeam to be filtered by the selection made in cboLeague. Any renewed support would be very much appreciated.
kev.

 

[jdncoke99]

Sorry Ajetrumpet, didn't see your post until i reposted myself, page 2 doh.
Are you saying that to create my calculated fields, I should removed them and the query will give me the option to put them back in? Is this to save creating them all individually?

 

[ajetrumpet]

Option to put them back in? I'm not sure what you mean by this, but it really doesn't matter. Actually, if you have so called 'calculated' fields in your table, how did you go about doing this? As in, what process are you using to produce the calculated numbers? Just wondering...

Anyway, my point was to use the queries for the calculations. It may be best to give you an example of what I do...

Say I am working on a new government project database, and I have these fields...

1) Project, 2) Grant Amount, 3) Total Cost, 4) Estimated Duration, 5) Funds Shortage/Overage

I would only enter into my table the first 4 fields, because #5 is a calculation (Grant Amount - Total Cost) . Then, I would query all 4 of these fields and add the expression relevant to #5 to show the additional calculated field (this would be my query recordset). Once I have that, I will never use the table again as a basis for anything (e.g., I would use the query to produce forms, reports, etc...) because all changes made to the query can be seen in the table as well. Plus, the query has everything that the table has, and then some!

So, in your case, you would really have two almost identical recordsets, just like I have described above.

As for your table structure now...to tell you the truth, I'm not even sure if you have enough data structure to be normalized!! You obviously can't assign a PK to a league the way you have it now, because there can't be just one league for every team, or there would be no matches played! If you want to keep it this way, I would just set up your league table like so...

tblLeague
Field 1 = autonumber (if you want) = PK
Field 2 = League Names

Use is as the ONE side of a one-to-many relationship with "tblTeams". Anyway, that's just a suggestion. The set up right now is not bad. Are you going to start working with your combo boxes? With the setup that you have now, or maybe after a few minor changes, you can make your combos cascade by copying the method I used in the FAQ sample. Have you looked through that too, or just the thread?

 

[jdncoke99]

To recap if I may.
I wanted the Table (tblResults) to record my data and then ask access to filter it in different ways, so I could discover trends. The data is spread over 278 teams in 16 different leagues. I want to input the data by Form. I would like to select the League from a combo box (done "cboLeague"). I would like to select the Home Team and Away Team by combo box but both filtered by League selection. I would like the "Form Difference" box to fill itself using the difference between the values that I enter in the "Home Form", "Away Form" boxes . Same goes for those named; Index Difference, Current Index Difference, Trend Difference, Score Difference and Total Score.
I needed tblTeam and tblLeague simply to populate the combo boxes. The Primary Key is an auto number which has been given to each teamname. This is linked in the relationships window to tblLeague via tblTeam Lookup.
I hadn't got as far as the calculation boxes yet, I've been stuck on the combo boxes.