Suggested approach to solve this problem:
STEP 1: This should be on paper
1) Identify the entities and attributes hidden in the Excel spread sheet.
a) Potential Entities: Customer, Order, ITEM, Ordered- ITEM and Shipper ian be represented with a rectangle.
CUSTOMER
*Customer Number
Customer Address
Customer State
Customer Zip
Customer Distance
ITEM
*ItemNo
ItemDescription
Item weight
Item Cost
ItemPrice
ITEM
*ItemNo
ItemDescription
Item weight
Item Cost
ItemPrice
b) Identify each column on the spread sheet as a potential attribute of some entity (e.g. address belongs to the customer, price belongs to an entity called product) Please Note: At this stage, remember the calculated fields should not be identified as potential attributes. Ignore the calculated fields
c) Identify the primary key for each entity with an asterisk.
d) Make sure that each entity is in Third Normal Form. (if you are not sure about the term "Normal Form" , go back to the Perry book and the Kent Article. Remember the calculated fields can be created by a query in MS Access.
e) Determine the relationships between the entities you have just identified. For instance CUSTOMER and ORDER relationship is One-to-Many. To relate CUSTOMER to ORDER, add customer number as an attribute to the ORDER entity.
f) Identify Many-to-Many relationships. In this example, the relation between ITEM and ORDER is Many-to-Many. An ITEM may be ordered more than once, and one ORDER might have more than one item. To avoid this type of relationships create intersection entities by using the keys from each of those entities in the following format. In our example we solve this problem by creating an intersection entity call it ORDERED-ITEMS. This an intersection entity and it owes its existence to the ITEM and ORDER entities. Accordingly it is identified by a composite key.
ORDERED-ITEM
*ItemNo
*Order Number Qty Ordered
STEP 2: This part should be in Access
1. Create Tables to represent each entity and add each attribute as a field in MS Access
2. Create the relationships between the tables you created by using foreign keys.
3. Select the menu option "Database Tools" and create a relationship between the tables you created. Save your results (Hint: See the upper half of the attachment called "CreateCalculatedField`)
STEP 3: Using the data in Excel spreadsheet, populate the data base tables.
STEP 3: Create a Query
1. Create a query adding all the tables and all the fields in your tables using Query Design mode. There are other alternatives to creating queries, such as wizards, but in my opinion the easiest alternative is Query Design approach.
2. While still in the Query Design mode, create the calculated fields using the formulas as shown in the attached screen shot "CreateCalculatedField.JPEG"
3. Run your query to see whether the results are OK and save the query.
STEP 4:Using the CustomerInvoice.pdf as your guide create a report of receivables in the Report Wizard mode.
Attachment:- Customer Invoices.rar