Database Design

[silvex]

Hi!
I'm trying to make a Database for registration of my clients, and I'm
stucked in one code. (I'm a "rockie" on the Access, unfortunately not like
Hamilton in F1)
The struture is as follows:
Database has 4 Tables:
tblClient (Date;ClientName;Category;Product;Supplier;UnitPrice).
tblCategory (CategoryID;CategoryName;Description).
tblProducts (ProductID;ProductName;CategoryID;SupplierID;UnitPrice) and
tblSupplier (SupplierID;CompanyName;Phone;Fax)
The products are classified in different categories
All the suppliers furnishes all the products but with different prices
Every Client is represented by a supplier (like an insurance company).
What I want is when I open the frmClient (based on tblClient) I’ll type the
date, the client name, I will select from the cboCategory the products I
want, (I already know how to do this) then I select the product in the
cboProduct, and after this when I select in the cboSupplier the CompanyName
it automatically fills the UnitPrice field with the price that the supplier I
choose is paying for that product.
Can anybody help me please!
Regards

 

[Steve]

To start, your tables need modified:
TblSupplier
SupplierID
SupplierName
Phone
Fax

TblClient
ClientID
ClientName
<<Other client contact fields>>
SupplierID

TblCategory
CategoryID
CategoryName
Description

TblProduct
ProductID
ProductName
CategoryID

TblSupplierProductPrice
SupplierProductPriceID
SupplierID
ProductID
UnitPrice

TblOrder
OrderID
OrderDate
ClientID
SupplierName

TblOrderDetail
OrderDetailID
OrderID
CategoryID
ProductID
Quantity
UnitPrice

Two things to note ---
1. Supplier is recorded in TblOrder rather than SupplierID. A client may
change suppliers at one time or another. Recording supplier allows the
supplier to be automatically filled in at all times with the client's
supplier when ClientID is recorded.
2. UnitPrice is recorded in TblOrderDetail rather than
SupplierProductPriceID. Supplier prices may change over time. Recording
UnitPrice allows the UnitPrice to be automatically filled in at all times
with any product price when the product and supplier are recorded.

You need to abandon your FrmClient and create a new form/subform. Name the
main form FrmOrder and it needs to be based on TblOrder. The main form needs
a combobox named ClientID for entering ClientID. The rowsource for the query
needs to be a query that includes TblClient and TblSupplier. The fields in
the query need to be ClientID, ClientName, SupplierID and SupplierName. Set
the Bound Column proprty to 1, Column Count to 3 and Column Width to 0;2;0.
The main form needs a hiddewn textbox named SupplierID for the SupplierID
field and a textbox named SupplierName for the SupplierName field.

When a client is selected in the combobox, the client's current SupplierName
will be automatically entered in the SupplierName textbox and the SupplierID
will be automatically entered in the SupplierID hidden textbox.

Name the subform SFrmOrderDetail and it needs to be based on TblOrderDetail.
The subform needs a combobox named CategoryID for selecting CategoryID. The
rowsource for the combobox can be TblCategory. Set the Bound Column property
to 1, Column Count to 2 and Column Width to 0;1.5. The subform needs a
combobox named ProductID for selecting ProductID. The rowsource for the
combobox needs to be a query that includes TblProduct and
TblSupplierProductPrice. The fields in the query need to be
ProductID,ProductName and CategoryID from TblProduct and SupplierID and
UnitPrice from TblSupplierProductPrice. Set the criteria for CategoryID to:
Forms!FrmOrder!SFrmOrderDetail.Form!CategoryID.
Set the criteria for SupplierID to:
Forms!FrmOrder!SupplierID.
Set the Bound Column property to 1, Column Count to 5 and Column Width to
0;1.5;0;0;1
Put the following code in the AfterUpdate event of the combobox:
Me!UnitPrice = Me!ProductID.Column(4)
Add a textbox to the subform for the UnitPrice field.

When a product is selected in the combobox, the product's current UnitPrice
will be automatically entered in the UnitPrice textbox.

The subform will be a continuous form. The ProductID combobox will be
dependent on the CategoryID combobox. I haven't included the details on how
to set this up. You can look the previous postings in the newsgroup for
setting this up - it is often addressed.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[silvex]

Steve
I tried all the steps you send to me, but they didn´t work. I think is
something to do with the relationships of the tables, or with the properties
of the comboBoxes, or perhaps I’m just to “green†or ignorant on this, to
understand your help. Please forgive my bad English and my worst knowledge of
database design and if you have some patience to read this again:
What I want something like this:

1) The Form will look like this : Frm Client

Date: ___(Field Date)_______
Name: ___(Field ClientName)_____________________
Age:__(fldAge) … other fields in tblClients ….
Category: __cboCategory____
Product: __cboProduct___ (selected from the AfterUpDate cboCategory)
Supplier: ____cboSupplier__(select from tblSupplier)__
UnitPrice: __txtBox (with the Price for that product that the supplier is
paying)

2) In Date field I’ll type the date of my Job
3) I type the ClientName, which can be a person or a company or whatever…
4) I type all the other fields that identify that particular Client
5) In the cboCategory I select the kind of Job I did to the Client (Ex:
Consultation;Surgery; Exams;…)
6) In the cboProduct appears the products that are related to the Category I
selected (Ex: Consultation=1)Gyne 2)Obst 3)Urgency 4)… Surgery= 1)Laparotomy
2)Salpingectomy 3)Hernia repair 4)…. and so on. As I told you I already know
how to do this.
7) Then when I select the supplier ( which is the insurance company that
will pay for the Job - Ex: AXA Insurance; MedicalInsurance;Doctorhelp;….)
the UnitPrice txtBox will automatically fills with the price that the
insurance company pays for that product, regardless of the client I insert.
Thanks for your Hellp
Silvex


"Steve" escreveu: