Setting Up Tables

[Guest]

Hello,

I am an Access beginner and could use some help setting up tables. I want
to set up a database that contains records by month from 2003 through April
2004 for the following data:

Division
Product
Volume
Sales
Cost
Profit

In other words, for every division we own I would like to see for each
product the sales, cost and profit. Should I set up separate tables for each
item listed above, or should I try to combne them into one table? Also, do I
need a separate table for time periods (the months from Jan '03 - April '04).


If I do need separate tables for each item, do I need a common field in each
table so I can create relationships to bring all the data into one report?

Any advice is much appreciated. Thanks.

John

 

[Guest]

Hi, John.

After thinking about the specific types of "things" in an application and
their "attributes" (which become Tables and Fields, respectively), I find it
helpful to next think about relationships. In your case, there is a
many-to-many relationship between a Division and a Product. Each Division
will sell many different products, and at least some products will be sold by
multiple divisions.

Implement this many-to-many with a junction table that will require each
record to identify the product and division for which the volume, sales, and
cost will be entered. Profit is a calculation [Sales]-[Cost], and therefore
should not be a table field, but rather calculated on-the-fly in a query.
Commonly application developers call this table a combination of the two
tables involved in the junction, but you can call it anything you want.

I see, then, three tables based on what you've described:

Division
------------
DivisionID AutoNumber or Integer (Primary Key)
Division Text

.... any other Division descriptors

Product
------------
ProductID AutoNumber or Number or Text (Primary Key)
ProductDescription Text

....any other product descriptors

DivisionProduct (or simply, Sales)
---------------------------------------
DivisionProductID AutoNumber (Primary Key)
DivisionID Integer (Foreign Key to Division)
ProductID Integer (Foreign Key to Product)
Month Integer
Volume Integer
Sales Currency
Cost Currency

Alternatively, you could make the Primary Key a compound key of DivisionID,
ProductID, and Month. I and many other developers prefer a simple AutoNumber
key. The downside of the simplicity of a single field key, however, is that
it would allow a duplicate record to be entered. To avoid this, add a
multiple field index made up of these three fields.

If you've never done this before, edit the table in Design View, select
View, Indexes. Give the index a name, such as DivProdMonth, and type the
first field in the FieldName column. Type the other two field names directly
below this one, but leave the IndexName column blank. Save the table to
store the index. Now, if a user attempts to add a record with the same
division, product, and month combination, Access will prevent the record from
being added and display a message.

Hope that helps.
Sprinks