converting spreadsheet to Access DB

[pietlinden]

Hi,
I have a spreadsheet that someone wrote that he wants me to turn into
a database for him.
The spreadsheet is for doing costing calculations so that the sales
rep can come up with a price that the customer agrees to. So this
thing has to be pretty flexible.

I've split out the spreadsheet so that it looks a lot more like that
ugly NWind dog. The spreadsheet I have been given basically breaks
out all the costs involved in purchasing, packaging, shipping, etc
inventory from the vendor to the final customer. So each column
calculates a separate cost. The "constants" that the column get
multiplied by are directly above the cell in question. For example, a
simple one would be "Markup Dollars", which is [PurchasePrice] * (1+
[MarkupPercent]) where MarkupPercent is an absolute reference to a
cell.

So far, the only way I can think of doing this and making it work
right is to put all the constants into the Invoice Header table and
then set the Line Items to multiply by those values. Here's the
structure of the spreadsheet that's giving me fits.

Packaging Costs
Row Guide Box_Ea Box_Freight
===================================
Min Order 1 1
Total Cost $5.00 $20.00
Cost Each =TotalCost/MinOrder

and then each of the costs is copied down the column ("=AB$5", for
example.)

The database part of my brain is thinking "create a table of costs
CREATE TABLE Costs(
CostType Text(50) NOT NULL,
CostItem Text(50) NOT NULL,
MinOrder Integer NOT NULL DEFAULT 1,
PRIMARY KEY (CostType, CostItem)
)

and then do the math in queries. The Min_Order and Total_Cost fields
are user-modifiable in the spreadsheet and the rest are pretty much
just calculated. So why not go this way? Each Cost has a 1:1
relationship with the Invoice header (spread evenly over the invoice
line items, especially when it comes to shipping, because all the
items are put in the same container and the container cost is spread
over each item inside).

Any ideas on what would be the best way to handle this? Of course, if
you have questions, feel free to ask! My description is even
confusing to me, but that could well be the myopia from staring at it
for so long.)

Right now I'm considering storing all the modifiable cost drivers in
the Invoice table, but that seems really ugly.

I must say, I have a new appreciation for Excel after doing this...

THanks for listening!

Pieter

 

[Jeff Boyce]

Pieter

If this is working as a spreadsheet right now, what new capability/feature
will migrating it to Access offer?

Given that spreadsheets can make complex calculations more automatic (as you
already have), and that relational databases (e.g., Access) store data and
make "slice & dice" reporting possible, why do you need Access?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
I have a spreadsheet that someone wrote that he wants me to turn into
a database for him.
The spreadsheet is for doing costing calculations so that the sales
rep can come up with a price that the customer agrees to. So this
thing has to be pretty flexible.

I've split out the spreadsheet so that it looks a lot more like that
ugly NWind dog. The spreadsheet I have been given basically breaks
out all the costs involved in purchasing, packaging, shipping, etc
inventory from the vendor to the final customer. So each column
calculates a separate cost. The "constants" that the column get
multiplied by are directly above the cell in question. For example, a
simple one would be "Markup Dollars", which is [PurchasePrice] * (1+
[MarkupPercent]) where MarkupPercent is an absolute reference to a
cell.

So far, the only way I can think of doing this and making it work
right is to put all the constants into the Invoice Header table and
then set the Line Items to multiply by those values. Here's the
structure of the spreadsheet that's giving me fits.

Packaging Costs
Row Guide Box_Ea Box_Freight
===================================
Min Order 1 1
Total Cost $5.00 $20.00
Cost Each =TotalCost/MinOrder

and then each of the costs is copied down the column ("=AB$5", for
example.)

The database part of my brain is thinking "create a table of costs
CREATE TABLE Costs(
CostType Text(50) NOT NULL,
CostItem Text(50) NOT NULL,
MinOrder Integer NOT NULL DEFAULT 1,
PRIMARY KEY (CostType, CostItem)
)

