Trouble linking 3 Tables

  • Thread starter Thread starter rasper22
  • Start date Start date
R

rasper22

I all I am quite new to all this database designing.

I have 3 tables: Suppliers_table, Call_table, Transaction_table. The
Relationships are as follows: Multiple CALLS can be made to a
SUPPLIER. A CALL can also can contain Multiple TRANSACTIONS. I am
having difficulty getting this to work in Access.

Can someone please advise me on how I go about doing this?


Franz
 
I all I am quite new to all this database designing.

I have 3 tables: Suppliers_table, Call_table, Transaction_table. The
Relationships are as follows: Multiple CALLS can be made to a
SUPPLIER. A CALL can also can contain Multiple TRANSACTIONS. I am
having difficulty getting this to work in Access.

Can someone please advise me on how I go about doing this?
Click to expand...

So it's Suppliers --1:n-- Calls --1:n-- Transactions?

Your Suppliers table should have a primary key, SupplierID let's call
it; you need a SupplierID of the same datatype (Long Integer if you
use an Autonumber as the ID) in Calls, and you need to join the
Suppliers.SupplierID to the Calls.SupplierID field in the
Relationships window. Similarly, put a CallID as the primary key in
Calls, and join it to a CallID foreign key in Transactions.

What specifically have you tried, and what difficulties are you
having? It's a bit hard to solve a problem stated as "having
difficulty getting this to work"...

John W. Vinson[MVP]
 
John Vinson1 said:
So it's Suppliers --1:n-- Calls --1:n-- Transactions?

Your Suppliers table should have a primary key, SupplierID
let's call
it; you need a SupplierID of the same datatype (Long Integer
if you
use an Autonumber as the ID) in Calls, and you need to join
the
Suppliers.SupplierID to the Calls.SupplierID field in the
Relationships window. Similarly, put a CallID as the primary
key in
Calls, and join it to a CallID foreign key in Transactions.

What specifically have you tried, and what difficulties are
you
having? It's a bit hard to solve a problem stated as "having
difficulty getting this to work"...

John W. Vinson[MVP]
Click to expand...

The Problem is that a supplier has more than one SupplierID(different
depts. in the suppliers company that we deal with). So when a supplier
phones up he could refer to invoices that not only covers one Supplier
ID but several Supplier ID’s.
 
John's point remains.... you will need a unique ID for the supplier that you
can use. You can have "sub"-IDs for that supplier in other departments if
you wish, but you still will need a single ID for the supplier in your
database.

Otherwise, you're going to have to hope that each supplier has a unique
name, or a unique combination of name and address and phone number and fax
number and first-born child's name and ..... well, let's just say you need
that single, unique ID.

--

Ken Snell
<MS ACCESS MVP>



rasper22 said:
John Vinson1 said:
So it's Suppliers --1:n-- Calls --1:n-- Transactions?

Your Suppliers table should have a primary key, SupplierID
let's call
it; you need a SupplierID of the same datatype (Long Integer
if you
use an Autonumber as the ID) in Calls, and you need to join
the
Suppliers.SupplierID to the Calls.SupplierID field in the
Relationships window. Similarly, put a CallID as the primary
key in
Calls, and join it to a CallID foreign key in Transactions.

What specifically have you tried, and what difficulties are
you
having? It's a bit hard to solve a problem stated as "having
difficulty getting this to work"...

John W. Vinson[MVP]
Click to expand...

The Problem is that a supplier has more than one SupplierID(different
depts. in the suppliers company that we deal with). So when a supplier
phones up he could refer to invoices that not only covers one Supplier
ID but several Supplier ID's.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL:
http://www.dbforumz.com/Tables-DB-Design-Trouble-linking-ftopict222829.html
Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=768632
Click to expand...
 
So, Franze. Basically you are telling us that you need a Many-to-Man
relationship between your Supplier table & your Calls table

To do this, you must create an extra table - lets call i
'CallsToSuppliers' table. This table has 2 fields: SupplierID an
CallID, each being of Long Integer data type. In Table Design view
highlight both felds and create a combined Primry Key. Then, lin
each of these fields to their corresponding fields in your Supplie
table & Calls table

One other point I query: you say that one call can embody man
Transactions, but can one Transaction be covered by many calls? I
so, then you will need another Many-to-Many relationship between you
Transactions table & your Calls table (call i
'CallsForTransactons', perhaps). Create it in the same way as th
'CallsToSuppliers' table

Cheers, & I hope this helps

Graham
 
P.S

I forgot to mention

The joining table, 'CallsToSuppliers', must have each of its field
configured to be on the Many side of a One-to-Many relationship wit
the corresponding fields in your Suppliers & Calls tables
(Similarly, with the 'CallsForTransactons' table, if you need one.

O.K. So now you know

All the best

Graham
 
Back
Top