Search form/and or criteria (1 Viewer)

renrenren

New member
Local time
Today, 10:39
Joined
Mar 31, 2021
Messages
17
I think the answer to this will be no....go build a better database but thought I would ask.

I have a database set up with different columns, the majority of them contain a yes/no reply but there are also columns that has data about the same subject but across a few fields, for example, animal1, animal2, animal3.

So I've delved into the access internet world and managed to work out how to make some search's work but on different forms. e.g 1 form that uses AND logic that just searches the yes/no columns and another that uses OR logic that searches over multiple columns using Like "*" & [Forms]![search]![animals] & "*"

The issue is when I want to try and combine the 2 searches onto one query/form the results just don't work. I presume that this is because of adding OR logic it means that when I run the query that its just using or logic?

I know this database is rubbish and it should be set up correctly with names tables and links but this is really just to be used as a proof of concept where if all is happy I can get budget/funding to have someone who knows Access to make it than a total noob!!

Thanks for taking a look, I've got this far from reading forums!

I've put some of the FROM and WHERE text below. I've cut loads out so if there are too many "(" that will be why.

FROM textdata
WHERE (((textdata.[Forms]![Full search]![Full assembly] & "*") AND ((textdata.Component) Like "*" & [Forms]![Full search]![Component] & "*") AND ((textdata.OMR) Like "*" & [Forms]![Full search]![OMR] & "*") AND ((textdata.Notes) Like "*" & [Forms]![Full search]![Keyword] & "*")) OR (((textdata.Accreditations2)=[Forms]![Full search]![Accred])) OR (((textdata.Accreditations3)=[Forms]![Full search]![Accred])) OR (((textdata.Accreditations4)=[Forms]![Full search]![Accred])) OR (((textdata.Accreditations5)=[Forms]![Full search]![Accred])) OR (((textdata.Accreditations6)=[Forms]![Full search]![Accred]));
 

mike60smart

Registered User.
Local time
Today, 10:39
Joined
Aug 6, 2017
Messages
846
I think the answer to this will be no....go build a better database but thought I would ask.

I have a database set up with different columns, the majority of them contain a yes/no reply but there are also columns that has data about the same subject but across a few fields, for example, animal1, animal2, animal3.

So I've delved into the access internet world and managed to work out how to make some search's work but on different forms. e.g 1 form that uses AND logic that just searches the yes/no columns and another that uses OR logic that searches over multiple columns using Like "*" & [Forms]![search]![animals] & "*"

The issue is when I want to try and combine the 2 searches onto one query/form the results just don't work. I presume that this is because of adding OR logic it means that when I run the query that its just using or logic?

I know this database is rubbish and it should be set up correctly with names tables and links but this is really just to be used as a proof of concept where if all is happy I can get budget/funding to have someone who knows Access to make it than a total noob!!

Thanks for taking a look, I've got this far from reading forums!

I've put some of the FROM and WHERE text below. I've cut loads out so if there are too many "(" that will be why.

FROM textdata
WHERE (((textdata.[Forms]![Full search]![Full assembly] & "*") AND ((textdata.Component) Like "*" & [Forms]![Full search]![Component] & "*") AND ((textdata.OMR) Like "*" & [Forms]![Full search]![OMR] & "*") AND ((textdata.Notes) Like "*" & [Forms]![Full search]![Keyword] & "*")) OR (((textdata.Accreditations2)=[Forms]![Full search]![Accred])) OR (((textdata.Accreditations3)=[Forms]![Full search]![Accred])) OR (((textdata.Accreditations4)=[Forms]![Full search]![Accred])) OR (((textdata.Accreditations5)=[Forms]![Full search]![Accred])) OR (((textdata.Accreditations6)=[Forms]![Full search]![Accred]));
Hi

Can you upload a zipped copy of your database?
 

renrenren

New member
Local time
Today, 10:39
Joined
Mar 31, 2021
Messages
17
Hi Mike, I have uploaded a sample of the database just so you can get the idea.
If you search if the AND logic on its own it works fine (the 1st 3 options) and if you search the OR logic (the last one) it also works fine.
but when you search together it fails.
What I think I want to happen is for it to work as if they are all AND's but using AND/OR to search multiple columns.

June7, i'll take a look.

Thanks
 

Attachments

  • test DB.zip
    34.1 KB · Views: 78

mike60smart

Registered User.
Local time
Today, 10:39
Joined
Aug 6, 2017
Messages
846
Hi the table structure is all wrong.

What do the Accreditations relate to?

When you have fields like this:-

Accreditation1
Accreditation2
Accreditation3
Accreditation4
Accreditation5

These are known as Repeating Groups.

Each Accreditation should be a record in a separate table and NOT fields in your current table.
 

renrenren

New member
Local time
Today, 10:39
Joined
Mar 31, 2021
Messages
17
Yeah I gathered that but wondered if there was a way around it rather than making separate tables. I know its not ideal but it wouldn't be for everyday use.

If I was to do it correctly then I would require a lot of tables as some of the columns contain over 30 different options which isn't an option but a lot of effort for something that wouldn't really be used.

Thanks tho
 

Isaac

Lifelong Learner
Local time
Today, 02:39
Joined
Mar 14, 2017
Messages
5,386
It's a bit hard to give advice outside the framing of any specific question. A few thoughts.

First, I appreciate the sense of humor in your first line. It gave me a good laugh. Hopefully no one would say it quite like that. : )

Second, including multiple (different) operands, like And and Or, in a single query logic, is always possible. It's a matter of concepts like hierarchy, order of operations, and of course--parenthesis. It may get ugly (especially in the Access IDE vs., for example, SQL Server) to look at, and (in my opinion) the sheer difficulty of reading long/complex ANDs and ORs in Access-based SQL .....in and of itself......is a big reason (sometimes) to split it into multiple queries. Sometimes it doesn't make sense to spend three days creating the perfect (un-readable) query, rather than 10 minutes creating 3 or 4 that are perfectly easy to understand and work the same.

I'm not quite clear on the connection you are drawing between the current setup being rubbish vs. searching And's and Or's in complex combinations, although definitely not saying there isn't one--I don't know your table setup.

Database development is like many other things. You simply MUST approach it as if it were incremental foundations, one after the other. If you are somewhat new at it, be aware that designing the tables is the first step, and MUST be done correctly. Everything else builds on that, everything else depends on that, and everything else gets easier (or harder) based on that. A minor-seeming flaw in the tables design can quickly morph into catastrophic levels of difficulty in doing much of anything else, and lead to reinventing many wheels thereafter. If I've said this once I've said it 100 times: If at all possible, stop what you are doing and ensure your table design is optimized. If you haven't learned how, stop doing other development on it and learn now.

Lastly, a thought on the "prototype" concept. First, I can't count the number of times I've developed something supposedly as a "prototype" for the big fancy system I.T. would one day create, only to find the database I created used for many months or even years thereafter - and in some cases, personally evaluated by the ".Net guys" who then concluded it couldn't be improved upon very much, and changed their mind about the planned upgrade. But putting that aside for a moment, think about the downstream consumers of your prototype project. What is the real purpose of a prototype? To show HOW something might work. There are two extremes on the continuum here. On the one extreme is to create something that works (produces the final intended result), but every single component - every "how" involved - has to be completely changed in the final platform. On the other extreme is to produce something where every single component involved is almost identical to how it will be in the final program. Your choice of where to fall on that continuum should probably be with an eye to truly demonstrate as many "hows" as possible. If I am on the SQL Server / .Net team, looking at your prototype, it would be more valuable to me if the way you designed your tables was a high-quality demonstration of just how I would/can design them, too - and in fact, would help drastically in the final data migration. On the other hand, if your table design is very undesirable, then I have to completely reimagine the whole setup from scratch - thus making the "prototype" not much of a prototype at all - and in fact, making data migration much more complicated!

A lot of people don't give enough thought to that last thing. The elements of the prototype ought to demonstrate a quality "how it is done" as much as you are able to, rather than be thought of as "well, it does what we said it would do" -- but has to be totally technically reimagined by the next team.

Therefore, if you are already convinced/aware that your current setup is rubbish, the first line of your post may be your intuition telling you how to proceed.
 

mike60smart

Registered User.
Local time
Today, 10:39
Joined
Aug 6, 2017
Messages
846
Yeah I gathered that but wondered if there was a way around it rather than making separate tables. I know its not ideal but it wouldn't be for everyday use.

If I was to do it correctly then I would require a lot of tables as some of the columns contain over 30 different options which isn't an option but a lot of effort for something that wouldn't really be used.

Thanks tho
I am sure that the process you need can be achieved with just a few tables.

If you can give us an example of the process required I am sure we can help.
 

renrenren

New member
Local time
Today, 10:39
Joined
Mar 31, 2021
Messages
17
Hey Isaac,
Thanks for the amazing reply! And totally see your points there.

When I said it was rubbish I meant that the structure is totally wrong and really I was being lazy and hoping that someone might know some magic code to make the database do what I wanted it to do...and that person would be amazing!!

Just for context the proposed database really is just taken from a massive Excel spreadsheet that has the fields set out in the way that's in the example database. The proof of concept would be for me to go " look we can take this spreadsheet and get someone to make a database better than this but look how you can pull out the data"

Again back on the lazy train I really should find the time to learn how to do this properly with the correct structure etc but again that could be a lot of effort to put in for someone to day, "cheers thanks, here's you money...."

I just don't know whether it's hard to learn how to do this and how long it would take. I suppose I've got to the point of exhausting YouTube videos....haha
 

mike60smart

Registered User.
Local time
Today, 10:39
Joined
Aug 6, 2017
Messages
846
Hey Isaac,
Thanks for the amazing reply! And totally see your points there.

When I said it was rubbish I meant that the structure is totally wrong and really I was being lazy and hoping that someone might know some magic code to make the database do what I wanted it to do...and that person would be amazing!!

Just for context the proposed database really is just taken from a massive Excel spreadsheet that has the fields set out in the way that's in the example database. The proof of concept would be for me to go " look we can take this spreadsheet and get someone to make a database better than this but look how you can pull out the data"

Again back on the lazy train I really should find the time to learn how to do this properly with the correct structure etc but again that could be a lot of effort to put in for someone to day, "cheers thanks, here's you money...."

I just don't know whether it's hard to learn how to do this and how long it would take. I suppose I've got to the point of exhausting YouTube videos....haha
OK Can you upload an example of the Excel file?
 

Isaac

Lifelong Learner
Local time
Today, 02:39
Joined
Mar 14, 2017
Messages
5,386
Hey Isaac,
Thanks for the amazing reply! And totally see your points there.

When I said it was rubbish I meant that the structure is totally wrong and really I was being lazy and hoping that someone might know some magic code to make the database do what I wanted it to do...and that person would be amazing!!

Just for context the proposed database really is just taken from a massive Excel spreadsheet that has the fields set out in the way that's in the example database. The proof of concept would be for me to go " look we can take this spreadsheet and get someone to make a database better than this but look how you can pull out the data"

Again back on the lazy train I really should find the time to learn how to do this properly with the correct structure etc but again that could be a lot of effort to put in for someone to day, "cheers thanks, here's you money...."

I just don't know whether it's hard to learn how to do this and how long it would take. I suppose I've got to the point of exhausting YouTube videos....haha
I see what you mean. Yeah - I understand now what your thought process was, but even if you could figure out how to make the search's work as-is, you would just be reinventing Excel in Access, rather than re-structuring the data as it should be in Access. Ultimately by structuring the tables right you'll be capable of demonstrating a far more vast capacity for searching/querying which will be to your benefit, hopefully.

I do sympathize with being tasked with mocking something up for, potentially, little reward or satisfaction. Putting a lot of effort into something that becomes throwaway work is emotionally & mentally draining.
 

renrenren

New member
Local time
Today, 10:39
Joined
Mar 31, 2021
Messages
17
Sorry it was replied in error. I can't really because it's work related but as an example there are 231 different options for just products or services
Would there need to be a table for each?
Also there are about 2000 rows of data
 

mike60smart

Registered User.
Local time
Today, 10:39
Joined
Aug 6, 2017
Messages
846
Would there need to be a table for each?
Also there are about 2000 rows of data
Sorry I have tried but you do not appear to be taking any notice of what we are saying. Luck with your project.
 

renrenren

New member
Local time
Today, 10:39
Joined
Mar 31, 2021
Messages
17
I see what you mean. Yeah - I understand now what your thought process was, but even if you could figure out how to make the search's work as-is, you would just be reinventing Excel in Access, rather than re-structuring the data as it should be in Access. Ultimately by structuring the tables right you'll be capable of demonstrating a far more vast capacity for searching/querying which will be to your benefit, hopefully.

I do sympathize with being tasked with mocking something up for, potentially, little reward or satisfaction. Putting a lot of effort into something that becomes throwaway work is emotionally & mentally draining.
I concur, learning the right way might be painful but will be worth it.
 

renrenren

New member
Local time
Today, 10:39
Joined
Mar 31, 2021
Messages
17
No. Probably just one table


So this is a tiny database?
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
 

Users who are viewing this thread

Top Bottom