and then do the math in queries. The Min_Order and Total_Cost fields
are user-modifiable in the spreadsheet and the rest are pretty much
just calculated. So why not go this way? Each Cost has a 1:1
relationship with the Invoice header (spread evenly over the invoice
line items, especially when it comes to shipping, because all the
items are put in the same container and the container cost is spread
over each item inside).

Any ideas on what would be the best way to handle this? Of course, if
you have questions, feel free to ask! My description is even
confusing to me, but that could well be the myopia from staring at it
for so long.)

Right now I'm considering storing all the modifiable cost drivers in
the Invoice table, but that seems really ugly.

I must say, I have a new appreciation for Excel after doing this...

THanks for listening!

Pieter

 

[pietlinden]

Pieter

If this is working as a spreadsheet right now, what new capability/feature
will migrating it to Access offer?

Given that spreadsheets can make complex calculations more automatic (as you
already have), and that relational databases (e.g., Access) store data and
make "slice & dice" reporting possible, why do you need Access?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
I have a spreadsheet that someone wrote that he wants me to turn into
a database for him.
The spreadsheet is for doing costing calculations so that the sales
rep can come up with a price that the customer agrees to. So this
thing has to be pretty flexible.

I've split out the spreadsheet so that it looks a lot more like that
ugly NWind dog. The spreadsheet I have been given basically breaks
out all the costs involved in purchasing, packaging, shipping, etc
inventory from the vendor to the final customer. So each column
calculates a separate cost. The "constants" that the column get
multiplied by are directly above the cell in question. For example, a
simple one would be "Markup Dollars", which is [PurchasePrice] * (1+
[MarkupPercent]) where MarkupPercent is an absolute reference to a
cell.

So far, the only way I can think of doing this and making it work
right is to put all the constants into the Invoice Header table and
then set the Line Items to multiply by those values. Here's the
structure of the spreadsheet that's giving me fits.

Packaging Costs
Row Guide Box_Ea Box_Freight
===================================
Min Order 1 1
Total Cost $5.00 $20.00
Cost Each =TotalCost/MinOrder

and then each of the costs is copied down the column ("=AB$5", for
example.)

The database part of my brain is thinking "create a table of costs
CREATE TABLE Costs(
CostType Text(50) NOT NULL,
CostItem Text(50) NOT NULL,
MinOrder Integer NOT NULL DEFAULT 1,
PRIMARY KEY (CostType, CostItem)
)

and then do the math in queries. The Min_Order and Total_Cost fields
are user-modifiable in the spreadsheet and the rest are pretty much
just calculated. So why not go this way? Each Cost has a 1:1
relationship with the Invoice header (spread evenly over the invoice
line items, especially when it comes to shipping, because all the
items are put in the same container and the container cost is spread
over each item inside).

Any ideas on what would be the best way to handle this? Of course, if
you have questions, feel free to ask! My description is even
confusing to me, but that could well be the myopia from staring at it
for so long.)

Right now I'm considering storing all the modifiable cost drivers in
the Invoice table, but that seems really ugly.

I must say, I have a new appreciation for Excel after doing this...

THanks for listening!

Pieter

Maybe just integrating would be a better idea. The problem with Excel
is that if he has 57K products now and expands a little bit, Excel
will no longer be able to hold that many records. Also, the costing
part of the application is only that - just a part of the whole
solution. He wants it to be able to send e-mails etc (of quotes etc),
and log his sales for him.

 

[Jeff Boyce]

If you don't have experience using Access, you may find the learning curve a
bit steep.

Have you searched for COTS (commercial, off-the-shelf) software that might
handle the general requirements (small business/accounting packages come to
mind).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Pieter

If this is working as a spreadsheet right now, what new
capability/feature
will migrating it to Access offer?

