PROMO DATABASE

[Kathrynlewis]

ACCESS DATABSE-
I manage promotional items and their distribution for my company. Is there an easy way to develop a promo database?
I need to have the distribution list in there, the orders they place, the list of promotional items, invoice, and the vendor information. Is there a template for such things? THanks.

 

[SkipVought]

Hi,

There are several database wizards in MS Access. Take a look and see if you can adapt what's there.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[dan1967]

The items you are asking about are pretty normal. You might want to check out the wizard. When you open Access you get a window asking if you want to create a new database using, and your choices are blank db or wizard. Select wizard and choose the one that best fits your needs. You can always add to or subtract from the objects the wizard creates.


Dan.

 

[Kathrynlewis]

Thanks. I looked at the wizard and decided to select the orders template. However, when I started deleting some of the stufff I don't need a number of error messages come popping up becasue of the intricate relationships throughout the database template. So for instance the template format includes Sale tax and discount, I took out both of those fields in the tables, queries, forms and reports, but everytime I go to preview the order in the switchboard I prompted with a discount to enter or sales tax. How do I get rid of these?

 

[SkipVought]

Hi,

My wizard (2000) required sales tax, at least in the first window.

Try editing the Form that this occurs on. Try to see whats happening with sales tax & discount. Somewhere, you ought to be able to set both to ZERO.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Kathrynlewis]

I am continuing to work on this database and need help. What I have done is taken the template and tweaked it to my needs. However, need hlp with the following:

Have a product catalogue ie:

A B C D E F
Category ID Code Item Price Totl Qty Total Price
100 01 Computer bag 25.00 5 125.00
02 Garment bag 17.50 15 262.50
03 Gym bag 19.50 3 58.50

200 01 Baseball hat 8.00 50 400.00
02 Bucket Cap 8.50 24 204.00
03 Visor 5.00 1 5.00
etc.

My problem is that each client (10) places orders as above. Further, there are several vendors I work with. i.e. I buy the computer bag from Vendor x, and Gym Bag from Vendor Y.

My question is how can create a form/report (and how many do I need to create) that will:
1.) Import each client order
2.) Consolidate all orders onto one report. (i.e the total number of items ordered for Computer bags combined between Client 1, 2,3 and 4.)
3.) Split out the total order per vendor. (i.e if item 100/01 and 200/02 is purchased from Vendor X, how do I create a report that will extract these lines and update one order form for Vendor X- but that it will do this for all 10 client orders?)

If anyone can help. I would GREATLY appreciate any advice! Thanks!

 

[nberryman]

It sounds to me you need some transaction tables so

TblCustomers

TblVendors

TblProducts

TblOrders

TblOrderDetails

TblOrders will hold the detail for the customer who is placing the order, order date etc and TblOrderDetails would be a One to Many related link that will allow an order to have many items and from many suppliers.

If you want to send me more detail to the e mail below I will have a go for you later.



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[SkipVought]

Hi Kathrynlewis,

You already have ProductCatalog and Vendors. I assume that your Vendors table has at least the following...

VendorNbr
Category
IDCode
VendItemID
VendPrice
...

Sounds to me like you also need the following tables/reports with at least the following fields...

ClientOrdersTable
ClientNbr
Category
IDCode
OrderQty
...

VendorOrdersTable
VendorNbr
VendItemID
OrderQty
...

First you import all client orders.

Then you query ProductCatalog & ClientOrders table to determine what product inventory is covered. (BTW, I would call TotQty, OnHandQty and TotalPrice, TotalValue since you are using the ProductCatalog as an Inventory database as well -- just a nit ;-) )

You will probably need another table that is your Order Policy for each item. At what point do you order, order lead time, order quantity, etc.

Depending on how precise you need to be, you can make soft and hard allocations for orders until the inventory for that item is physically allocated.

Once the allocation is accomplished, you may have client line items that have not been covered. That result along with the results of Order/Reorder policy from analyzing the Product Catalog, query with Vendors to produce a Vendor Orders table/report. If your system includes drop ship to clients direct from the vendor, you will need to include that info in the table/report as well.

Have fun! This is NOT a trivial task!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Kathrynlewis]

We definitely want to use this tool as an inventory databse too. As right now we maintain inventory manually on excel spreadsheets. I guess my question is how will the database be able to gauge inventory/stock levels? Do we need a table of current inventory items? The vendor does do drop shipments for us. One of our major problems is figuring out which client has not received which items.

After all this communication am not certain that I have set up this database properly. I need it to be simple and extremely user friendly for soemone that has zero knowledge of access (mine is EXTREMELY limited...as you can tell am sure) the primary purpose we need a database for is the following:

1.) Automate procedures and reduce administrative work
2.) Have all orders per client as individual orders in one location
3.)Consolidate all orders onto one order form
4.) Separate out the different vendors and orders per vendor
according to what the clients ordered from me
5.)Inventory management
6.)Charge the client/invoice
7.) Record invoices received from Vendor and whether payed
8) SIMPLE, SIMPLE, SIMPLE!
Thanks for comments on how I could best approach this-
in terms of tables needed, queries etc. Perhaps would be best to start from sratch vs. play with this template that I keep getting error messages from! Please help. Thanks!

 

[SkipVought]

K,

This is not the kind of help that can be offered easily in such a forum. You are talking about a comprehensive system design that includes a database. This is Tek-Tips. You need ALOT more that a few tips.

A good database design will, among other things, normalize data to reduce mintenance and increase performance. For instance, although it might make sense to see certain data on a form, that data may come from a multitude of tables that are "connected" by certain relationships. So the design of the database must be done from a data perspective rather than a use perspective.

Another instance, Your Client Orders table should really be at least 2 tables -- one table for header information and another table for detail information. This principle is called "normalizing" data.

You need to hire a systems analyst to help you through this project.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Kathrynlewis]

Great. Thanks for your help!