Mulltiple tables

[Waywreth]

I'm working with MS Access 2002. I have 3 tables.
Table 1 has information about a vendor.
Table 2 has information about the products the vendors sell and thusly have
the vendors listed multiple times - once per product they sell.
Table 3 has a list of customers.

Currently it's setup such that to add a product you must first add the
vendor. This is good. However, I need to setup the relationships such that
I can create a query and/or report that shows which products each customer
uses. My issue arises in that each customer uses multiple vendors, and I
can't link using a one to many relationship.


Please can anyone help me? This is driving me nuts.

 

[tina]

the direct relationship is actually between Customers and Products, as a
many-to-many relationship: one customer may buy many products AND one
product may be purchased by many customers. in Access, a many-to-many
relationship must be resolved with a "linking" table.

try the following tables/relationships:

tblVendors
VendorID (primary key)
VendorName

tblProducts
ProductID (pk)
ProductName
VendorID (foreign key from tblVendors)

tblCustomers
CustomerID (pk)
FirstName
LastName

tblCustomerProducts [the linking table]
CustomerID (fk from tblCustomers)
ProductID (fk from tblProducts)
Quantity
(you can use the two foreign key fields as a combination primary key for
this table; or, if you prefer, you may add an additional field - probably an
Autonumber - as the primary key.)

the relationships are:
tblVendors 1:n tblProducts
tblProducts 1:n tblCustomerProducts
tblCustomers 1:n tblCustomerProducts

you'll be able to query for the *vendors* a customer purchased from, because
though customers aren't linked to vendors directly, the products they
purchase are.

(*note*: if more than one vendor can sell the same product - as usually
occurs in the "real world", then there is actually a many-to-many
relationship between Vendors and Products, which should be resolved with a
linking table: tblVendorProducts
VendProdID (pk)
VendorID (fk from tblVendors)
ProductID (fk from tblProducts)

tblVendors 1:n tblVendorProducts
tblProducts 1:n tblVendorProducts)

hth

 

[Waywreth]

Thank you for the great advice! I've set the tables up as you mentioned. In
the 2nd half of your response - no, the vendors cannot sell the same
products, they're unique to each vendor.

A 2nd question does however arise - where (and how) do I list the customers
who use each vendor and the reverse, and how do I structure the query to pull
this information?

As a note my overall goals are thus:
1. Report/query listing which vendors each customer uses.
2. Report/query listing which customers each vendor has.

the direct relationship is actually between Customers and Products, as a
many-to-many relationship: one customer may buy many products AND one
product may be purchased by many customers. in Access, a many-to-many
relationship must be resolved with a "linking" table.

try the following tables/relationships:

tblVendors
VendorID (primary key)
VendorName

tblProducts
ProductID (pk)
ProductName
VendorID (foreign key from tblVendors)

tblCustomers
CustomerID (pk)
FirstName
LastName

tblCustomerProducts [the linking table]
CustomerID (fk from tblCustomers)
ProductID (fk from tblProducts)
Quantity
(you can use the two foreign key fields as a combination primary key for
this table; or, if you prefer, you may add an additional field - probably an
Autonumber - as the primary key.)

the relationships are:
tblVendors 1:n tblProducts
tblProducts 1:n tblCustomerProducts
tblCustomers 1:n tblCustomerProducts

you'll be able to query for the *vendors* a customer purchased from, because
though customers aren't linked to vendors directly, the products they
purchase are.

(*note*: if more than one vendor can sell the same product - as usually
occurs in the "real world", then there is actually a many-to-many
relationship between Vendors and Products, which should be resolved with a
linking table: tblVendorProducts
VendProdID (pk)
VendorID (fk from tblVendors)
ProductID (fk from tblProducts)

tblVendors 1:n tblVendorProducts
tblProducts 1:n tblVendorProducts)

hth

I'm working with MS Access 2002. I have 3 tables.
Table 1 has information about a vendor.
Table 2 has information about the products the vendors sell and thusly have
the vendors listed multiple times - once per product they sell.
Table 3 has a list of customers.

Currently it's setup such that to add a product you must first add the
vendor. This is good. However, I need to setup the relationships such that
I can create a query and/or report that shows which products each customer
uses. My issue arises in that each customer uses multiple vendors, and I
can't link using a one to many relationship.


Please can anyone help me? This is driving me nuts.

 

