Creating Lists in microsoft access

[mndauber]

In microsoft access, is it possible to basically create a list? In Access, I have a company, and I want each company to have a list of products. I can't figure out how to have each company have their own list of products, attributed to that company. Ideally, I would also have a database of products, and then the companies list would reference the list of products, but, I might have multiple companies producing the same products, and almost every company will be producing more than one product. Is there any way to do this in microsoft access? If so, some advice or resources I could look at would be greatly appreciated. Thanks!




In microsoft access, is it possible to basically create a list? In Access, I have a company, and I want each company to have a list of products. I can't figure out how to have each company have their own list of products, attributed to that company. Ideally, I would also have a database of products, and then the companies list would reference the list of products, but, I might have multiple companies producing the same products, and almost every company will be producing more than one product. Is there any way to do this in microsoft access? If so, some advice or resources I could look at would be greatly appreciated. Thanks!

[ArviLaanemets]

tblCompanies: CompanyID, CompanyName, ...;
tblProducts: ProductID, PartNo, ProductName, ...;
tblCompanyProducts: CPID, CompanyID, ProductID.

Depending on your business logic, you may need additional tables, like
tblCompanyProductStatus: CPSID, CPID, StatusDate, ProductStatus (where ProductStatus may have values for statuses like "active" or "obsolete")

Now, you can at any time run a query like

SELECT p.* FROM tblCompanyProducts cp INNER JOIN tblProducts p ON p.ProductID = cp.ProductID WHERE cp.CompanyID = 1

to get a list of products for specific company

[mndauber]

Thank you so much for your answer! I am fairly new to access, and was wondering if you could explain that query, and how it is constructed, a little. That would be super awesome. Here are the table names and fields I used in my database for reference.
Offering DB is the product database, which has the fields Offering ID, Offering Code, Offering Name.
Company Offering DB is the in between database, which has the fields Company Offering ID, Offering ID, and Company Website
Company DB is the company database, which has the fields Company Website.

Ideally I would be able to construct two queries. One which takes in a Company Website, and returns a list of all products/offering associated with that company, and a second which takes in an Offering Code, and returns a list of all companies associated with that product.

I greatly appreciated your assistance, and thank you in advance for your advice.

[ArviLaanemets]

At start some termins.
A database (DB) is your application in corpore - all tables, forms, queries and reports;
A table is an entity in database where you store some data, which are related somehow. In your database are tables for company info, product info (Offerings), and for linking products and companies (CompanyOfferings);
A table is composed of fields and rows. Any field contains specific type of data - avoid fields having multiple layers of information (i.e. instead of full name of person it is better to have separate fields for fore- and last name). Every row contains a set of data having something common;
Every table must have unique primary key (the table is indexed on this primary key, indexing is needed for Access to optimize queries). Usually is best to have an autonumeric field as primary key, as then you don't have bother with creating proper and unique value for new entry, but you can have primary key entered manually or generated using form events too. And you can have compsite primary keys - but this may cause problems without proper coding sometimes. You can have additional indexes too, when it is reasonable, but don't create them without need too;
When 2 tables have information, which is linked somehow, then you enter the linked field(s) (usually primary key, but it is not a rule) values from one table into fields in another table. Those fields have common name Foreign Keys. It is a good practice to have to index the table on those foreign keys;
Don't use special characters in table or in field names (except underscore). A space is not banned entirely, but better avoid spaces too - it makes writing VBA code later easier. (I.e. instead field [Company Website] have field CompanyWebsite or Company_Website in your table;
Then there are queries, which will read info from your tables and present this info in some modified form. Queries may be saved ones, or you define them in code, or you enter them as sources for forms and reports;
You will use tables, and you will use queries in your application. To avoid confusion what you are using, apply some naming conventions, like all tables are named like tTableName or tblTableName, and all queries are named like qQueryName or qryQueryName.

What type of field is Company Website? Text? Web Address? Link to Web Address? Anyway it probably contains some long text, which makes indexing on this field slow. So I think it is better to design your tables like:
tblOfferings: OfferingID, OfferingCode, OfferingName;
tblCompanies: CompanyID, CompanySite;
tblCompanyOfferings: COID, CompanyID, OfferingID.

Your create a saved query like

qOfferingList = SELECT c.CompanyID c.CompanySite, o.OfferingCode, o.OfferingName FROM (tblCompanyOfferings co INNER JOIN tblOfferings o ON o.OfferingID = co.OdderingID) INNER JOIN tblCompanies c ON c.CompanyID = co.CompanyID ORDER BY c.CompanySite, o.OfferingName

and then you can run a query like

SELECT * FROM qOfferingList WHERE CompanyID = 1

to get a list of products for company with CompanyID = 1.
And you run a query (I assume here that OfferingCode is text field) like

SELECT * FROM qOfferingList WHERE OfferingCode = "SomeTextCode"

to get a list of companies associated with this product.

In both last queries you can define the list of fields you want to get instead of all fields, of-course.

[orange]

Here is a link to lots of info on Database Planning and Design--including concepts.
I recommend you spend some time watching the videos and work through 1 or 2 of the tutorial from RogersAccessLibrary to get some experience with the design process.
For your own development--don't jump into physical Access database without some appreciation of the underlying database concepts. Normalization, primary key, foreign key....

Can more than 1 company offer the same Product?
You will probably find that a junction table or bridging table will be required.
Google can be a great assistant and should not be overlooked.

Good luck with your project.