Trying to understand why the order changes the results

[SuperDude_123]

With the help of a coworker, we finally got what I'm trying to obtain with SQL to work on Access. The problem that I don't understand is, why is it that if I switch my first and second symbol around, I get more results one way and less the other way? Shouldn't they be equal?

SELECT DISTINCT Database.Description, Database.[Part Number], Database.Manufacturer, Database.Symbol, Database.[Job Number], Database.File, Database.Directory
FROM [Database]
WHERE ( Symbol like '*' + [Enter First Symbol] + '*')
	and Database.[Job Number] is not null
	and Database.[Job Number] in (
		SELECT DISTINCT Database.[Job Number]
FROM [Database]
		where 
		( Symbol like '*' + [Enter Second Symbol] + '*')
and Database.[Job Number] is not null
	);

Also, when I run this, I only see all the filtered results based on what I was prompted to enter at the [Enter First Symbol], while I was thinking that it should show the results of both prompt windows. To solve this, I am thinking that I could simply trim my code from above to only have a SELECT value of Database.[Job Number] only, and use those returned results in a inner join fashion to show me all lines that meet have the Database.[Job Number] from the first query with the two entered symbols. Would this be the most optimal way to do this?

[CJ_London]

as previously advised in your other threads, use &, not + to concatenate your strings

To answer your thread question, you are now using a subquery - so you need to alias the table name so SQL does not confuse it with the main table. At the moment it is looking at the same data

It seems to me that despite all the suggestions made to solve your requirement in the preceding threads, you are ignoring them and continue down a different road.

To solve this, I am thinking that I could simply trim my code from above to only have a SELECT value of Database.[Job Number] only, and use those returned results in a inner join fashion to show me all lines that meet have the Database.[Job Number] from the first query with the two entered symbols. Would this be the most optimal way to do this?

that is very much aligned to what was proposed by me in your first thread and ignored by you - instead you tried a hybrid that does not work

I only see all the filtered results based on what I was prompted to enter at the [Enter First Symbol], while I was thinking that it should show the results of both prompt windows

even with the alias corrected, you will still only return records that meet the first criteria - albeit only those that also meet to second criteria. Why do you think otherwise?

[SuperDude_123]

For the benefit of others that may use my posts in the future as a newbies guide, here's what I've learned and had to do to make my problems work.

Firstly, to address the following:

It seems to me that despite all the suggestions made to solve your requirement in the preceding threads, you are ignoring them and continue down a different road.

that is very much aligned to what was proposed by me in your first thread and ignored by you - instead you tried a hybrid that does not work

I went about in a different way then the proposed solutions as I was having errors with the results. After utilizing a coworker who has SQL programming experience, we decided to try starting from scratch in a pure SQL environment. When we did one half of the work, the results were impressive and what we expected, but when we ran the two halves of the queries, he pointed out that the reason that I was having problems with the proposed solutions from my previous posts were that (and I still don't fully understand what this means, if you could help me understand this, it would be extremely helpful in having a clue of what's going on) I don't have a relational database, and I might have a non normalized set of data (still no clue what this means), which was the reason why things didn't work.

So the solution I came up with was to merge the results of our SQL with a UNION in Access, and things just worked!

My SQL code is the following, and for future references, to save my sanity, instead of entering the data (by using [data]) every time Access looked at it in the SQL code and having to repetitively enter it multiple times, I followed the MS example to make a form and a macro that ran my queries (links bellow).

My Union's Code:

SELECT Database.*
FROM [Database]
WHERE ( Symbol like '*' + [Forms]![Form1].[Filter_1] + '*')
	and Database.[Job Number] is not null
	and Database.[Job Number] in (
		SELECT  Database.[Job Number]
FROM [Database]
		where 
		( Symbol like '*' + [Forms]![Form1].[Filter_2] + '*')
and Database.[Job Number] is not null
	)


UNION SELECT Database.*
FROM [Database]
WHERE ( Symbol like '*' + [Forms]![Form1].[Filter_2] + '*')
	and Database.[Job Number] is not null
	and Database.[Job Number] in (
		SELECT  Database.[Job Number]
FROM [Database]
		where 
		( Symbol like '*' + [Forms]![Form1].[Filter_1] + '*')
and Database.[Job Number] is not null
	)
ORDER BY [Job Number];

Useful Links:

- Unions (MS Guide)
- I used this guide to run my Query input Forms, I found it more direct then the MS guide.
- Here's the MS guide that I am referring to above. I started with this but simplified it with the above guide and executed my Union Query with the help from the above non MS guide.

[CJ_London]

here are some links about normalisation

https://en.wikipedia.org/wiki/Database_normalization
https://www.sqa.org.uk/e-learning/MDBS01CD/page_26.htm

good luck with your project