create a list of catalogs with cross referenced items

[Joe]

I am wanting to create a searchable list of catalogs in a school. I want
teachers to be able to either enter an item or choose from a list, then have
catalogs that offer that item shown.

 

[John Vinson]

I am wanting to create a searchable list of catalogs in a school. I want
teachers to be able to either enter an item or choose from a list, then have
catalogs that offer that item shown.

Ok; are you having some specific problem doing so? This would be quite
straightforward to design in Access - a table of Catalogs, a table of
Items, and a third table ItemsIncluded with links to the item table
and the catalog table. Populating the tables (i.e. putting in all of
the items in each catalog) will be a lot of work though!

John W. Vinson[MVP]

 

[Joe]

Thanks for the response. My problem is that I don't quite know how to link
the tables so that when you choose something it shows stuff from the other
table.
I don't want to list everything in the catalogs, just highlights and things
that teachers routinely search for. I have someone that will input data, I
just need help linking tables - I am not real good at setting the
relationships between tables.

 

[John Vinson]

Thanks for the response. My problem is that I don't quite know how to link
the tables so that when you choose something it shows stuff from the other
table.
I don't want to list everything in the catalogs, just highlights and things
that teachers routinely search for. I have someone that will input data, I
just need help linking tables - I am not real good at setting the
relationships between tables.

You need to identify the "Entities" - real-life things, persons or
events that you need to manage. In this case I'd guess that there are
only two types of Entities: Items and Catalogs.... right?

Does each Item appear in only one Catalog, or might several catalogs
carry the same item (different brands or models perhaps)? If not (i.e.
if each item appears uniquely in only one catalog), then you just need
two tables:

Catalogs
CatalogID Primary Key <autonumber>
CatalogName
DateOfIssue
<any other info about the catalog as an object you can hold in your
hand>

Items
ItemID Primary Key <autonumber>
CatalogID Long Integer <joined to Catalogs.CatalogID in the
Relationships window>
Description
Price
<anything else you need to know about this item>

You can create a Query joining the two tables, joining CatalogID to
CatalogID; if you put criteria on the Description, or the Price, or
both, and base a Form or Report on this query, you'll get just those
records matching the criteria, with the name of the catalog and the
name of the item displayed.

John W. Vinson[MVP]

 

[Joe]

John- First - thank you for you assistance. You are correct - I really
only need to manage about 100 catalogs - the catalog names and the main itmes
that are carried by the catalog. We get so many of them, people forget that
itmes can be found in many catlaogs so they only use 1 or 2 most of the time.
Tends to limit price range and options. At this time, I don't think I want
to take care of price - they change from year to year. The teachers just
really want to have a way to look for an item and see which of the catalogs
carry that item. They can then get the catalog from the shelves and look up
prices. There are several catalogs that all carry same or similar items
and then there are some specialty catalogs with things the others don't have.

I may try to follow your steps this weekend. What additional should I do to
work with many catalogs carrying the same item, and the specialty catalogs?

 

[John Vinson]

I may try to follow your steps this weekend. What additional should I do to
work with many catalogs carrying the same item, and the specialty catalogs?

You'll need *three* tables not two:

Catalogs
CatalogID <Primary Key>
CatalogName

Items
ItemID
Description
<other info about the item>

Listings
ItemID <link to Items>
CatalogID <link to Catalogs>
(price, or any info about *this* item in *this* catalog, if
appropriate)

You could use a Form based on Items with a subform based on Listings;
put a combo box on the subform bound to the catalogID but displaying
the catalog name, or just base the subform on a Query linking Catalogs
to Listings.

John W. Vinson[MVP]