Need help/ideas - price book project

[Steve]

Folks,

I've been directed to create the new pricebooks for my company, and I'm
stuck trying to figure out the best way to do this. In fact, I'm stuck
trying to figure out any manageable way to do this ;-)

In the past price books were fairly simple reports we output from an access
database we created. Earlier this year I redid that database to allow us to
create different versions of the book for different recipients. Since then,
someone proposed a new format that is better, and we need to incorporate it.

The new format has a summary table for each product, and this table varies
in dimensions for each product and for each product's application - we make
capital equipment for the graphic arts industry. Following this table is a
reference section listing the actual pricing items that have been totaled
for each intersection in the table. Following the reference section, there
is a list of options, and some of those will have photos or other
illustrations. We have about 30 price book recipients who will receive a
custom-tailored version, which is to say the tables, reference sections and
options can be different for each recipient. There are about 200 products,
maybe 20-30 needing the new format - the others will done in the old format.

It's fairly easy to create the necessary tables in excel, but I can't figure
out how to get them into access as an OLE object without manually pasting
them in each record - which is unmanageable given how many there are. Also,
linking each reference section to the appropriate table is a problem. I know
I can pound this all out manually in Word, but god help us if we need to
make changes and propagate them through all the versions. I've also thought
of doing the entire thing in Excel, but page formatting becomes a problem. I
think the ultimate solution is to have a word doc where I can specify what
book is being made, read the book contents out of Access, pull the tables
out of Excel (which in turn pulls pricing from Access) and then save each
doc as it is created. But even this would mean making 30 new docs every time
changes are made...

Has anyone done anything similar? Have any advice?

Thanks!

Steve

 

[Kevin]

As far as the format goes, Word offers a lot more
flexability than Access Reports, but getting information
from Access to Word will take some effort. You can create
a word template for each format, then output the
information using Mail Merge. As far as the information in
Excel, can that information be loaded into Access as data
in tables? Then outputting the information would not be
that difficult if your going to an Access report. I would
suggest purchasing the Access Developers Handbook for your
version of Access. This will give you somne good
information as to how to get information from Access to
Word and even in getting information from Excel to Access.
You can buy an Access Developers Kit which includes the
Access Developers Handbook and the VBA habdbook (I am not
sure if that's the title or not but it is a VBA
reference). Both of these references have been a world of
help to me!

Hope that helps!

Kevin

 

[Steve]

Kevin,

Thanks for the response!

I've bought the book you mention, although it seems a bit advanced for me. I
was, many years ago, a sysadmin on unix systems but the whole event-based VB
thing lost me and I ended up taking a different career path anyway. I guess
I've got to let it find me ;-)

Anyway, I figure I've got a few choices:

1. Use the database I have now, but figure out some way to pull a table from
Excel into certain records as an OLE object. When I say table, I mean a
table like an illustration in a book. Different products are sold in
different ways, with different variables along the two dimensions of the
table, so making the table in access is as much work as in Excel. Another
wrinkle is that I will probably have to have different versions of some
tables for some price book versions. Not really sure how to do that, aside
from doing it manually.

2. I'm thinking I might be able to make a 'master' document, that contains
all elements, and then create some way to filter which elements are shown
depending on the price book version. The problem with this is that it means
filtering individual columns in some tables, whole sections, or even
individual lines. No idea how to do that. The other down side to this method
is that it limits flexibility - all versions are a subset of the master
version, so it's possible I may be stuck making duplicate sections at some
point.

3. Something else I haven't thought of yet.