[tina]

well, you're welcome, but i have to say i'd been up all night when i posted
my answer (and it shows <g>), but i'll address certain shortcomings in a
minute. first, using the table design i posted earlier, here's how you could
write a query to list "the customers of each vendor", as

SELECT tblProducts.fkVendorID, tblVendors.VendorName, [FirstName] & " " &
[LastName] AS CustName
FROM ((tblCustomerProducts LEFT JOIN tblCustomers ON
tblCustomerProducts.fkCustomerID = tblCustomers.CustomerID) LEFT JOIN
tblProducts ON tblCustomerProducts.fkProductID = tblProducts.ProductID) LEFT
JOIN tblVendors ON tblProducts.fkVendorID = tblVendors.VendorID
GROUP BY tblProducts.fkVendorID, tblVendors.VendorName, [FirstName] & " " &
[LastName];

and a query to list "the vendors of each customer", as

SELECT tblCustomerProducts.fkCustomerID, [FirstName] & " " & [LastName] AS
CustName, tblVendors.VendorName
FROM ((tblCustomerProducts LEFT JOIN tblCustomers ON
tblCustomerProducts.fkCustomerID = tblCustomers.CustomerID) LEFT JOIN
tblProducts ON tblCustomerProducts.fkProductID = tblProducts.ProductID) LEFT
JOIN tblVendors ON tblProducts.fkVendorID = tblVendors.VendorID
GROUP BY tblCustomerProducts.fkCustomerID, [FirstName] & " " & [LastName],
tblVendors.VendorName;

now, please see my *next post*, for an alternate - and probably more
appropriate - tables/relationships design.

hth

Thank you for the great advice! I've set the tables up as you mentioned. In
the 2nd half of your response - no, the vendors cannot sell the same
products, they're unique to each vendor.

A 2nd question does however arise - where (and how) do I list the customers
who use each vendor and the reverse, and how do I structure the query to pull
this information?

As a note my overall goals are thus:
1. Report/query listing which vendors each customer uses.
2. Report/query listing which customers each vendor has.

the direct relationship is actually between Customers and Products, as a
many-to-many relationship: one customer may buy many products AND one
product may be purchased by many customers. in Access, a many-to-many
relationship must be resolved with a "linking" table.

try the following tables/relationships:

tblVendors
VendorID (primary key)
VendorName

tblProducts
ProductID (pk)
ProductName
VendorID (foreign key from tblVendors)

tblCustomers
CustomerID (pk)
FirstName
LastName

tblCustomerProducts [the linking table]
CustomerID (fk from tblCustomers)
ProductID (fk from tblProducts)
Quantity
(you can use the two foreign key fields as a combination primary key for
this table; or, if you prefer, you may add an additional field - probably an
Autonumber - as the primary key.)

the relationships are:
tblVendors 1:n tblProducts
tblProducts 1:n tblCustomerProducts
tblCustomers 1:n tblCustomerProducts

you'll be able to query for the *vendors* a customer purchased from, because
though customers aren't linked to vendors directly, the products they
purchase are.

(*note*: if more than one vendor can sell the same product - as usually
occurs in the "real world", then there is actually a many-to-many
relationship between Vendors and Products, which should be resolved with a
linking table: tblVendorProducts
VendProdID (pk)
VendorID (fk from tblVendors)
ProductID (fk from tblProducts)

tblVendors 1:n tblVendorProducts
tblProducts 1:n tblVendorProducts)

hth

I'm working with MS Access 2002. I have 3 tables.
Table 1 has information about a vendor.
Table 2 has information about the products the vendors sell and thusly have
the vendors listed multiple times - once per product they sell.
Table 3 has a list of customers.

Currently it's setup such that to add a product you must first add the
vendor. This is good. However, I need to setup the relationships

such
that

I can create a query and/or report that shows which products each customer
uses. My issue arises in that each customer uses multiple vendors, and I
can't link using a one to many relationship.


Please can anyone help me? This is driving me nuts.

 

[tina]

if you're actually supporting a "real-world" process of customers buying
products, then the tables/relationships i posted previously won't cut it.
what you really have are customer *orders* (or "purchases"), where one
customer may buy products on multiple separate occasions, and one customer
may buy multiple products on any one occasion.

