What's wrong with this Query?

[prabha_friend]

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;

 

[plog]

I don't think BETWEEN is a valid comparison operator in a JOIN. Use math symbols:

Table1Value >= Table2ValueA AND Table1Value<=Table2ValueB

 

[plog]

Just checked and I think BETWEEN is a valid operator. So, that means I bet your fields that you are comparing aren't the datatype you think they are. Are the fields in your JOIN text or numbers?

 

[isladogs]

You haven't defined which field in the Jobs table is the link field.
Add something like the part I've shown in RED

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

I can also confirm using BETWEEN ... AND is valid in a join

 

[prabha_friend]

Just checked and I think BETWEEN is a valid operator. So, that means I bet your fields that you are comparing aren't the datatype you think they are. Are the fields in your JOIN text or numbers?

The Following fields are Number:
Years_of_Experience.Year_No
jobs.Atleast
Jobs.NotMoreThan

This Query works but not able to modify it. When I try to Modify it I am getting the below error message. Ever for Layout Changes!

"Between operator without And in query expression 'Years_of_Experience.Year_No BETWEEN jobs.Atleas'."

 

[prabha_friend]

You haven't defined which field in the Jobs table is the link field.
Add something like the part I've shown in RED



I can also confirm using BETWEEN ... AND is valid in a join

There is no Year_No in the jobs table

Anyways, tried your code but still getting the same error message I mentioned in my earlier post.

 

[isladogs]

What field in the Jobs table are you using to link the 2 tables?

 

[prabha_friend]

Jobs.Atleast and Jobs.NotMoreThan

with

YearsofExperience.Year_No

it's a "BETWEEN JOIN"

 

[isladogs]

I understand now.

So I'm guessing you're not really joining the 2 tables
If so, try removing the INNER JOIN like this

SELECT Jobs.*,Years_of_Experience.Year_No
FROM Jobs 
WHERE Years_of_Experience.Year_No BETWEEN Jobs.Atleast AND Jobs.NotMoreThan;

If not, will it let you do a screenshot of your query in design view?

 

[prabha_friend]

I understand now.
Will it let you do a screenshot of this as a query in design view?

No. Only on SQL View.

 

[isladogs]

I just edited my last post with code added - have a look

 

[plog]

This Query works but not able to modify it.

So then everything is fine. It works.

The reason you are unable to modify it is because Access Design View cannot represent that type of JOIN. So, to modify the query is going to require you to use SQL and not the Design View.

You can partly get around this by clicking through the error messages to get to Design View, making the changes you want, but then going back into SQL view and pasting back in the correct JOIN (using your BETWEEN).

 

[prabha_friend]

Even SQL Modifications results in Error Message. No Problem so far now as the atleat returns records. Happy to know that its a valid query and a different attempt too.. Thanks Plog and Ridders.

 

[isladogs]

Sorry but I disagree.
I created a query identical in idea to your original query for 2 tables with 'similar types of fields'
I got exactly your error message

I then modified it in the way I posted in post #9 & it runs without error.

Design view:

In other words it has 2 unlinked tables with BETWEEN .. AND used as the 'join' criteria

Code for the above is:

SELECT PRecords.PastoralRecordID, PRecords.PupilID, PRecords.DateOfIncident, PRecords.TermNumber
FROM PRecords, Sessions
WHERE (((PRecords.DateOfIncident) Between [Sessions].[StartDate] And [Sessions].[CalendarDate]));

The code I posted in #9 is that idea but for your tables
If you haven't yet done so, please try it!

SELECT Jobs.*,Years_of_Experience.Year_No
FROM Jobs 
WHERE Years_of_Experience.Year_No BETWEEN Jobs.Atleast AND Jobs.NotMoreThan;

 

[prabha_friend]

SELECT [Jobs with Experience Slabs].ID, [Jobs with Experience Slabs].jobTitle, Companies.NameVal, [Jobs with Experience Slabs].Skills, Locations.NameVal, [Jobs with Experience Slabs].JobPostedOn, [Jobs with Experience Slabs].ExperienceRequired
FROM ([Jobs with Experience Slabs] INNER JOIN Companies ON [Jobs with Experience Slabs].Company = Companies.ID) INNER JOIN Locations ON [Jobs with Experience Slabs].Location = Locations.ID
GROUP BY [Jobs with Experience Slabs].ID, [Jobs with Experience Slabs].jobTitle, Companies.NameVal, [Jobs with Experience Slabs].Skills, Locations.NameVal, [Jobs with Experience Slabs].JobPostedOn, [Jobs with Experience Slabs].ExperienceRequired
HAVING (((First([Jobs with Experience Slabs].Year_No)) Between 4 And 10));

returns 128 records

but

SELECT Jobs.*
FROM Jobs, Years_of_Experience
WHERE (((Years_of_Experience.Year_No) Between 4 And 10));

returns 3752 records...

 

[isladogs]

Prabha,

1. The first code you just posted is nothing like what you had before.
This uses 3 different tables & aggregates using GROUP BY and HAVING.
That's not something you mentioned previously
Obviously that's got around the original error or you wouldn't have got any records

2. The second query isn't checking the same thing.
You could try using SELECT DISTINCT but I doubt that's the issue.
Two unlinked tables could well give you many more records but if you look they will probably be pulling the same data repeatedly

The question is really whether the first query is giving you the correct results.
If it is, that's fine

 

[prabha_friend]

128 should be the job count for the Years of Experience ranging from 4 Years to 10 Years.

 

[The_Doc_Man]

The query that returned 3700+ records is a Cartesian JOIN (a.k.a. Permutation JOIN) that does not constrain the Jobs table at all. Therefore you get EVERY POSSIBLE COMBINATION of a record from the Jobs table and any matching record from Years_of_Experience. And given the set theory basis for SQL, that is actually the correct answer to the question you asked. Which was probably not the question you thought you asked.

 

[ashleedawg]

ooh, that's what it's called? Cartesian Join. Okay, now I have to find the other thread I was trying to explain that on. I knew what it was and what causes it; I just had another word for it. Thanks for the inadvertent lesson!

 

[NauticalGent]

The query that returned 3700+ records is a Cartesian JOIN (a.k.a. Permutation JOIN) that does not constrain the Jobs table at all. Therefore you get EVERY POSSIBLE COMBINATION of a record from the Jobs table and any matching record from Years_of_Experience. And given the set theory basis for SQL, that is actually the correct answer to the question you asked. Which was probably not the question you thought you asked.

Ya know Doc, I once did an append query in this manner with a brand new table and the result is now PK (AutoNumber) creates new records in 75000+ range and counting. Fortunately only I am aware of this so I bear the shame alone.

I knew the reason why I just would not have been able to explain it to anyone. That has all changed now thanks to your post. In other words I can dazzle anyone who is willing to listen with cool words like Cartesian and Permutation Joins. Brilliant! Thanks for being "la voce della chi non ha voce" (the voice of the voiceless). Itslians, hell all of Europe for that matter, have a way to make ordinary phases sound cool...