| re: Relationship question.
Stewart Allen wrote:
Thank you very much for your detailed reply Stewart. Sorry to take so long
to answer but I've been away from the office.
We are a very small company manufacturing biologicals and selling both
wholesage and retail into a niche market. My part of the plan at the moment
is to design a database system for the company so that it can move away
from its present more time consuming record keeping. I have worked for this
company for many years and am now retired and part time, which is how I
ended up with this project.
My knowledge of Access and relational databases is very much in the
beginning stage of things. This list and its members have been a great
help.
Bill.
[color=blue]
> Hi Bill,
>
> No the fields don't have to have the same name in each table, only the
> same data type but it pays to have the same name so it is easier to
> understand why that field is there. i.e. you have 2 tables; People and
> BirthCountry. A person can only be born in one country but one counrty can
> have many births (this does not include people born at in international
> waters). tblPerson
> ***********
> PersonID (PK)
> FirstName
> LastName
> CountryID (FK) form tblCountry
> BirthDate
>
> tblCountry
> ***********
> CountryID (PK)
> Country
>
> The CountryID in tblPerson could be named BirthCountry as long as the data
> types are the same. What I do here is name the field CountryID in the
> tblPerson but type Birth Country in the Caption property for that field to
> make it easier for the user to understand what the field is for.
>
> You're just about there with your table relationships. Have a look at the
> Northwind Traders database that came with Access, its located in the
> samples folder under the MS Office\Office folder.
>
> What are you trying to achieve with your DB; are your tracking the buying
> of products only or are you selling them to customers?
>
> If you're selling them to customers you'll need a Customers table, Orders
> table, OrderLines table and a Products table. A customer can have many
> orders and an order can have many products so the need for the OrderLines
> table.
> tblCustomers
> ***************
> CustID (PK)
> CustName
> Address
>
> tblOrders
> **********
> OrderID (PK)
> CustID (FK) from tblCustomers
> OrderNo
> OrderDate
>
> tblOrderLines
> ***************
> LineID (PK)
> OrderID (FK) from tblOrders
> ProductID (FK) from tblProducts
> Quantity
> Price
>
> tblProdcuts
> *************
> ProductID (PK)
> Product
>
> This is for selling products to customers and doesn't include invoicing
> customers. One order can have many invoices because all the products might
> not be in stock.
>
> As you mentioned you are buying products from a Vendor (Supplier) I'd keep
> the same layout as above but change the tblCustomer to tblVendors and all
> the CustID's to VendID and add a VendID to the tblProducts. This way you
> can create a new order, select a vendor (supplier) and only select
> products from that vendor.
>
> tblProducts
> *************
> ProductID (PK)
> Product
> VendID (FK) form tblVendors
>
> Keep the tblOrders and the tblOrderLines table names. I don't understand
> why you have an Invoice table when you're buying products because an
> Invoice is used by a supplier of goods to charge the purchaser of those
> goods. A purchaser normally uses a purchase order (but not always) to
> track what goods the purchaser ordered.
>
> Hope this helps
> Stewart
>
>
> "William Bradley" <bradleyw@magma.ca> wrote in message
> news:NtidnePIWfpGtAmiU-KYuA@magma.ca...[color=green]
>> Some help with relationships would be appreciated. I have read up on them
>> but am still a bit vague ...
>>
>> The tables are as follows:
>>
>> 1. Main table -- this holds information about a product we buy and the[/color]
> name[color=green]
>> of the vendor gained from the Venor table via a combo box.
>> .
>> 2. Vendor table -- Name, address, phone etc.
>>
>> 3. Invoice table -- Provides the invoice number, product, amount and[/color]
> price.[color=green]
>> And is a subform of the main table.
>>
>> First of all do I need a field of the same name in each of the fields for
>> the relationship to occur.
>>
>> I presume the "Main table" to the "Invoice table" would be a one to many
>> relationship, because there will be a number of invoices for the same
>> product over the year.
>>
>> Again the relationship between "Vendor table" to the "Main table" would[/color]
> also[color=green]
>> be a "one to many as the same vendor can have a number of products.
>>
>> Thanks,
>>
>> Bill.
>>
>>[/color][/color]
| 