if the above describes your process more fully, then tblVendors,
tblProducts, and tblCustomers (as previously described) will work fine.
*throw out tblCustomerProducts.* add the following tables, as

tblCustomerOrders
OrderID (pk)
fkCustomerID (fk from tblCustomers)
OrderDate

tblOrderDetails [linking table between tblOrders and tblProducts]
DetailID (pk)
fkOrderID (fk from tblOrders)
fkProductID (fk from tblProducts)
Quantity

relationships are:
tblVendors 1:n tblProducts
tblProducts 1:n tblOrderDetails
tblCustomers 1:n tblCustomerOrders
tblCustomerOrders 1:n tblOrderDetails

using this revised tables/relationships design, here's how you could
write a query to list "the customers of each vendor", as

SELECT tblProducts.fkVendorID, tblVendors.VendorName, [FirstName] & " " &
[LastName] AS CustName
FROM (((tblOrderDetails LEFT JOIN tblCustomerOrders ON
tblOrderDetails.fkOrderID = tblCustomerOrders.OrderID) LEFT JOIN tblProducts
ON tblOrderDetails.fkProductID = tblProducts.ProductID) LEFT JOIN
tblCustomers ON tblCustomerOrders.fkCustomerID = tblCustomers.CustomerID)
LEFT JOIN tblVendors ON tblProducts.fkVendorID = tblVendors.VendorID
GROUP BY tblProducts.fkVendorID, tblVendors.VendorName, [FirstName] & " " &
[LastName];

and a query to list "the vendors of each customer", as

SELECT tblCustomerOrders.fkCustomerID, [FirstName] & " " & [LastName] AS
CustName, tblVendors.VendorName
FROM (((tblOrderDetails LEFT JOIN tblCustomerOrders ON
tblOrderDetails.fkOrderID = tblCustomerOrders.OrderID) LEFT JOIN tblProducts
ON tblOrderDetails.fkProductID = tblProducts.ProductID) LEFT JOIN
tblCustomers ON tblCustomerOrders.fkCustomerID = tblCustomers.CustomerID)
LEFT JOIN tblVendors ON tblProducts.fkVendorID = tblVendors.VendorID
GROUP BY tblCustomerOrders.fkCustomerID, [FirstName] & " " & [LastName],
tblVendors.VendorName;

hth

well, you're welcome, but i have to say i'd been up all night when i posted
my answer (and it shows <g>), but i'll address certain shortcomings in a
minute. first, using the table design i posted earlier, here's how you could
write a query to list "the customers of each vendor", as

SELECT tblProducts.fkVendorID, tblVendors.VendorName, [FirstName] & " " &
[LastName] AS CustName
FROM ((tblCustomerProducts LEFT JOIN tblCustomers ON
tblCustomerProducts.fkCustomerID = tblCustomers.CustomerID) LEFT JOIN
tblProducts ON tblCustomerProducts.fkProductID = tblProducts.ProductID) LEFT
JOIN tblVendors ON tblProducts.fkVendorID = tblVendors.VendorID
GROUP BY tblProducts.fkVendorID, tblVendors.VendorName, [FirstName] & " " &
[LastName];

and a query to list "the vendors of each customer", as

SELECT tblCustomerProducts.fkCustomerID, [FirstName] & " " & [LastName] AS
CustName, tblVendors.VendorName
FROM ((tblCustomerProducts LEFT JOIN tblCustomers ON
tblCustomerProducts.fkCustomerID = tblCustomers.CustomerID) LEFT JOIN
tblProducts ON tblCustomerProducts.fkProductID = tblProducts.ProductID) LEFT
JOIN tblVendors ON tblProducts.fkVendorID = tblVendors.VendorID
GROUP BY tblCustomerProducts.fkCustomerID, [FirstName] & " " & [LastName],
tblVendors.VendorName;

now, please see my *next post*, for an alternate - and probably more
appropriate - tables/relationships design.

hth

Thank you for the great advice! I've set the tables up as you

mentioned.

In
the 2nd half of your response - no, the vendors cannot sell the same
products, they're unique to each vendor.

A 2nd question does however arise - where (and how) do I list the customers
who use each vendor and the reverse, and how do I structure the query to pull
this information?

As a note my overall goals are thus:
1. Report/query listing which vendors each customer uses.
2. Report/query listing which customers each vendor has.

