How to I get my Access DB to list multiple vendors?

[Jesica]

I have a DB that has two tables:
(1) vendors
(2) categories (I have made the category field mult-select)

I have a relationship of one (vendor) to many (categories).
How do I get the two tables to "combine" and show me a vendor that may have
multiple categories?

 

[Douglas J Steele]

If you've got a one-to-many between vendor and categories, that means that a
category can only apply to one vendor. Is that what you want, or do you want
a many-to-many between vendor and categories (one vendor can have multiple
categories, and a category can apply to multiple vendors)?

For a true one-to-many, you'd need to add the Vendor Id as a foreign key in
the Categories table.

For a many-to-many, you need to introduce a new table that consists of the
Vendor Id and the Category Id, and then populate it appropriately.

In either case, you'll need to create a query that joins the 2 (or 3) tables
together.

 

[Keith]

I have a DB that has two tables:
(1) vendors
(2) categories (I have made the category field mult-select)

I have a relationship of one (vendor) to many (categories).
How do I get the two tables to "combine" and show me a vendor that may
have
multiple categories?

Open a new query in design view, include both tables and drag the fields
onto the grid as appropriate.

HTH - Keith.
www.keithwilby.com

 

[Jessica]

Doug - Thanks for the help. I have made a new table called Junction table
and made both the vendor ID and the Category ID primary keys. I have also
created a Query and dragged all the appropriate fields into it.
Where do I enter my info into? The vendor table and category tables? Or
the Junction Table??

 

[Douglas J Steele]

All three.

You put Vendor information in the Vendor table. You put Category information
in the Category table. You link vendors and categories in the junction
table.

A common approach would be to have a form bound to Vendors, linked to a
subform bound to the junction table. The subform would have a combobox based
on the Category table bound to the Category Id in the table.

 

[Ofer]

Noting if you don't want to.

Just use this sql to get all the vendors that has multiple categories

SELECT vendor, Count(vendor) AS CountOfvendor
FROM [Junction table]
GROUP BY vendor
HAVING Count(vendor)>1

' Chenge the names to suit you DB.

 

[Jessica]

I'm sorry, but I still don't get where I'm supposed to use the sql? I'm
relatively "green" in Access.

Noting if you don't want to.

Just use this sql to get all the vendors that has multiple categories

SELECT vendor, Count(vendor) AS CountOfvendor
FROM [Junction table]
GROUP BY vendor
HAVING Count(vendor)>1

' Chenge the names to suit you DB.

Okay, Ofer's reply was a bit over my head. I didn't quite understand what I
was supposed to do???

 

[Ofer]

Ok, The sql I provided it's to use in a query.
Create a new query, copy and paste the sql I provided you with and change
the name of the table and the name of the vendor id to suit the name of you
db.

Or provide me with the name of the Junction table name, and the vandor id
field name in that table and I will create the sql for you.

This Sql take the junction table, and count how many time the vandor apear
there, if he apear more then once, the query will return that vendor id, that
way you will know which vendor has more then one category.

I'm sorry, but I still don't get where I'm supposed to use the sql? I'm
relatively "green" in Access.

Noting if you don't want to.

Just use this sql to get all the vendors that has multiple categories

SELECT vendor, Count(vendor) AS CountOfvendor
FROM [Junction table]
GROUP BY vendor
HAVING Count(vendor)>1

' Chenge the names to suit you DB.

Okay, Ofer's reply was a bit over my head. I didn't quite understand what I
was supposed to do???

:

I have a DB that has two tables:
(1) vendors
(2) categories (I have made the category field mult-select)

I have a relationship of one (vendor) to many (categories).
How do I get the two tables to "combine" and show me a vendor that may have
multiple categories?

 

[Ofer]

To see if a vendor have multiple categories, you need to create a group by
query on the junction table, thet will count the number of entries for a
vendor.


SELECT vendor, Count(vendor) AS CountOfvendor
FROM [Junction table]
GROUP BY vendor
HAVING Count(vendor)>1

You need a junction table when you use a many to many relation ship, if the
relation ship is one to many, all you need is to store the vandor field in
the categories table.

Now if a category can have few vendors, then the relation ship ypu have it
many to many

 

[Jessica]

Okay, Ofer's reply was a bit over my head. I didn't quite understand what I
was supposed to do???

 

[BruceM]

VendorID and Category ID would be foreign keys, not primary keys, in the
junction table. In other words, they are fields of the same data type as
their primary key namesakes in the Vendor and Category tables. If VendorID
in the Vendors table has autonumber as its data type, VendorID in the
junction table would be Number. In all other cases the data type needs to
be the same in the two tables. You set the primary key in table design
view, but not the foreign key. A field becomes a foreign key by its
relationship to the primary key in another table.

 

[Jessica]

Okay, I think I have everything working....almost. When I go back to my
vendor input form (I have a subform that has multi-select categories). When
I chose a category or many categories, I get a message that says "You cannot
add or change a record because a related record is required in table
'categories'.

 

[Jessica]

If I posted this twice I apologize:
I think I'm almost "there" with the database. The only remaining problems
is when I am in my vendor form (which has a categories subform) and I select
a category, I get the following message:

You cannot add or change a record because a related record is required in
table 'categories'

Please help!

 

[Douglas J. Steele]

The only things that comes to mind is that however you're getting the list
of categories on your subform isn't by querying the table, or that you're
using a combobox on your subform, and you've bound the wrong column.

 

[Lori Prewitt]

Jessica,
Did you ever get the error message "you cannot add or change a record
because a related record is required in the table 'categories'" fixed. I'm
having the same error message and I'm not sure how to fix it. Any help would
be greatful.

 

[KARL DEWEY]

Please post the table structure for both tables.

What field are you using for Master/Child links for form Vendors and subform
Categories?