inquiries vs. actual sales

Using Access 2007, I will be developing a prospect call log and order entry
system for my company. The products we sell are somewhat expensive so we get
a lot of requests for quotes, but not a lot of sales (relative to quotes). In
some cases a quote can be very straight forward and we just need a few
details to give a price. In many cases we need a LOT of detail to get a
prospect the correct price. Our quote/order form asks a lot of questions that
all need to be answered IF someone decides to buy. I basically want an easy
way to turn a prospect quote system into a sale without a lot of duplicate
data entry on the salesperson’s part. It had been requested that “at a push
of a button� all the data that is enter into the quote form could be
transferred to the sales form and then the salesperson could continue to
complete the sale.

We don’t want the prospects’ data in with the sales and the prospects will
be purged weekly of data that is older than 60 days. Sales need to be kept
forever. A pending order is still considered to be a prospect until the
credit card is approved. If it is declined, we don’t want that info in the
sales section.

Since there are a number of ways to set this up, I am requesting advice. I
see my options as:
1) A single “flat� prospect table for all the data and then once it becomes
a sale, break it apart and append to the separate tables for each type of
data: customer, payment, product, order, etc. It sounds like a lot of
duplication, but since we will be purging old quotes after about 60 days, we
won’t actually be maintaining duplicate data.
2) Run two sections (tables not related to each other) with complete table
setup for each; prospects and sales. Basically it is the same as option 1,
but both sections are properly designed/related to the lookup tables, etc.
When a prospect becomes a sale, “press a button� and append to all the
respective tables in the sales section. This would be a huge duplication of
both design and data and doesn’t seem like a great idea at all.
3) Have one properly designed/related set of tables and when a prospect
becomes a sale, check a box and have all sales related queries, forms,
reports, etc. based upon this criteria. This seems the best idea, but because
we will be purging useless outdated prospect data all the time, I need to
make sure sales don’t get deleted. That is why I was thinking I should append
actual sales to a separate section.
a.) I think this option would also allow for customers who order often
as we would be able to search for them by name or account# and have their
basic information (name, address, phone) already available. That way we would
only need to obtain detail on the product we will be quoting.

Any other suggestions on design that I haven’t thought of? (I’m sure there
are) Hit me, I’m open.

Option 3

You would just run a delete query that would ignore any records that
have been marked as a sale.

You may want to consider having a backup table where the deleted
records would be stored for, say, another 60 days just in case.
Depending on how many records you generate on a daily basis, you
might also consider just leaving them in the table and using criteria
to only display records that are newer than 60 days, unless you are
certain that you would *never* need to view any of the older ones.

--
_________

Sean Bailey


"AllYourSpam" wrote:

Using Access 2007, I will be developing a prospect call log and order entry
system for my company. The products we sell are somewhat expensive so we get
a lot of requests for quotes, but not a lot of sales (relative to quotes). In
some cases a quote can be very straight forward and we just need a few
details to give a price. In many cases we need a LOT of detail to get a
prospect the correct price. Our quote/order form asks a lot of questions that
all need to be answered IF someone decides to buy. I basically want an easy
way to turn a prospect quote system into a sale without a lot of duplicate
data entry on the salesperson’s part. It had been requested that “at a push
of a button� all the data that is enter into the quote form could be
transferred to the sales form and then the salesperson could continue to
complete the sale.

We don’t want the prospects’ data in with the sales and the prospects will
be purged weekly of data that is older than 60 days. Sales need to be kept
forever. A pending order is still considered to be a prospect until the
credit card is approved. If it is declined, we don’t want that info in the
sales section.

Since there are a number of ways to set this up, I am requesting advice. I
see my options as:
1) A single “flat� prospect table for all the data and then once it becomes
a sale, break it apart and append to the separate tables for each type of
data: customer, payment, product, order, etc. It sounds like a lot of
duplication, but since we will be purging old quotes after about 60 days, we
won’t actually be maintaining duplicate data.
2) Run two sections (tables not related to each other) with complete table
setup for each; prospects and sales. Basically it is the same as option 1,
but both sections are properly designed/related to the lookup tables, etc.
When a prospect becomes a sale, “press a button� and append to all the
respective tables in the sales section. This would be a huge duplication of
both design and data and doesn’t seem like a great idea at all.
3) Have one properly designed/related set of tables and when a prospect
becomes a sale, check a box and have all sales related queries, forms,
reports, etc. based upon this criteria. This seems the best idea, but because
we will be purging useless outdated prospect data all the time, I need to
make sure sales don’t get deleted. That is why I was thinking I should append
actual sales to a separate section.
a.) I think this option would also allow for customers who order often
as we would be able to search for them by name or account# and have their
basic information (name, address, phone) already available. That way we would
only need to obtain detail on the product we will be quoting.

Any other suggestions on design that I haven’t thought of? (I’m sure there
are) Hit me, I’m open.