Suggestions?????

[Tia]

I'm in the process of designing a new database to assist our small business
to track pricing changes. I'm just wanting to set it up in the most
efficient way possible. Here's what I have is a list of vendors and a list
of products with pricing. My goal is to have a report that lists the
customer and the products they use with their cost. My dilemna is that some
of the products can be purchased from different vendors. Do I need to set up
a table for each vendor with their products? If so, when I create the
customer form, will I be able to select the vendor, than the product and have
the price filled in? I ideally would love to have a form for the Customer,
where you enter Customer_ID, then select the vendor that you want to purchase
the product from, then choose the product and have a description filled in,
and then the current price will be calculated from that information.

What I'm picturing is some type of combo box where you select the vendor,
than it will populate the products box with the products available to
purchase from that specific vendor. My hangup is that I'm not sure how that
happens if each of the vendor has it's own table of products--suggestions??

 

[Jeff Boyce]

Tia

Before you press on any further, turn off the PC and pick up paper and
pencil. "... a table for each vendor with their products" might be how
you'd do it if you were working with a spreadsheet, but Access is a
relational database.

The paper/pencil is for you to identify the things about which you want to
keep information (e.g., vendors, products, customers, customers' orders,
....) and how these are related. Draw out the things, then show how they are
related. For example, I'm going to guess that one vendor can have many
products, and so on.

When you have the entities (things you want to keep info about) and
relationships (self-explanatory), you're ready to turn your PC and Access
back on. Those entities are a pretty good bet for your tables. The
specific data elements you want to keep about each entity are your fields.

If "normalization" and "relational" aren't familiar terms, brush up on them
before you try to make Access do something it isn't designed for.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

What you have is a many-to-many relationship between Vendors and Products. A
many-to-many relationship is modelled by a separate table, so the tables
you'd need would be:

1. Customers: CustomerID, names, address etc.

2. Vendors: VendorID, names, address etc.

3. Products: ProductID, product description, etc, but, assuming not every
vendor will charge the same price per product, not unit price.

4. VendorProducts: This models the many-to-many relationship between
Vendors and Products, by having VendorID and ProductID foreign key columns
(NB, these should not be autonumbers, thigh the primary keys of Products and
Vendors which they reference can be), and a UnitPrice column as this is
specific to each vendor/product combination. In the jargon its said to be
functionally dependent on the key of VendorProducts.

In a form based on VendorProducts you'd have two combo boxes bound to
VendorID and ProductID and a text box bound to UnitPrice. Set up the combo
boxes to hide the VendorID and ProductID columns and show meaningful columns
like the vendor's name and product description. To do this for products for
example the combo box's properties would be:

ControlSource: ProductID

RowSource: SELECT ProductID, ProductDescription FROM Products ORDER BY
ProductDescription;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

You'll also have forms to enter data into Customers and Vendors and Products
of course, but they are very straightforward bound forms which the form
wizard can easily set up for you.

You'll now need another table to hold the data about each customers product
requirements and the vendors they acquire hen from. This is analogous to an
Orders table. Normally there would be an OrderDetails table relating this to
Products (see the sample Northwind database which comes with Access for an
example), but from your description it sound as though you simply need a
single table as you are storing data about each customer's requirements
rather than orders over a period of time. What follows is on that basis, but
bear in mind you'll need something akin to the Northwind setup if you are
recording separate orders over time.

I'll call this table CustomerRequirements. It is in fact another example of
a table which models a relationship, in this case between Customers and
VendorProducts. So again it has two foreign keys. CustomerID is one, but
the other is a composite foreign key of two columns VendorID and ProductID.
The primary key of VendorProducts is similarly a composite one made up of
these two columns. The primary key of CustomerRequirements is similarly all
three columns.

The form based on this table will have a combo box for Customers bound to
CustomerID but set up as above to show the customer name, another combo box
bound to VendorID set up in the same way, and another bound to ProductID,
also set up to show the product description. The last will be correlated
with the VendorID combo box by referencing it in its RowSource query like
this:

SELECT Products,ProductID, ProductDescription
FROM Products INNER JOIN VendorProducts
ON Products,ProductID = VendorProducts.ProductID
WHERE VendorID = Forms!frmCustomerRequirements!cboVendorID
ORDER BY ProductDescription;

where frmCustomerRequirements is the name of the form and cboVendorID is the
name of the combo box bound to the VendorID column.

To get the second combo box, which I'll name cboProductID, to show only
those products supplied by the selected vendor in cboVendorID's AfterUpdate
event procedure requery the second combo box with:

Me.cboProductID.Requery

You might like to consider making the frmCustomerRequirements form a subform
with a Customers form, likned on the CutsomerID columns. That way you would
not need a combo box bound to CustomerID in the subform as the value of the
current customer's CustomerID would be automatically inserted into this
column via the linking mechanism.

For a report you'd simply join the Customers, CustomerRequirements,
VendorProducts, Vendors and Products tables in a query, the last two both
being joined to VendorProducts. Include the relevant columns from Customers,
Products, VendorProducts and Vendors in the query's result table and base a
report on this query. Group the report by customer and include the product
and price data in the detail section.

Ken Sheridan
Stafford, England