Search form/and or criteria

[mike60smart]

See that just shows how much of a novice I am.
Cool I'll take a look and see how I can structure better. Might just need a table each for products, services, acccredations, contact details and then one for all the other 20 yes/no questions

The attached is an example of how it should be structured.

 

[renrenren]

Did you see post #2?

I did yes and thought I had replied to you, sorry.
I'll take a look tomorrow.

Thanks

 

[renrenren]

The attached is an example of how it should be structured.

Thanks for this!! I will take a look later and see how I can use to base the DB on.
The full spreadsheet is about company related data and what they do/make. So my starting point would be to look at making the 1st table that has an ID number per company so that I can use the same ID to link up further tables with product, service and accreditation data etc.

Thanks again

 

[renrenren]

Just another question, I am going to set up some tables to try and make this database the correct way but I just have a question.
As mentioned the database will be about companies and the products and services they provide.
I'm going to set up tables for company details, products, services and accreditations.

As an example the products table will include 70 products, any company could have anything between 1 and 70 products.
So to do a query from a from, I want to be able to select up to 3 products and for the query and the query needs to find all the companies that sell those products (along with other criteria)

How would this table need to be structured and how would I link the products to a company?

Thanks

 

[mike60smart]

I would recommend that you use Google to find out about Database Normalisation.

 

[MajP]

As an example the products table will include 70 products, any company could have anything between 1 and 70 products.
So to do a query from a from, I want to be able to select up to 3 products and for the query and the query needs to find all the companies that sell those products (along with other criteria)

If products are unique to a company then the relation is simple
TblCompanies
--CompanyID a unique primary key usually an autonumber but can be r some other known, non changing, unique number
--Other Company fields

TblProducts:
--ProductID a unique primary key usually an autonumber but can be a SKU or some other known, non changing, unique number
--Other FIelds describing the product
--CompanyID_FK a foreign Key relating back to the distributor

If however you have a lot of products that are sold by many vendors you will need another junction table to make a many to may

TblCompanies_Products
--CompanyID_FK
--ProductID_FK

Then you have a key for each company and for each product. So company 1 can sell product A,B,C and Comapny 2 could sell product A,C,E,F
Both sell (A,C).

If your query joins company to products and you filter on product A it will return all companies that sell product A.

 

[renrenren]

If products are unique to a company then the relation is simple
TblCompanies
--CompanyID a unique primary key usually an autonumber but can be r some other known, non changing, unique number
--Other Company fields

TblProducts:
--ProductID a unique primary key usually an autonumber but can be a SKU or some other known, non changing, unique number
--Other FIelds describing the product
--CompanyID_FK a foreign Key relating back to the distributor

If however you have a lot of products that are sold by many vendors you will need another junction table to make a many to may

TblCompanies_Products
--CompanyID_FK
--ProductID_FK

Then you have a key for each company and for each product. So company 1 can sell product A,B,C and Comapny 2 could sell product A,C,E,F
Both sell (A,C).

If your query joins company to products and you filter on product A it will return all companies that sell product A.

Thank you!!
Its deffo the latter, the products are not unique to a specific company, as you say a company a can makes product ABC, company 2 can make ADE etc.
Id like then to have a search form with multiple different combo boxes where I can select "Product A" from a product1 combo box then "Product B" from product 2 combo box etc and it will find all the listed products.

It does get more complicated as I would want to search for other things at the same time as products e.g location and sector they work in.

Sounds like a hella lot to work out for a novice!!!

Thanks

 

[MajP]

A lot easier and way more flexible to use a multi select listbox. Then you can pick as many products as you lik.

 

[renrenren]

A lot easier and way more flexible to use a multi select listbox. Then you can pick as many products as you lik.

woahhhhh hold on!

So what that is doing is kinda filtering down from table on the fly as you add the criteria with the option to pick And or Or?
and not running a query?

Thats well cool

 

[MajP]

Yes, Access forms have a filter property. This is an easy way to filter the current form, but not the most efficient. Fine for a small local database. If you were connected to another backend like SQL Server or a much larger dataset then you are better to create your filter and dynamically query.

The difference is in this approach you pull all the records and then filter. In the other you return only the records you need. Can be a huge difference if very large databases.

 

[renrenren]

Just an update to say that I managed to get a work around for this with some help with how to write it.
I created a query for the yes/no answers then created another query for the OR answers and added the name of the 1st query in the FROM field of the 2nd query. So really query 2 is running on the back of the records found by query 1.

 

[MajP]

I created a query for the yes/no answers then created another query for the OR answers and added the name of the 1st query in the FROM field of the 2nd query. So really query 2 is running on the back of the records found by query 1

I really have no idea what you are saying, but I guess it is good. If you are saying you made a query and saved it. Then made another query that uses the first query, that is a very common thing to do. Often much easier to write a query in steps than try to do it all at once.

 

[Uncle Gizmo]

I am going to set up some tables to try and make this database the correct way

If you do decide to bite the bullet then you may find my blog on Excel in Access helpful:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com