Given that spreadsheets can make complex calculations more automatic (as
you
already have), and that relational databases (e.g., Access) store data
and
make "slice & dice" reporting possible, why do you need Access?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
I have a spreadsheet that someone wrote that he wants me to turn into
a database for him.
The spreadsheet is for doing costing calculations so that the sales
rep can come up with a price that the customer agrees to. So this
thing has to be pretty flexible.

I've split out the spreadsheet so that it looks a lot more like that
ugly NWind dog. The spreadsheet I have been given basically breaks
out all the costs involved in purchasing, packaging, shipping, etc
inventory from the vendor to the final customer. So each column
calculates a separate cost. The "constants" that the column get
multiplied by are directly above the cell in question. For example, a
simple one would be "Markup Dollars", which is [PurchasePrice] * (1+
[MarkupPercent]) where MarkupPercent is an absolute reference to a
cell.

So far, the only way I can think of doing this and making it work
right is to put all the constants into the Invoice Header table and
then set the Line Items to multiply by those values. Here's the
structure of the spreadsheet that's giving me fits.

Packaging Costs
Row Guide Box_Ea Box_Freight
===================================
Min Order 1 1
Total Cost $5.00 $20.00
Cost Each =TotalCost/MinOrder

and then each of the costs is copied down the column ("=AB$5", for
example.)

The database part of my brain is thinking "create a table of costs
CREATE TABLE Costs(
CostType Text(50) NOT NULL,
CostItem Text(50) NOT NULL,
MinOrder Integer NOT NULL DEFAULT 1,
PRIMARY KEY (CostType, CostItem)
)

and then do the math in queries. The Min_Order and Total_Cost fields
are user-modifiable in the spreadsheet and the rest are pretty much
just calculated. So why not go this way? Each Cost has a 1:1
relationship with the Invoice header (spread evenly over the invoice
line items, especially when it comes to shipping, because all the
items are put in the same container and the container cost is spread
over each item inside).

Any ideas on what would be the best way to handle this? Of course, if
you have questions, feel free to ask! My description is even
confusing to me, but that could well be the myopia from staring at it
for so long.)

Right now I'm considering storing all the modifiable cost drivers in
the Invoice table, but that seems really ugly.

I must say, I have a new appreciation for Excel after doing this...

THanks for listening!

Pieter

Maybe just integrating would be a better idea. The problem with Excel
is that if he has 57K products now and expands a little bit, Excel
will no longer be able to hold that many records. Also, the costing
part of the application is only that - just a part of the whole
solution. He wants it to be able to send e-mails etc (of quotes etc),
and log his sales for him.

 

[pietlinden]

If you don't have experience using Access, you may find the learning curve a
bit steep.

Have you searched for COTS (commercial, off-the-shelf) software that might
handle the general requirements (small business/accounting packages come to
mind).

Several years, and school to boot (not that it helped!). I suggested,
as I thought I mentioned, doing it in QuickBooks Pro Manufacturing,
but that was shot down. I don' think he liked the price tag, and the
one key problem that his pricing is pretty flexible and QBP just
wouldn't handle it the way his business's pricing structures work.

Right now I'm leaning toward putting all the individual costs/expenses
into a single child table and creating a cartesian product if
necessary to populate them. Then I can query to make it look like
Excel, if he wants and use ADO to shove it into a worksheet. Why in
heaven's name he wants to do this in Access at all is beyond me,
because I would NEVER trust my critical business data to Access. Seen
too many databases irreparably corrupted by freaky networks or
whatever.

Pieter

 

[Jeff Boyce]

Actually, I've had just the opposite experience. I have several
line-of-business Access applications running where I work, some for several
years. I don't recall the last time any of them suffered a corruption
issue.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[pietlinden]

Actually, I've had just the opposite experience. I have several
line-of-business Access applications running where I work, some for several
years. I don't recall the last time any of them suffered a corruption
issue.

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

Maybe I did all those things they tell you not to, and worked on a
screwy network.