the direct relationship is actually between Customers and Products, as a
many-to-many relationship: one customer may buy many products AND one
product may be purchased by many customers. in Access, a many-to-many
relationship must be resolved with a "linking" table.

try the following tables/relationships:

tblVendors
VendorID (primary key)
VendorName

tblProducts
ProductID (pk)
ProductName
VendorID (foreign key from tblVendors)

tblCustomers
CustomerID (pk)
FirstName
LastName

tblCustomerProducts [the linking table]
CustomerID (fk from tblCustomers)
ProductID (fk from tblProducts)
Quantity
(you can use the two foreign key fields as a combination primary key for
this table; or, if you prefer, you may add an additional field - probably an
Autonumber - as the primary key.)

the relationships are:
tblVendors 1:n tblProducts
tblProducts 1:n tblCustomerProducts
tblCustomers 1:n tblCustomerProducts

you'll be able to query for the *vendors* a customer purchased from, because
though customers aren't linked to vendors directly, the products they
purchase are.

(*note*: if more than one vendor can sell the same product - as usually
occurs in the "real world", then there is actually a many-to-many
relationship between Vendors and Products, which should be resolved

with

a

 

[NickV]

Hi Tina - Possibly you could help me address a problem I'm having with
creating a 2002 Access report. The DB in question has 6 tables. What I call
the Primary table has an Agency Name Prim key. The Primary table holds a
1:many relationship with each of the 5 other tables; Agency Name is the
common field between all (6) tables. I'd like to create a report that lists
tables/records the following order:

Primary table data including (Agency Name- unduplicated prim key)
List all records (0-many) from Table 2 matching Primary table (Agency Name)
List all records (0-many) from Table 3 matching Primary table (Agency Name)
List all records (0-many) from Table 4 matching Primary table (Agency Name)
List all records (0-many) from Table 5 matching Primary table (Agency Name)
List all records (0-many) from Table 6 matching Primary table (Agency Name)
Repeat above sequence

Ideally, I'd also like to page break when the 'Agency Name' changes and show
the 'Agency Name' at the top of each page. Also, I'd like to present
records from tables 206 in descending record date order.

I tried many wizard combinations but none seem to work. It seems that the
wizard will only tolerate listing 1:many when only 2 tables are involced.
Any advice you offer will be appreciated. Would emailing you the table
schema help?

Also, although Table 1 is tied relationally to the other 5 via Agency Name,
the other 5 prim keys are autnum "ID" - FYI

Thanks. - NickV

the direct relationship is actually between Customers and Products, as a
many-to-many relationship: one customer may buy many products AND one
product may be purchased by many customers. in Access, a many-to-many
relationship must be resolved with a "linking" table.

try the following tables/relationships:

tblVendors
VendorID (primary key)
VendorName

tblProducts
ProductID (pk)
ProductName
VendorID (foreign key from tblVendors)

tblCustomers
CustomerID (pk)
FirstName
LastName

tblCustomerProducts [the linking table]
CustomerID (fk from tblCustomers)
ProductID (fk from tblProducts)
Quantity
(you can use the two foreign key fields as a combination primary key for
this table; or, if you prefer, you may add an additional field - probably an
Autonumber - as the primary key.)

the relationships are:
tblVendors 1:n tblProducts
tblProducts 1:n tblCustomerProducts
tblCustomers 1:n tblCustomerProducts

you'll be able to query for the *vendors* a customer purchased from, because
though customers aren't linked to vendors directly, the products they
purchase are.

(*note*: if more than one vendor can sell the same product - as usually
occurs in the "real world", then there is actually a many-to-many
relationship between Vendors and Products, which should be resolved with a
linking table: tblVendorProducts
VendProdID (pk)
VendorID (fk from tblVendors)
ProductID (fk from tblProducts)

tblVendors 1:n tblVendorProducts
tblProducts 1:n tblVendorProducts)

hth

I'm working with MS Access 2002. I have 3 tables.
Table 1 has information about a vendor.
Table 2 has information about the products the vendors sell and thusly have
the vendors listed multiple times - once per product they sell.
Table 3 has a list of customers.

Currently it's setup such that to add a product you must first add the
vendor. This is good. However, I need to setup the relationships such that
I can create a query and/or report that shows which products each customer
uses. My issue arises in that each customer uses multiple vendors, and I
can't link using a one to many relationship.


Please can anyone help me? This is driving me nuts.