What's wrong with this Query?

[isladogs]

Also my thanks for explaining the cartesian join so clearly
I've always called them unlinked table queries!
They do have their uses ....occasionally

I just answered the original question
Then the OP decided he wanted something else!

That's fine ... but he could have said that earlier.
It would have saved me several fruitless responses!

 

[NauticalGent]

That's fine ... but he could have said that earlier.
It would have saved me several fruitless responses!

What else have you got to do with your time...? Run a business??

 

[isladogs]

Ha!
One that's going to go bankrupt if I keep wasting my time. LOL!

 

[prabha_friend]

My Question is Still Valid and Opened. I asked the Following:

What's wrong with this Query?

SELECT Jobs.*,Years_of_Experience.Year_No
FROM Jobs INNER JOIN Years_of_Experience ON Years_of_Experience.Year_No BETWEEN jobs.Atleast AND Jobs.NotMoreThan;

Meaning: When I copy and Paste the same exact SQL statement in another Query Builder (SQL View) it is giving error. So, What's wrong with this Query?

 

[isladogs]

Final answer from me
You can't do an INNER JOIN in this type of query.
By definition an INNER JOIN needs one field in each table to be linked
Over and out

 

[plog]

I got the answer. And its very unsatisfying.

If you paste this exact code into a new query it will not work:

SELECT Jobs.*,Years_of_Experience.Year_No
FROM Jobs INNER JOIN Years_of_Experience ON Years_of_Experience.Year_No BETWEEN jobs.Atleast AND Jobs.NotMoreThan;

If you add parenthesis around the ON portion of the JOIN it will:

SELECT Jobs.*,Years_of_Experience.Year_No
FROM Jobs INNER JOIN Years_of_Experience ON (Years_of_Experience.Year_No BETWEEN jobs.Atleast AND Jobs.NotMoreThan);

It gets better. Let's call the first one QBad and the second QGood. So, you have QGood in a query, you save it as a new query (let's call this MyQuery). When you reopen MyQuery in SQL view the QGood is gone and has turned into QBad--Access automatically removed those parenthesis, however the query object MyQuery still runs. MyQuery shows QBad, but it works. So you think you can just copy the SQL from MyQuery and paste it into a new query, but you can't. It's removed the parenthesis and it won't work as a new query because its QBad.

So in summary, there is no good answer, just Access being Access.

 

[NauticalGent]

Interesting discovery Plog. I guess the answer would be to build the query in SQL and run the query with VBA...?

 

[The_Doc_Man]

I think part of the problem may be in knowing what each table actually represents.

What (IN ENGLISH) does the Jobs table hold? Not field names. What BUSINESS ENTITY is in the Jobs table? Is this Job Descriptions? Open Jobs? Jobs already filled?

What (IN ENGLISH) does the Years_of_Experience table hold? Not field names. What BUSINESS ENTITY is in the Y_O_E table? Is this some sort of applicant data? Is this some sort of resume builder where the idea is to show the experience level of people already in jobs?

The problem I am having here is that I cannot "see" the natural relationship between the two tables "in my head" (I'm very visual) and you just dropped failing code on us with a very vague question, "What's wrong with this?"

To the others who were unaware of the Cartesian JOIN: We can thank Rene Descartes for that concept. He was a big name in the field of Analytical Geometry. We remember him in programming for his contributions to that field.

https://en.wikipedia.org/wiki/René_Descartes

 

[prabha_friend]

Actually I have started searching for jobs.

So I did a VBA program to mine all job details from monsterindia.com (Data Mining)

The result is a table(jobs) having all job details like its Job_ID;Job_Company;Skills_Required and the final field Experience_Required

Sample Data for Experience_Required field:
"0-2 years"
"2-5 years"
"2-8 years"
"4-8 years"
"6-10 years"

and so many combinations of Required Minimum and Maximum years of Experiences.

I wanted to normalize it the below:
A table having an list of Years starting from 0 to 25 (Years_of_Experience table)
Field name is "Year_No"
Values:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

If I group the data as the employers have given it: I will not get the "all of the" following jobs:
"2-5 years"
"2-8 years"
"4-8 years"

for minimum 5 years required.

P.S:
I have added two calculated columns called jobs.atleast and jobs.NotMorethan having minimum and maximum respectively. Hope you all got the "Big Picture" Now.

Hope you like my idea of job searching

 

[prabha_friend]

Why this sudden silence?

 

[Tieval]

Why don't you just trim it:
2-8 = 2
2-5 = 2

If they are asking for two to eight years they cannot object to you only having seven, if you are willing to take the pay packet they also cannot be too concerned if you have nine

 

[prabha_friend]

Can someone conclude this Question by telling which is the best way to get the required details? So that I can mark it as "SOLVED"

 

[Uncle Gizmo]

Why the sudden silence?

This can happen if you affectively ask a new question in the thread.

You may get a better response if you post the new question in a new thread.

Sent from my SM-G925F using Tapatalk

 

[The_Doc_Man]

Here is a thought which might help.

IF that "experience" field is ALWAYS of the format: number1 dash number2 YEARS ("n1-n2 years") then parse it first. You can do some string manipulation with INSTR to find the dash and the word "YEARS" and extract the digits from that clause, then convert the digit strings into true numbers with a VAL functions. Then you have the two required values ready-made for the BETWEEN n1 AND n2 qualifier.

As to what is wrong with a particular query: You said yourself that the query returns data, just not what you expected. So the query is not technically broken, it just asks the wrong question.

We responded that the query was malformed because it tried to relate the two tables in a way that wasn't an accurate interpretation of how (if at all) they were actually related. Your explanation doesn't really help us since the purpose of that table that contains the numbers 1 through 25 is kind of elusive.

I suspect the silence is due to either frustration or confusion.