How to handle a VERY flexible price structure

[pietlinden]

I've been given the task of building a database for a manufacturer's
representative. He basically imports product and resells it. He
currently has this spreadsheet that's fairly complex. (Either that or
I'm just plain stupid, because it took me 2 days to figure out how it
all worked!) Most of the sheet is simple and straightforward . Looks
a lot like a normal table in Access would, except there are formulas
thrown in everywhere.

In a section at the top of the sheet, there are costs associated with
the column below it that the user can modify. If I were to do this in
Access and I didn't need to store the results, I would just use
unbound text boxes and restrict the inputs to a valid range etc. and
then base the form on a query. There are problems with this approach,
though. In order to get a sale, there are several variables that the
user can adjust (think of doing this in Excel and it makes a lot more
sense) - manipulating various rates so that the final bid comes in to
the customer's liking.

A few examples - these are all defaults, but can be adjusted to make
the sale:
Gross Margin - 33%
Commission - 5%
Back End Points - 7%
Container cost 40' = $3500

(As my English teacher once said "Make a CASE, not a collage!") Okay,
to the fun stuff.

All of these expenses have to be recorded, because they'll eventually
become part of someone's final bill. What's the best way to deal with
this information? Set up fields in the Invoice, since they're applied
to the whole invoice, not to individual items,(at least as far as I
know)? Then if I store them there, I can calculate everything...
Okay, setting the defaults (so they work in a fashion similar to
Excel)... Store the defaults in a separate table or just in the form?
(maybe I'm just thinking way too hard!) Right now, my gut instinct is
to store them in the form to test things out, and then to maybe move
them to a table with a single record. then I could load the defaults
from the table, and the user could modify them later.

One KEY "rule" here is that once an invoice is billed, that's a final
price (so just store the calculated values because the business rule
trumps the database rule?)

And straight from the boss's mouth: Pretty much ANY cost can be
overridden. So just calculating as if the rules were hard and fast
(IOW in a simple query) won't really work.

Any thoughts? (Okay, you can snicker some, but try not to guffaw...)

And why not use QuickBooks Pro for Manufacturing? He already shot
that down.
He "only" needs it to do Invoices, Quotes, and Purchase Orders. And
he pays the bills, so I guess he's right no matter what.

Thanks!
Pieter

[salad]

pi********@hotm ail.com wrote:

I've been given the task of building a database for a manufacturer's
representative. He basically imports product and resells it. He
currently has this spreadsheet that's fairly complex. (Either that or
I'm just plain stupid, because it took me 2 days to figure out how it
all worked!) Most of the sheet is simple and straightforward . Looks
a lot like a normal table in Access would, except there are formulas
thrown in everywhere.

In a section at the top of the sheet, there are costs associated with
the column below it that the user can modify. If I were to do this in
Access and I didn't need to store the results, I would just use
unbound text boxes and restrict the inputs to a valid range etc. and
then base the form on a query. There are problems with this approach,
though. In order to get a sale, there are several variables that the
user can adjust (think of doing this in Excel and it makes a lot more
sense) - manipulating various rates so that the final bid comes in to
the customer's liking.

A few examples - these are all defaults, but can be adjusted to make
the sale:
Gross Margin - 33%
Commission - 5%
Back End Points - 7%
Container cost 40' = $3500

(As my English teacher once said "Make a CASE, not a collage!") Okay,
to the fun stuff.

