How To Incorporate Selling A Product Option (1 Viewer)

[AJR]

Hi

As usual I have to say thanks in advance for all the help I receive here. Without it I would be so much more confused than I am.

I have an interesting sort of problem that I will explain with an analogy.

---------------------------------------------------------------------------------------------

You have a company that sells a small line of very exotic, non-perishable, chemicals to manufacturers.

You source these chemicals from around the world and resell them to the manufacturers.

The chemicals are not only very expensive, but may or may not be required for any given batch of product the manufacturer is producing.

The manufacturer does not know if the chemicals will be required, or not, until the last minute, so must have them immediately available for each batch.

You keep track of the process with a simple database that is built around a "Chemicals" database and a "Transactions" database. Which are linked
by [Product Code]

Quite straight forward to this point.

-----------------------------------------------------------------------------------------------

Now

You are seeking a way to make these chemicals accessible to manufacturers who cannot afford to buy them, just to have them sit around waiting
for the day they are needed.

You set up a system where a manufacturer can purchase an "Option" on the chemicals whereby:

They pay you a certain amount of money for the right to hold the chemicals at their plant for a given period, denoted in weeks.

After the pre-determined period is over they must either:

Return the chemicals to you and you keep the money they paid for the option.

If they used the chemicals, pay you the pre-determined purchase price --you still keep what they paid for the option.

----------------------------------------------------------------------------------------------

My problem is incorporating the options into the database built around "Chemicals" and "Transactions," e,g,:


(1) The "Chemicals" table has fields such as [Product Code],[Quantity in Stock],[Cost],[Sales Price per Kilo]. The options require a unique set of
fields such as [Product Being Optioned], [Expiry Date of Option],[Sales Price per Kilo -If Used],[Weekly Price for Option]

It doesn't seem correct to jam these into the same "Chemicals" table although I could be wrong.

(2) The same is true for the "Transactions" table, i.e., it now identifies the chemicals in each transaction using [Product Code].

To identify an option in a transaction one needs to use [Product Code] and [Expiration Date of Option]

(3) When a chemical is optioned the company has to track the fact that that quantity of it is not available for outright sale until the option expires


(4) A further problem exists even if I have figured out how to structure the tables.

Before selling a chemical one must create a record for that chemical in the "Chemicals" table--not a problem.

Options on the chemicals, however, do not exist until they are sold i.e., instead of using the "Chemicals" table to look up data for each
transaction. Each transaction (to sell an option) has to create a record in the table being used to track the options


Thoughts?

Many Thanks!!

 

[AlexN]

As far as I figured it out right, you need at least two more tables:
tblOptions and tblStock both linked to tblChemicals and tblTransactions. Think this might get things easier for you.

Caution: This is the first thing that crossed my mind, and I'm too far from being an expert.Other guys here might have better ideas and surely are experts.

 

[AJR]

Hi Alex and thanks for the reply

For the record what I have decided to do is:

Create a series of pop up forms that are used to input transaction data. That way I can tailor the required data to the specific type of transaction.

I then added fields to tblChemicals that store option specific data.

When selling an option the pop up form that will appear has fields for all the option specific data and when the user clicks the "Done" button a bit of VBA code is executed that creates a record in tblChemicals and populates all the relevant fields.

When the option terminates i.e., it is exercised or it expires, a different popup is used to input data. It uses a combo box to look up the option that is terminating and includes, for example, a field to indicate the outcome (exercise/expire).

This additional data is then written to the original record, the record is copied to a new table for closed out options, and the original record in tblChemicals is deleted. This is, again, done with some VBA generated by the click event on a button on the popup labelled "Done"

Thanks Again

A/R

 

[pbaldy]

An option that comes to mind is having the transaction be for 2 "products", the actual product plus the option. If they don't exercise the option, you return the product portion of the original sale.

 

[AJR]

Thanks for that Paul. It's an interesting angle that I'm going to think about. One problem,however, would be that the product would not show up in inventory if I did that.

A/R

 

[pbaldy]

It certainly could, and should. It would either reverse or offset the sale, either way returning the product to inventory.