Basic Question

[Dalagrath]

Hello, my name is Joshua Burton, but you can call me Dala or Dalagrath. I am starting a Data Entry position and I am very interested in finding out how things work in access in a whole. I have messed with Access (both 2007 and 2003) a few times before, and I am testing out some stuff with a Roleplaying Game I play. (I am using Windows XP and Access 2003 at work)



My question is this: Using a form, what would be the steps to create what I have described below?

I want to incorporate the user choosing a "Species" that has attributes attached to specific Species and an Ability Modifier that has a + or - value attached to that depending on the dice roll.

I currently have the Species and all attributes in one table, and the ability modifiers in another.

(If possible, I want the form to do the basic function of having the user enter values for his strength, dexterity, etc and his or her Species and have the form spit out a Overall (and eventually advanced information) Summary.)

[jzwp11]

Before working on forms, you have to come up with a table structure to hold the data that is displayed in the forms. The most critical parts of a relational database are the tables and the relationships between those tables (i.e the table structure). Structure design is governed by the rules of normalization. This site has a basic summary.



So my first question is what does your current table structure look like?

[Dalagrath]

Well I have attached what my current Table and Insert Form look like.

My end goal would have when you enter a weapon name through a Combo Box, all the data I want appears on the same form.

[jzwp11]

Concentrating soley on your table at this point, I see some things that need attention.

First, you have several weapons that have multiple types, and of course, the attributes change based on the type. With respect to the type, the type can apply to several different weapons. So a weapon can have many types which describes a one-to-many relationship. Conversely, a type can apply to several weapons, so another one-to-many relationship. When you have 2 one-to-many relationships between the same two tables, you need a juction table to represent the combinations, but actually yours is a little more complicated than that (see below):

Just looking at some of your weapons, you have some basic weapons: pistol, rifle, cannon, carbine, grenade etc.

As to some of the types, I see: ion, heavy, sporting, assault, repeating

Additionally the types can be used in combination with each other: heavy repeating blaster

"Blaster" is something I'm struggling with because it appears to be both a weapon unto itself (aurial blaster) but also can be a type that modifies another weapon: baster rifle, blaster cannon etc. I guess for now we can have it as both a record in the weapon table as well as a type in the type table (see below)


So, first we need a table to hold the basic weapon: pistol, rifle, cannon, carbine, grenade etc.

tblWeapons
-pkWeaponID primary key, autonumber
-txtWeaponName

Then a table to hold the types:
tblTypes
-pkTypeID primary key, autonumber
-txtType

A table to hold the combination names for the types. This table is somewhat of a place holder so that we can make the combinations we need.

tblCombinationNames
-pkComboNamesID primary key, autonumber
-txtComboName

Now a table to hold the types associated with each combination name

tblComboNameTypes
-pkComboNameTypeID primary key, autonumber
-fkComboNamesID foreign key to tblCombinationNames
-fkTypeID foreign key to tblTypes


Now associate the combination name with the basic weapon to define the weapon. The other fields that detail the attributes of the weapon would go in this table (to some extent):

tblWeaponComboTypes
-pkWeaponTypeID primary key, autonumber
-fkWeaponID foreign key to tblWeapons
-fkComboNameTypeID foreign key to tblComboNameTypes
-currCost
-spWeight

As to damage, if there are multiple types of damage (damage and stun damage) and a weapon(with its various types) can inflict multiple types of damage, then you would need a table of damage types and a table related to tblWeaponComboTypes that lists the types of damage for each weaponcombotype. (This is a guess based on your table, you would have to provide a little more detail to be sure) Something like this:

tblDamageTypes
-pkDamageTypeID primary key, autonumber
-txtDamage

tblWeaponComboTypesDamage
-pkWeaponComboTypeDamageID primary key, autonumber
-fkWeaponComboTypeID foreign key to tblWeaponComboTypes
-fkDamageTypeID foreign key to tblDamageType


Regarding availablility, if there is only 1 availability name that can apply to a weapon, then you would just have a field in tblWeaponComboTypes. I would recommend having a table to hold the availability names and then use a foriegn key in tblWeaponComboTypes that way you do not have to retype each name (you would just populate using a combo box on your form).


Relative to the "Type" field in your current table, if a weapon can display multiple "types" then you would use a similar approach as that for damage types described above. If there is only 1 "type" then it would be a field in
tblWeaponComboTypes

