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
|
|||
|
|||
Two related select querys in series. How to design tables?
Thanks @ advance.
Got two comboboxes in series. Firs combobox defines skill area e.g. Running. Second combobx uses the value selected in the first combobox to narrow the list of choices in the second combobox. Question: What is the most economical way of setting up the tables? I am currently repeating the primary key from the "Skill Area" table as a Foreign Key in the "Specific Skill Area Ability" table. This repeat of the info alows me to use a select query to fill the second combobox with only the skills specific to the skill area. e.g. 100 Meter Hurdle is a specific skill under the "Skill Area" of Running. Should I only have one table with the following rows. PrimaryKey. Skill SkillAbility But if I do this I will be repeating the Skill for each Skill Ability. This is counter to the point of Relational Data Bases? -- Anthony |
#2
|
|||
|
|||
Two related select querys in series. How to design tables?
On Wed, 5 Aug 2009 06:51:01 -0700, BaBaBo
wrote: Thanks @ advance. Got two comboboxes in series. Firs combobox defines skill area e.g. Running. Second combobx uses the value selected in the first combobox to narrow the list of choices in the second combobox. Question: What is the most economical way of setting up the tables? I am currently repeating the primary key from the "Skill Area" table as a Foreign Key in the "Specific Skill Area Ability" table. This repeat of the info alows me to use a select query to fill the second combobox with only the skills specific to the skill area. e.g. 100 Meter Hurdle is a specific skill under the "Skill Area" of Running. Should I only have one table with the following rows. PrimaryKey. Skill SkillAbility But if I do this I will be repeating the Skill for each Skill Ability. This is counter to the point of Relational Data Bases? Hi Anthony, Your two-table structure is the correct way. You wouldn't want to repeat all the Skill Area information (even the name) any more than necessary - just its primary key in the Specific Ability will suffice. Ideally your Skill Area primary key is something short and static, like an AutoNumber. Armen Stein Microsoft Access MVP www.JStreetTech.com |
#4
|
|||
|
|||
Two related select querys in series. How to design tables?
Dear Steve and Armen:
Thanks for your help, both usefull but I am getting this a bit ass about tit as we say it over in the UK. This is not to be used to enter info but used to populate dropdown lists. I will have already populated the fields with the information about the skills, and the skill ability. My question is concerned with setting up a look up table in the most efficent form. Can be that your answer still stands. But surely it is easier to create 1 table with 3 fields: Primary Key Skill ID Skill Name SKill Ability ID Skill Ability Ok I am did not go back and change what I had written so you could see my learning process. I will get right to it. I stated earlier I only needed 3 fields and I see now I need 5. This is a very simple problem when you look at it from entering the info. I was looking at it as a fill the combo boxes on the form using the info in the table to then populate the drop down lists. e.g. I have someone who wants to join the track team and for the first combobox I select the skill "Running". From the second combobox I am given the options that are only concerned with running, 100m, 400m, 1 mile etc. Thanks all so much. Thanks all so much for your help. -- Anthony "Steve" wrote: NEVER design your tables around how you want to display the data. Tables should be designed around the data; ie, normalized. TblSkill SkillID Skill TblSkillArea SkillAreaID SkillID SkillArea From the above you can create a form/subform for data entry. The main form will be based on TblSkill and the subform will be based on TblSkillArea. The LinkMaster and LinkChild properties will be SkillID. You can then ... 1. Enter a new skill in the main form 2. Add skill areas to a new skill in the subform 3. Add skill areas to an existing skill in the subform 4. Edit/Delete skill areas in the subform 5. Delete a skill and all associated skill areas in the main form Steve "BaBaBo" wrote in message ... Thanks @ advance. Got two comboboxes in series. Firs combobox defines skill area e.g. Running. Second combobx uses the value selected in the first combobox to narrow the list of choices in the second combobox. Question: What is the most economical way of setting up the tables? I am currently repeating the primary key from the "Skill Area" table as a Foreign Key in the "Specific Skill Area Ability" table. This repeat of the info alows me to use a select query to fill the second combobox with only the skills specific to the skill area. e.g. 100 Meter Hurdle is a specific skill under the "Skill Area" of Running. Should I only have one table with the following rows. PrimaryKey. Skill SkillAbility But if I do this I will be repeating the Skill for each Skill Ability. This is counter to the point of Relational Data Bases? -- Anthony |
#5
|
|||
|
|||
Two related select querys in series. How to design tables?
"Running" and such goes in TblSkill. 100m, 400m, 1 mile etc. go in
TblSkillArea. Your data entry form for these two tables is as previously described. TblTrackTeam looks like: TblTrackTeam TrackTeamID PersonID (from TblPerson or could be StudentID from TblStudent) SkillID SkillAreaID DateJoinedTrackTeam SkillID and SkillAreaID are both entered in comboboxes. As you said, the SkillID combobox will be designed to limit the choices in the SkillAreaID combobox. This is called "cascading comboboxes". If you don't know how to set them up, refer to mvps.org/access. Steve "BaBaBo" wrote in message ... Dear Steve and Armen: Thanks for your help, both usefull but I am getting this a bit ass about tit as we say it over in the UK. This is not to be used to enter info but used to populate dropdown lists. I will have already populated the fields with the information about the skills, and the skill ability. My question is concerned with setting up a look up table in the most efficent form. Can be that your answer still stands. But surely it is easier to create 1 table with 3 fields: Primary Key Skill ID Skill Name SKill Ability ID Skill Ability Ok I am did not go back and change what I had written so you could see my learning process. I will get right to it. I stated earlier I only needed 3 fields and I see now I need 5. This is a very simple problem when you look at it from entering the info. I was looking at it as a fill the combo boxes on the form using the info in the table to then populate the drop down lists. e.g. I have someone who wants to join the track team and for the first combobox I select the skill "Running". From the second combobox I am given the options that are only concerned with running, 100m, 400m, 1 mile etc. Thanks all so much. Thanks all so much for your help. -- Anthony "Steve" wrote: NEVER design your tables around how you want to display the data. Tables should be designed around the data; ie, normalized. TblSkill SkillID Skill TblSkillArea SkillAreaID SkillID SkillArea From the above you can create a form/subform for data entry. The main form will be based on TblSkill and the subform will be based on TblSkillArea. The LinkMaster and LinkChild properties will be SkillID. You can then ... 1. Enter a new skill in the main form 2. Add skill areas to a new skill in the subform 3. Add skill areas to an existing skill in the subform 4. Edit/Delete skill areas in the subform 5. Delete a skill and all associated skill areas in the main form Steve "BaBaBo" wrote in message ... Thanks @ advance. Got two comboboxes in series. Firs combobox defines skill area e.g. Running. Second combobx uses the value selected in the first combobox to narrow the list of choices in the second combobox. Question: What is the most economical way of setting up the tables? I am currently repeating the primary key from the "Skill Area" table as a Foreign Key in the "Specific Skill Area Ability" table. This repeat of the info alows me to use a select query to fill the second combobox with only the skills specific to the skill area. e.g. 100 Meter Hurdle is a specific skill under the "Skill Area" of Running. Should I only have one table with the following rows. PrimaryKey. Skill SkillAbility But if I do this I will be repeating the Skill for each Skill Ability. This is counter to the point of Relational Data Bases? -- Anthony |
#6
|
|||
|
|||
Two related select querys in series. How to design tables?
Dear Steve:
Well you helped me out no end. This is fantastic. I looked up Cascading Lists and found exactly the information. Funny the guy is postulating exactly what I was questioning the fastest way to build the tables. There is more than one way. Give a man an answere and you fix the problem, give a man a tearm to search and he learns the answer. Again this is the best answer I HAVE ever got. I got the info on how to build the cascading list boxes from: http://www.fontstuff.com/access/acctut10.htm Man this weeked is going to be fun. -- Anthony "Steve" wrote: "Running" and such goes in TblSkill. 100m, 400m, 1 mile etc. go in TblSkillArea. Your data entry form for these two tables is as previously described. TblTrackTeam looks like: TblTrackTeam TrackTeamID PersonID (from TblPerson or could be StudentID from TblStudent) SkillID SkillAreaID DateJoinedTrackTeam SkillID and SkillAreaID are both entered in comboboxes. As you said, the SkillID combobox will be designed to limit the choices in the SkillAreaID combobox. This is called "cascading comboboxes". If you don't know how to set them up, refer to mvps.org/access. Steve "BaBaBo" wrote in message ... Dear Steve and Armen: Thanks for your help, both usefull but I am getting this a bit ass about tit as we say it over in the UK. This is not to be used to enter info but used to populate dropdown lists. I will have already populated the fields with the information about the skills, and the skill ability. My question is concerned with setting up a look up table in the most efficent form. Can be that your answer still stands. But surely it is easier to create 1 table with 3 fields: Primary Key Skill ID Skill Name SKill Ability ID Skill Ability Ok I am did not go back and change what I had written so you could see my learning process. I will get right to it. I stated earlier I only needed 3 fields and I see now I need 5. This is a very simple problem when you look at it from entering the info. I was looking at it as a fill the combo boxes on the form using the info in the table to then populate the drop down lists. e.g. I have someone who wants to join the track team and for the first combobox I select the skill "Running". From the second combobox I am given the options that are only concerned with running, 100m, 400m, 1 mile etc. Thanks all so much. Thanks all so much for your help. -- Anthony "Steve" wrote: NEVER design your tables around how you want to display the data. Tables should be designed around the data; ie, normalized. TblSkill SkillID Skill TblSkillArea SkillAreaID SkillID SkillArea From the above you can create a form/subform for data entry. The main form will be based on TblSkill and the subform will be based on TblSkillArea. The LinkMaster and LinkChild properties will be SkillID. You can then ... 1. Enter a new skill in the main form 2. Add skill areas to a new skill in the subform 3. Add skill areas to an existing skill in the subform 4. Edit/Delete skill areas in the subform 5. Delete a skill and all associated skill areas in the main form Steve "BaBaBo" wrote in message ... Thanks @ advance. Got two comboboxes in series. Firs combobox defines skill area e.g. Running. Second combobx uses the value selected in the first combobox to narrow the list of choices in the second combobox. Question: What is the most economical way of setting up the tables? I am currently repeating the primary key from the "Skill Area" table as a Foreign Key in the "Specific Skill Area Ability" table. This repeat of the info alows me to use a select query to fill the second combobox with only the skills specific to the skill area. e.g. 100 Meter Hurdle is a specific skill under the "Skill Area" of Running. Should I only have one table with the following rows. PrimaryKey. Skill SkillAbility But if I do this I will be repeating the Skill for each Skill Ability. This is counter to the point of Relational Data Bases? -- Anthony |
#7
|
|||
|
|||
Two related select querys in series. How to design tables?
Hi Anthony,
I'm always glad to help someone out! Thank you for graciously showing your appreciation. If you need further help, post back here; I will be watching. Good luck! Steve "BaBaBo" wrote in message ... Dear Steve: Well you helped me out no end. This is fantastic. I looked up Cascading Lists and found exactly the information. Funny the guy is postulating exactly what I was questioning the fastest way to build the tables. There is more than one way. Give a man an answere and you fix the problem, give a man a tearm to search and he learns the answer. Again this is the best answer I HAVE ever got. I got the info on how to build the cascading list boxes from: http://www.fontstuff.com/access/acctut10.htm Man this weeked is going to be fun. -- Anthony "Steve" wrote: "Running" and such goes in TblSkill. 100m, 400m, 1 mile etc. go in TblSkillArea. Your data entry form for these two tables is as previously described. TblTrackTeam looks like: TblTrackTeam TrackTeamID PersonID (from TblPerson or could be StudentID from TblStudent) SkillID SkillAreaID DateJoinedTrackTeam SkillID and SkillAreaID are both entered in comboboxes. As you said, the SkillID combobox will be designed to limit the choices in the SkillAreaID combobox. This is called "cascading comboboxes". If you don't know how to set them up, refer to mvps.org/access. Steve "BaBaBo" wrote in message ... Dear Steve and Armen: Thanks for your help, both usefull but I am getting this a bit ass about tit as we say it over in the UK. This is not to be used to enter info but used to populate dropdown lists. I will have already populated the fields with the information about the skills, and the skill ability. My question is concerned with setting up a look up table in the most efficent form. Can be that your answer still stands. But surely it is easier to create 1 table with 3 fields: Primary Key Skill ID Skill Name SKill Ability ID Skill Ability Ok I am did not go back and change what I had written so you could see my learning process. I will get right to it. I stated earlier I only needed 3 fields and I see now I need 5. This is a very simple problem when you look at it from entering the info. I was looking at it as a fill the combo boxes on the form using the info in the table to then populate the drop down lists. e.g. I have someone who wants to join the track team and for the first combobox I select the skill "Running". From the second combobox I am given the options that are only concerned with running, 100m, 400m, 1 mile etc. Thanks all so much. Thanks all so much for your help. -- Anthony "Steve" wrote: NEVER design your tables around how you want to display the data. Tables should be designed around the data; ie, normalized. TblSkill SkillID Skill TblSkillArea SkillAreaID SkillID SkillArea From the above you can create a form/subform for data entry. The main form will be based on TblSkill and the subform will be based on TblSkillArea. The LinkMaster and LinkChild properties will be SkillID. You can then ... 1. Enter a new skill in the main form 2. Add skill areas to a new skill in the subform 3. Add skill areas to an existing skill in the subform 4. Edit/Delete skill areas in the subform 5. Delete a skill and all associated skill areas in the main form Steve "BaBaBo" wrote in message ... Thanks @ advance. Got two comboboxes in series. Firs combobox defines skill area e.g. Running. Second combobx uses the value selected in the first combobox to narrow the list of choices in the second combobox. Question: What is the most economical way of setting up the tables? I am currently repeating the primary key from the "Skill Area" table as a Foreign Key in the "Specific Skill Area Ability" table. This repeat of the info alows me to use a select query to fill the second combobox with only the skills specific to the skill area. e.g. 100 Meter Hurdle is a specific skill under the "Skill Area" of Running. Should I only have one table with the following rows. PrimaryKey. Skill SkillAbility But if I do this I will be repeating the Skill for each Skill Ability. This is counter to the point of Relational Data Bases? -- Anthony |
Thread Tools | |
Display Modes | |
|
|