Wrong Use Of Access? Suggestions Please.

[shazar]

Hey all,

I am attempting to use access to make a character creation form.

What I would like to do it have combo boxes to pick the:
Gender
Race
Class
Profession
Discipline1 through 4.

The trick is only some options are availible depending on what you picked before, and I only want the combo box to display what options are left after taking into account the previous selections.

I have tables created with the above sections and the fields in those are check boxes for if that options would be availible.

I'd like to know if access is the wrong tool for something like this, and if it would work maybe a push in the right direction would great!

Here is a link to what I am a working on. It is 14megs.

http://shazar76.home.comcast.net/SBPROJECT.mdb

Thanks for any help,
Patrick

 

[shazar]

another way to get the file

Here is a zipped attachment of what I'm working on.

 

[KeithG]

What you want to do is no problem in Access. Search the forum for cascading combo boxes. Basically what you have to do is filter the comboboxes once you make a selction in one of the combo's. But currently your major issue is normalization. You have your table structures setup incorrectly.

 

[shazar]

Thanks for giving me a keyword to do some searchs on. I really didn't know where to start.

I guess in my head and on paper it made sense to make the tables they way I did, but as you say it didn't fly in access so well.

Will I find out how to better arrange my tables looking into cascading combo boxes? Or is there additional information that might help?

Thanks again,
Patrick

 

[qdogfball]

Patrick,

http://support.microsoft.com/kb/209534

http://databases.about.com/od/specificproducts/a/normalization.htm

http://www.fabalou.com/Access/General/Normalization.asp

These are a good place to start understanding Normalization.

Access for a simple database, is very powerful and easy to use, with things Normalized.

It can get quite complex if you are programming it WITHOUT understanding and using Normalization!

Basically, you do not want redundant data. Set things up so that the information is input ONE time.

Ex) you do not want to have someone's name 2-5,000 times in different tables in a database. Once IS enough!

Access uses Relationships to help drive the setup of having one name and then that name is linked to many different records for that name. (ie a name (one) and their attendance records (many))

 

[shazar]

Need Another Push

Alright, I have read several documents about normalization now and can see how it will help a lot.... if I can figure out how to apply it.

The examples are all about stuff I am not working with.

Here is a snippet of my data:

Gender
Male
Female

Race:
Human
Elf
Dwarf

Class:
Fighter
Healer
Mage

Profession:
Warrior
Priest
Channeler
Warlock

Discipline:
Knight
Animator
Bladeweaver
Bounty Hunter
Dark Knight
Traveler

So following some rules of normalization I immediate see that I want to create some tables:

GenderID Gender
1 Male
2 Female
3 Either

RaceID Race
1 human
2 elf
3 dwarf

Now... everything can be male, but dwarfs can not be female so I add a field to the RaceID table right?:

RaceID Race GenderID
1 human 3
2 elf 3
3 dwarf 2

That seems simple... but now for Classes...
Any Gender can be Any Class
Dwarf can NOT be Mage

Then you get to Professions and it gets worse:
Warrior: any gender, any race, fighter class only
Priest: any gender, any race, healer class only
Channeler: any gender, can’t be dwarf, can’t be fighter
Warlock: male only, can’t be dwarf, can’t be healer.

Then there are the disciplines which are dependant on everything else:
Knight: male only, any race, fighter only, warrior only. Can’t be Dark Knight
Animator: ANY
Bladeweaver: any gender, elf only, any class, not warlock.
Bounty Hunter: any gender, any race, fighter only, warrior and warlock only.
Dark Knight: any gender, any race, fighter only, warrion only. Can’t be Knight.
Traveller: any gener, any race, can’t be fighter, can’t be warrior.

This is very simplified… there are many more of every category. I just don’t see yet how do make clean tables when so many things depend on each other. I am guessing that queries are the magic solution at some point, which is why I started the project with yes/no boxes for tables where I had one item vs another.

I just don't see how I can make a table that has all the rules for who can be what without duplicating data. Unless I am suppose to make a table for every case... that would be a lot of tables!

I’m trying hard to do the bulk of the work, so just give me another push!

Thanks again!
Patrick

 

[qdogfball]

Patrick,

Don't over think what you want to do

You were mostly on the right track with your sample.

Create the Character table.

Just list out ALL the options that you can have for that each character.

Setup the Profession table with all the available professions. The Class table with all the classes. Do not worry about the exceptions that can happen, ie dwarves cannot be mages, etc.

On your main form, set it up using the Character table as "where the objects data comes from"

Now in design view, you add your fields to be selected using a combo box or list box.

You want the combo box to Look up values in your other tables, ie gender, class, or profession.

Now store that information in the gender class or profession field in the Character table.

Now the hard part is setting up your code to make sure your exceptions work or don't work.

But just get the info in the table, add more characters and then you can get on with the coding the exceptions.

 

[shazar]

keep it coming

Thank you for your continued replies!

Just to make sure I understand you correctly… I should create a separate table for each option that needs to be made like this:

tblGender
GenderID/Gender

tblRace
RaceID/Race

ect. to get them all associated with an ID number?

Then create a Character table that has all the ways the above tables can mix? That will be a huge data entry project if that's the case as there are 2 genders, 12 races, 22 Professions, and 47 Disciplines (and you can choose to take 4 of them) and they all may be dependent on each other.

But then you say to make it so that the combo boxes write to the Character table as well... so I guess I am definitely missing something.

Seems like there should be a way to utilize the yes/no tables I already created to generate something useful. It is far easier to go down a list and click the box for yes then it is to write out each option available.

Maybe you if you have a spare minute you could put some of the data I have listed in the last post in a table like you describe so I have a picture to go along with your words?

Thanks,
Patrick

 

[qdogfball]

Ok when I look at this, this is not a complex database.

This is a simple database.

Sorry if I have confused you.

You will not need to have ID numbers in the Gender, Class, Professions tables.

You just list the Professions in their table.

You will have one main table to enter data to. When you create the form, you have the combo box for professions save the data to your Character table.

See my example.

 

[shazar]

ah yes....

Thanks for the example!

That is actually pretty close to what I started with... but I scrapped it because it doesn't help me add any rules or dependencies about who can be what.

Getting the data in there is simple like you show!

How do you recommend getting it so only the correct choices show up in the next box based on what was selected before?

Seems like any way to do that would be long and involved... either a HUGE table that has every possibility, a butt load of quieries to handle every case, or a bunch of little tables that detail what each thing can be.

Thanks,
Patrick

 

[qdogfball]

Ahh!

There you will have to delve into Combo Boxes Based on Other Combo Boxes.

Take a look at those threads to give you an idea of what to do.

Or you could have a bunch of IF or Case statements in the Combo boxes, such as stating that since you selected a Dwarf, you cannot be a Mage, etc.