Search form/and or criteria (1 Viewer)

mike60smart

Registered User.
Local time
Today, 21:09
Joined
Aug 6, 2017
Messages
1,899
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.
 

Attachments

  • test DB.zip
    36.6 KB · Views: 145

renrenren

New member
Local time
Today, 21:09
Joined
Mar 31, 2021
Messages
17
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

New member
Local time
Today, 21:09
Joined
Mar 31, 2021
Messages
17
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

Registered User.
Local time
Today, 21:09
Joined
Aug 6, 2017
Messages
1,899
I would recommend that you use Google to find out about Database Normalisation.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:09
Joined
May 21, 2018
Messages
8,463
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

New member
Local time
Today, 21:09
Joined
Mar 31, 2021
Messages
17
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

You've got your good things, and you've got mine.
Local time
Today, 17:09
Joined
May 21, 2018
Messages
8,463
A lot easier and way more flexible to use a multi select listbox. Then you can pick as many products as you lik.
 

Attachments

  • MultiSelectListBoxControl V2.accdb
    1.4 MB · Views: 147

renrenren

New member
Local time
Today, 21:09
Joined
Mar 31, 2021
Messages
17
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

You've got your good things, and you've got mine.
Local time
Today, 17:09
Joined
May 21, 2018
Messages
8,463
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

New member
Local time
Today, 21:09
Joined
Mar 31, 2021
Messages
17
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

You've got your good things, and you've got mine.
Local time
Today, 17:09
Joined
May 21, 2018
Messages
8,463
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

Nifty Access Guy
Staff member
Local time
Today, 21:09
Joined
Jul 9, 2003
Messages
16,245
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:-

 

Users who are viewing this thread

Top Bottom