Question Inputting DATE & QUANTITY data to obtain a rate

[corai]

Hi there,

I'm am relatively new to Access and this forum so don't really know what I'm talking about.

I have records for about 500 companies that all make orders (of say apples) at random. For any given company I would like to develop a system which allows me to input the order date and order quantity (via a form), and thus calculate the rate at which apples are consumed (apples/day) by subtracting the last date an order was made from the new order date to calculate the number of days over which the quantity of apples lasted. Is there an easy way to do this? Or do I need to use macros in order to do this?

Any help on this would be very much appreciated!

C

 

[jzwp22]

Welcome to the forum!

With a properly setup database, the calculation would be easily handled with a query.

How is your database currently structured (tables, fields, relationships)?

 

[corai]

Hi jzwp,

My database is currently rather simple. I have a main table which holds the information for the 500 companies or so. This includes other information which isn't really relevant to the problem at hand such as address and contact information. I'm using forms linked to this table to edit the information.

Do I need to create a separate table for each company in order to store order dates and is there a quick way of doing this?

 

[jzwp22]

Do I need to create a separate table for each company in order to store order dates

You do need some additional tables but not one for each company.

If a company can place many orders, that describes a one-to-many relationship, so the orders have to be in a separate table related back to the specific customer.

Assuming that your customer table looks something like this:

tblCustomers
-pkCustomerID primary key, autonumber
-txtCustomerName text field
-txtCustomerAddress text field
-txtCustomerCity text field
etc.

tblCustomerOrders
-pkCustOrderID primary key, autonumber field
-fkCustomerID foreign key relating to tblCustomers, long number data type
-dteOrder date/time field to hold the order date


Now since a customer can order many items/products for a particular order and a product can be purchased by many customers we have what is called a many-to-many relationship.

First, we need a table to hold all products

tblProducts
-pkProductID primary key, autonumber field
-txtProductName text field

Now to represent a many-to-many relationship, we need a junction table that will relate products to orders

tblCustomerOrderProducts
-pkCustOrdProdID primary key, autonumber field
-fkCustOrderID foreign key to tblCustomerOrders, long number data type
-fkProductID foreign key to tblProducts, long number data type
-longQTY long number field to hold the quantity of the particular product ordered. If an order can be placed for fractional amounts, then you will need a single precision number field rather than a long number field.