All of these expenses have to be recorded, because they'll eventually
become part of someone's final bill. What's the best way to deal with
this information? Set up fields in the Invoice, since they're applied
to the whole invoice, not to individual items,(at least as far as I
know)? Then if I store them there, I can calculate everything...
Okay, setting the defaults (so they work in a fashion similar to
Excel)... Store the defaults in a separate table or just in the form?
(maybe I'm just thinking way too hard!) Right now, my gut instinct is
to store them in the form to test things out, and then to maybe move
them to a table with a single record. then I could load the defaults
from the table, and the user could modify them later.

One KEY "rule" here is that once an invoice is billed, that's a final
price (so just store the calculated values because the business rule
trumps the database rule?)

And straight from the boss's mouth: Pretty much ANY cost can be
overridden. So just calculating as if the rules were hard and fast
(IOW in a simple query) won't really work.

Any thoughts? (Okay, you can snicker some, but try not to guffaw...)

And why not use QuickBooks Pro for Manufacturing? He already shot
that down.
He "only" needs it to do Invoices, Quotes, and Purchase Orders. And
he pays the bills, so I guess he's right no matter what.

Thanks!
Pieter

I would consider having a table to hold the default values. That way
the person could update them without you updating the form.

If the values are for each separate invoice, I'd store the results in
the Invoice table or else a table that is linked to the invoice table by
the invoice id.

Since the values can be calced (via some formula) or overridden, you
need to have those "calced" fields be in some field.

You could put a button on the form to "recalc" the info.

If needed, you could have some sort of flag (like a checkbox) so that
overwritten values aren't recalced if a calc is performed.

I don't fully comprehend what problems you face, so consider this
slinging some mud and hoping something sticks.

[pietlinden]

On Feb 9, 1:35 am, salad <o...@vinegar.c omwrote:

pietlin...@hotm ail.com wrote:

I've been given the task of building a database for a manufacturer's
representative. He basically imports product and resells it. He
currently has this spreadsheet that's fairly complex. (Either that or
I'm just plain stupid, because it took me 2 days to figure out how it
all worked!) Most of the sheet is simple and straightforward . Looks
a lot like a normal table in Access would, except there are formulas
thrown in everywhere.

In a section at the top of the sheet, there are costs associated with
the column below it that the user can modify. If I were to do this in
Access and I didn't need to store the results, I would just use
unbound text boxes and restrict the inputs to a valid range etc. and
then base the form on a query. There are problems with this approach,
though. In order to get a sale, there are several variables that the
user can adjust (think of doing this in Excel and it makes a lot more
sense) - manipulating various rates so that the final bid comes in to
the customer's liking.

A few examples - these are all defaults, but can be adjusted to make
the sale:
Gross Margin - 33%
Commission - 5%
Back End Points - 7%
Container cost 40' = $3500

(As my English teacher once said "Make a CASE, not a collage!") Okay,
to the fun stuff.

All of these expenses have to be recorded, because they'll eventually
become part of someone's final bill. What's the best way to deal with
this information? Set up fields in the Invoice, since they're applied
to the whole invoice, not to individual items,(at least as far as I
know)? Then if I store them there, I can calculate everything...
Okay, setting the defaults (so they work in a fashion similar to
Excel)... Store the defaults in a separate table or just in the form?
(maybe I'm just thinking way too hard!) Right now, my gut instinct is
to store them in the form to test things out, and then to maybe move
them to a table with a single record. then I could load the defaults
from the table, and the user could modify them later.

One KEY "rule" here is that once an invoice is billed, that's a final
price (so just store the calculated values because the business rule
trumps the database rule?)

And straight from the boss's mouth: Pretty much ANY cost can be
overridden. So just calculating as if the rules were hard and fast
(IOW in a simple query) won't really work.

Any thoughts? (Okay, you can snicker some, but try not to guffaw...)

And why not use QuickBooks Pro for Manufacturing? He already shot
that down.
He "only" needs it to do Invoices, Quotes, and Purchase Orders. And
he pays the bills, so I guess he's right no matter what.

Thanks!
Pieter

I would consider having a table to hold the default values. That way
the person could update them without you updating the form.

If the values are for each separate invoice, I'd store the results in
the Invoice table or else a table that is linked to the invoice table by
the invoice id.

Since the values can be calced (via some formula) or overridden, you
need to have those "calced" fields be in some field.

You could put a button on the form to "recalc" the info.

If needed, you could have some sort of flag (like a checkbox) so that
overwritten values aren't recalced if a calc is performed.

I don't fully comprehend what problems you face, so consider this
slinging some mud and hoping something sticks.

Right now, sling all the mud you want!! Muddy thoughts are better
than no thoughts. At the moment, I'm thinking along the lines of
assuming (probably dangerous) that _some_ fields are going to be
purely calculated. And those that are flexible, I'll store. Then I
won't have to store around 70 fields of data that are superfluous...
Then once I have the data, I can use ADO to shove the data to a
template with named ranges/cells per Dev's article
CopyFromRecords et... now, with marble-like sharpness, I'm off to try
it...

Thanks for the feedback!

Pieter