DB Normalization Question

[Cordury]

I have created a simple DB to house work projects. This db will allow me to track all current open issues as well as everything worked on and completed within a certain time period.

Right now, it has only one table. I am looking for suggestions how to break up the data. All of these work issues/investigations deal with stores. And I have started to run into an issue where one store has multiple issues therefore the store # is showing up more than once in my table. But if I break the db up into Table1 = Stores and Table 2 = Issues, will I have to use a sub form?
How can I have multiple records for one store.

tblMaster contains the following fields:
Store Number (which is really the only store info I need for now)
Account Number
Utility Provider
Comments
Open Date
Close
Date
Status
Employee

Should I have one table which only field is store number?

Thanks,
Cord

 

[jdegeorge]

You're okay for now. If you were to have more information about the store in this table, such as address, etc., then you'd need to create a store table. Same goes for the other fields...unless you want more information about utility providers or employees, for example, you're okay with the one table. It's already normalized.

Jim DeGeorge

 

[rdodge]

Here's a link that deals with the first 3 levels of data normalization form

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

For most situations, the first 3 levels of normalization form is all you need. However, there are few rare exceptions that will require up to the 5th and last level of normalization form, which has the link below:

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm8.html

Now, once you have planned out your tables to meet the normalization form requirements of going in both directions (drilling up and down) for what your DB program requires, you can then create queries, either QBE (Query By Example) or SQL (Structured Query Language) statements, which then your forms can be bound to the dynaset queries. In addition, you can use sub forms in Access to help this process out.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[Cordury]

Thank you both for your help.
One more question: If I were to break the db into two tables:

tblStores (which would contain static store info) and
tblIssues (would contain the details of the issue/investigation)

How would I set up the form(s). Obviously, the data would be linked by the store number. Like we discussed earlier, just use a subform for the issues? Oh well I shall cross that bridge when I get to it.

HAVE A SAFE AND HAPPY HOLIDAY!

 

[rdodge]

The tblStores would contain the static store information, of which you should have a field (probably the first field) of the table be set as the unique ID field, so as each store as a unique identification.

tblIssues would contain it's own unique ID number, it's related fields, and a field that contains the Store's ID number, thus you would have a relationship of a 1 to many from the Store table to the Issues Table. That's cause the link is based on the Store ID number, and this ID number is only listed one time within the Store table, but may be listed more than once in the Issues table.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[rdodge]

When you setup your sub form, you may have issues tied to your sub form with your main form having the static store information. I haven't worked enough with sub forms to know all of it's workings yet, but at some point of time, I will cross that road as I'm still in the relatively early stages of my DB building, but then with my forms being unbound, it hasn't really become as much of a requirement, but there would be some cases for such requirement.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.