ACCESS HELP!!!!

[ChrisLouie]

I am no familiar with Access at all so please send your help my way!!
Right now I have a Excel workbook that contains an Inventory list, Customer
Information List, and an order form.
The Order Form is linked to t he Inventory and Customer list using the
vlookup formula to populate Customer info (address, company, phone #) as well
as product # and qty in stock.

Is it possiible to set up something in access that will do the following:
1) When I have a new customer their information is automatically saved to
the customer list for any future orders.
2) When I enter a customer name the information (if already in the database)
automatically populates in the appropriate places.
3) The product inventory list automatically gets updated after an order form
is filled out. For example: We have 5 pairs of red socks in stock. I recieve
an order for 2 paris of red socks and once i print the invoice / order form
the inventory database automatically gets updated to show that we now have 3
pairs of red socks in stock.

Is this at all possible in Access???

Thank you!!!!

 

[Lynn Trapp]

Yes, it is not only possible in Access, but Access is the right place for
that. Of course, doing an inventory management system is a bit complicated,
but with some work it can be done.

 

[George Nicholson]

Access ships with a sample database (Northwind.mdb) that has some of what
you ask about. While it has fields in the Products table for "Qty In Stock"
and "Qty On Order", they seem to be placeholders since I don't see that
those values ever get updated, but I haven't looked at it very hard (I'm not
even sure whether QtyOnOrder refers to incoming inventory orders placed or
outgoing customer orders). You'd need to add a lot of that functionality
yourself, according to your business rules (when do OnHand/OnOrder change?
when ordered, when invoiced or when shipped? What happens to CustOrders when
out of stock?) , etc.

(Northwind.mdb usually in C:\ProgramFiles\MicrosoftOffice\OfficeXX\Samples)

There are also database Templates available for Inventory Management and
Order Entry (and several others). These are available either via Office
installation (templates aren't installed by default, so you might need to
re-run it) or the Office website.

It is highly unlikely that you will find any of these to be usable 'as-is',
but they might help to get you started.

HTH,

 

[Larry Linson]

There's very good help on inventory applications at MVP Allen Browne's site,
http://allenbrowne.com/tips.html. Take a look there, as well as looking at
the references cited by Lynn and George.

Is it possiible to set up something in access
that will do the following:

1) When I have a new customer their information
is automatically saved to the customer list for
any future orders.

There are multiple ways to handle this; a convenient one is to display the
customer information in a Subform of your Order/Invoice Form, selected by
using a Combo Box. If you use the Combo Box "Limit to List" property, you
can open a detail form for entering the customer data, if it's not in the
Customer Table.

2) When I enter a customer name the information
(if already in the database) automatically populates
in the appropriate places.

Using the Subform and ComboBox technique I just described, this will happen
automatically.

3) The product inventory list automatically gets
updated after an order form is filled out. For example:
We have 5 pairs of red socks in stock. I recieve
an order for 2 paris of red socks and once i print the
invoice / order form the inventory database automatically
gets updated to show that we now have 3
pairs of red socks in stock.

Keeping a "quantity on hand" field that requires updating may, or may not,
be the best way to define your database. See Allen's site for more
information.

Is this at all possible in Access???

Yes, indeed.

Larry Linson
Microsoft Access MVP