By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,122 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,122 IT Pros & Developers. It's quick & easy.

Relationship question.

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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" <br******@magma.ca> wrote in message
news:Nt********************@magma.ca...
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.

Nov 12 '05 #2

P: n/a
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.
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" <br******@magma.ca> wrote in message
news:Nt********************@magma.ca...
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.


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.