This might be a little overwhelming at first, so please do not hesitate to ask questions.

[slave138]

Well I have attached what my current Table and Insert Form look like.

My end goal would have when you enter a weapon name through a Combo Box, all the data I want appears on the same form.

If the form is just to insert new weapons, you should probably just change the combo to a text field.

As for your end goal, are you saying you want to be able to select a weapon from a drop down and have it display all the stats in a form?

[Dalagrath]

My very first question would be that could I start using Access 2007 to modify my Access database in a 2003 format? I don't like the view of 2003 and I like 2007 much better.

Secondly, I am going to try what you are explaining, but I might have difficulty seeing a how I only understood half of the terms used. (Google ftw). Also the name field is just the name of the weapon, the type is the type of damage it does. So would I still need a combo name table when it is just a reference to the name?

Thirdly, what does the fk and pk parts of the table you described mean? I know tbl is table and frm is form etc.

Also, that form is just an example of what I know so far. Yes I would like to type in a combo box and click search/filter and the form shows all data on that weapon.

[jzwp11]

Access 2007 should have no problem using a database created in 2003.

fk and pk are just prefixes I use to identify a primary key (pk) field and its corresponding foreign key (fk) field in the related table. To establish a relationship between two table, you would make the join using the two fields (i.e. they will contain the same value).

Secondly, I am going to try what you are explaining, but I might have difficulty seeing a how I only understood half of the terms used. (Google ftw). Also the name field is just the name of the weapon, the type is the type of damage it does. So would I still need a combo name table when it is just a reference to the name?

But within your weapon name records, you have names that share characteristics that describe the weapon. You have rifles, cannons, pistols etc. but you have many types of these entities. I was just breaking those types out. A lot of what I illustrated goes back to the rules of normalization.

I would hold off on the forms until you have a better understanding of tables and how they need to be structured otherwise you might end up redoing your forms several times.

[Dalagrath]

Sounds like a good idea. I am trying to understand how relationships work between tables. I tried looking around google for a good Access Starting guide, but it is not very nice.

Maybe you can answer this question. I have multiple tables, and In those tables I have multiple pieces of info specific to each "Class" of character. What would be a good way for me to keep a single Class table with multiple classes and all their info.

Here is a screenshot:

[jzwp11]

Forgive my ignorance, but can you list the classes of characters? And what are the specific items attributable to each class?

Looking at the tables you posted, you have 3 tables of weapons: tblExplosiveWeapons, tblMeleeWeapons and tblRangedWeapons. There should be 1 table that holds the weapons (like data should be stored in a table). You have 3 categories of weapons

tblWeapons
-pkWeaponID primary key, autonumber field (I have one autonumber, primary key in every table)
-fkCategoryID foreign key to tblCategory


tblCategory (holds 3 records: Explosive, Melee and Ranged)
-pkCatID primary key, autonumber
-txtCategory

I assume the characters are Jedi, Noble, scoundrel, scout, soldier; these should be records in a table

tblCharacters
-pkCharID primary key, autonumber
-txtCharacters

[Dalagrath]

I am trying to fix my weapons at the moment, and I can't quite get a foreign key to work. Could you explain how to get it to work for me?

Here is what I currently have:



My new weaponstable



And my WeaponsCat table:

[jzwp11]

The CatID field in tblWeapons has to be a long number field. You will actually have to enter the data in the CatID field in tblWeapons. You can do this directly in the table or you can create a simple form bound to the tblWeapons; you can use the form wizard to do it if you want. Then remove the textbox control tied to the CatID field that the wizard put in and replace it with a combo box control (use the combo box wizard) that is based on your category table.

The relationship you show between the two tables looks OK

[Dalagrath]

Ok, I kind of have been studying the relationships between two tables. One simple question, what is the reason for having multiple tables for combo weapons and single weapons, when the names are for referential purposes?

[jzwp11]

In the structure I presented earlier, there is only 1 table that holds the weapons. The tblWeaponComboTypes would have a corresponding record for every weapon in the weapon table that describes that weapon. That way your use could select the weapon and then look through a list of potential descriptors to help them select the weapon they want. For example, let's say the user wants a pistol. He would select that from a combo box and then a choice of the types of pistols would be presented (ion, blaster etc) from which they could make a choice. Perhaps I have taken it down to too a more granular level than you want, but it would allow the use to go through a specific sequence of choices to select the weapon they want.