Relationship question.

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 name
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 price.
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 also
be a "one to many as the same vendor can have a number of products.

Thanks,

Bill.

re: Relationship question.

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=blue]
> 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=blue]
> 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=blue]
> 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=blue]
> be a "one to many as the same vendor can have a number of products.
>
> Thanks,
>
> Bill.
>
>[/color]

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]