Select statement assistance

[Danielle]

Data Information:
table1: tblPayGradeStructure
Fields to be used: CurrGrade and CurrBreakpoint

table2: tblPredSalary
field to be used: PredSalary (note this field is alredy pulled into the
query)

what i need is for the query to look at [PredSalary] and compare it to each
[CurrBreakpoint] in the table1, and where [PredSalary] is either less than or
equal a specific [CurrBreakpoint] return the corresponding [CurrGrade]

I had tried may different select statements and have received syntax or
paran errors on every single one --- spent hours looking at this and can't
get it to work --- any help would be greatly appreciated!!!

 

[Allen Browne]

See Tom Ellison's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

 

[Marshall Barton]

Data Information:
table1: tblPayGradeStructure
Fields to be used: CurrGrade and CurrBreakpoint

table2: tblPredSalary
field to be used: PredSalary (note this field is alredy pulled into the
query)

what i need is for the query to look at [PredSalary] and compare it to each
[CurrBreakpoint] in the table1, and where [PredSalary] is either less than or
equal a specific [CurrBreakpoint] return the corresponding [CurrGrade]

I had tried may different select statements and have received syntax or
paran errors on every single one --- spent hours looking at this and can't
get it to work --- any help would be greatly appreciated!!!

Not sure I understand that, but maybe this will give you an
idea:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary <= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint) As PayGrade
FROM tblPredSalary As P

 

[Danielle]

Thank you Marshall, unfortuantely I do not understand your response.

Data Information:
table1: tblPayGradeStructure
Fields to be used: CurrGrade and CurrBreakpoint

table2: tblPredSalary
field to be used: PredSalary (note this field is alredy pulled into the
query)

what i need is for the query to look at [PredSalary] and compare it to each
[CurrBreakpoint] in the table1, and where [PredSalary] is either less than or
equal a specific [CurrBreakpoint] return the corresponding [CurrGrade]

I had tried may different select statements and have received syntax or
paran errors on every single one --- spent hours looking at this and can't
get it to work --- any help would be greatly appreciated!!!

Not sure I understand that, but maybe this will give you an
idea:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary <= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint) As PayGrade
FROM tblPredSalary As P

 

[Marshall Barton]

Since it is near impossible to describe a query in terms of
the query design window's grid, queries are almost always
communicated using their SQL view, especially when they
include a subquery or other nontrivial construct.

Open a new query and, without doing anything else, switch to
SQL view. Then Copy/Paste my suggested query to replace
whatever Access put in there by default. Then double check
the names I used to make sure they are the names you have.
Change any names where I made a mistake, then test by
switching to Datasheet view.
--
Marsh
MVP [MS Access]

Thank you Marshall, unfortuantely I do not understand your response.

Data Information:
table1: tblPayGradeStructure
Fields to be used: CurrGrade and CurrBreakpoint

table2: tblPredSalary
field to be used: PredSalary (note this field is alredy pulled into the
query)

what i need is for the query to look at [PredSalary] and compare it to each
[CurrBreakpoint] in the table1, and where [PredSalary] is either less than or
equal a specific [CurrBreakpoint] return the corresponding [CurrGrade]

I had tried may different select statements and have received syntax or
paran errors on every single one --- spent hours looking at this and can't
get it to work --- any help would be greatly appreciated!!!

Not sure I understand that, but maybe this will give you an
idea:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary <= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint) As PayGrade
FROM tblPredSalary As P

 

[Danielle]

Thanks Marsh, please bare with me - as I have not worked directly on the SQL
screen in the past.

In your text below - please clarify what the P. and G. represent...are those
the respective tables?

This may also help -- in my example -- there is a breakpoint associated with
each grade, if the predicted salary falls between two breakpoints, then the
lower of the two grades is the data I need to see.

Since it is near impossible to describe a query in terms of
the query design window's grid, queries are almost always
communicated using their SQL view, especially when they
include a subquery or other nontrivial construct.

Open a new query and, without doing anything else, switch to
SQL view. Then Copy/Paste my suggested query to replace
whatever Access put in there by default. Then double check
the names I used to make sure they are the names you have.
Change any names where I made a mistake, then test by
switching to Datasheet view.
--
Marsh
MVP [MS Access]

Thank you Marshall, unfortuantely I do not understand your response.

Danielle wrote:

Data Information:
table1: tblPayGradeStructure
Fields to be used: CurrGrade and CurrBreakpoint

table2: tblPredSalary
field to be used: PredSalary (note this field is alredy pulled into the
query)

what i need is for the query to look at [PredSalary] and compare it to each
[CurrBreakpoint] in the table1, and where [PredSalary] is either less than or
equal a specific [CurrBreakpoint] return the corresponding [CurrGrade]

I had tried may different select statements and have received syntax or
paran errors on every single one --- spent hours looking at this and can't
get it to work --- any help would be greatly appreciated!!!


Not sure I understand that, but maybe this will give you an
idea:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary <= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint) As PayGrade
FROM tblPredSalary As P

 

[Danielle]

Thank you Allen, although I understand the logic to a degree - programming in
SQL at this stage is beyond my ability. I've inherited this database and
need this calculation to work so we can cost out salary changes and a new pay
structure. I am really just beyond frustrated at this point - and tempted
to follow the first example in the article - creating a new column in my
paygradestructure table showing the range between the breakpoints and using
the 'between' function in the statement.

See Tom Ellison's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Data Information:
table1: tblPayGradeStructure
Fields to be used: CurrGrade and CurrBreakpoint

table2: tblPredSalary
field to be used: PredSalary (note this field is alredy pulled into the
query)

what i need is for the query to look at [PredSalary] and compare it to
each
[CurrBreakpoint] in the table1, and where [PredSalary] is either less than
or
equal a specific [CurrBreakpoint] return the corresponding [CurrGrade]

I had tried may different select statements and have received syntax or
paran errors on every single one --- spent hours looking at this and can't
get it to work --- any help would be greatly appreciated!!!

 

[Marshall Barton]

The P and G are aliases for the table names. [These are
sometimes necessary so you can distinguish which instance of
a table field is being referenced.] In this case, they are
used just to avoid retyping the table names.

The <= in the subquery's WHERE clause and the ORDER BY
clause determine which breakpoint is used. I guess I got it
wrong so change it to:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary >= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint DESC) As PayGrade
FROM tblPredSalary As P
--
Marsh
MVP [MS Access]

Thanks Marsh, please bare with me - as I have not worked directly on the SQL
screen in the past.

In your text below - please clarify what the P. and G. represent...are those
the respective tables?

This may also help -- in my example -- there is a breakpoint associated with
each grade, if the predicted salary falls between two breakpoints, then the
lower of the two grades is the data I need to see.

Since it is near impossible to describe a query in terms of
the query design window's grid, queries are almost always
communicated using their SQL view, especially when they
include a subquery or other nontrivial construct.

Open a new query and, without doing anything else, switch to
SQL view. Then Copy/Paste my suggested query to replace
whatever Access put in there by default. Then double check
the names I used to make sure they are the names you have.
Change any names where I made a mistake, then test by
switching to Datasheet view.

Thank you Marshall, unfortuantely I do not understand your response.


Danielle wrote:
Data Information:
table1: tblPayGradeStructure
Fields to be used: CurrGrade and CurrBreakpoint

table2: tblPredSalary
field to be used: PredSalary (note this field is alredy pulled into the
query)

what i need is for the query to look at [PredSalary] and compare it to each
[CurrBreakpoint] in the table1, and where [PredSalary] is either less than or
equal a specific [CurrBreakpoint] return the corresponding [CurrGrade]

I had tried may different select statements and have received syntax or
paran errors on every single one --- spent hours looking at this and can't
get it to work --- any help would be greatly appreciated!!!


:
Not sure I understand that, but maybe this will give you an
idea:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary <= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint) As PayGrade
FROM tblPredSalary As P

 

[Danielle]

Thank you for the help and clarification!

I seem to be getting the folloiwng
"Syntax error (missing operator) in query expression
'...(SELECT TOP 1 G.CurrGrade FROM tblPayGradeStructure as G WHERE
P.PredSalary >= G.CurrBreakpoint ORDER BY G.CurrBreakpoint DESC)'

The P and G are aliases for the table names. [These are
sometimes necessary so you can distinguish which instance of
a table field is being referenced.] In this case, they are
used just to avoid retyping the table names.

The <= in the subquery's WHERE clause and the ORDER BY
clause determine which breakpoint is used. I guess I got it
wrong so change it to:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary >= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint DESC) As PayGrade
FROM tblPredSalary As P
--
Marsh
MVP [MS Access]

Thanks Marsh, please bare with me - as I have not worked directly on the SQL
screen in the past.

In your text below - please clarify what the P. and G. represent...are those
the respective tables?

This may also help -- in my example -- there is a breakpoint associated with
each grade, if the predicted salary falls between two breakpoints, then the
lower of the two grades is the data I need to see.

Since it is near impossible to describe a query in terms of
the query design window's grid, queries are almost always
communicated using their SQL view, especially when they
include a subquery or other nontrivial construct.

Open a new query and, without doing anything else, switch to
SQL view. Then Copy/Paste my suggested query to replace
whatever Access put in there by default. Then double check
the names I used to make sure they are the names you have.
Change any names where I made a mistake, then test by
switching to Datasheet view.


Danielle wrote:
Thank you Marshall, unfortuantely I do not understand your response.


Danielle wrote:
Data Information:
table1: tblPayGradeStructure
Fields to be used: CurrGrade and CurrBreakpoint

table2: tblPredSalary
field to be used: PredSalary (note this field is alredy pulled into the
query)

what i need is for the query to look at [PredSalary] and compare it to each
[CurrBreakpoint] in the table1, and where [PredSalary] is either less than or
equal a specific [CurrBreakpoint] return the corresponding [CurrGrade]

I had tried may different select statements and have received syntax or
paran errors on every single one --- spent hours looking at this and can't
get it to work --- any help would be greatly appreciated!!!


:
Not sure I understand that, but maybe this will give you an
idea:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary <= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint) As PayGrade
FROM tblPredSalary As P

 

[Marshall Barton]

The elipsis in my query was just to show you where to put
any other fields you want the query to return (e.g. employee
name or ???). Either delete the three dots or replace them
with a list of fields.

Also double check that the names I used are correct for your
tables.
--
Marsh
MVP [MS Access]

I seem to be getting the folloiwng
"Syntax error (missing operator) in query expression
'...(SELECT TOP 1 G.CurrGrade FROM tblPayGradeStructure as G WHERE
P.PredSalary >= G.CurrBreakpoint ORDER BY G.CurrBreakpoint DESC)'

The P and G are aliases for the table names. [These are
sometimes necessary so you can distinguish which instance of
a table field is being referenced.] In this case, they are
used just to avoid retyping the table names.

The <= in the subquery's WHERE clause and the ORDER BY
clause determine which breakpoint is used. I guess I got it
wrong so change it to:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary >= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint DESC) As PayGrade
FROM tblPredSalary As P

Thanks Marsh, please bare with me - as I have not worked directly on the SQL
screen in the past.

In your text below - please clarify what the P. and G. represent...are those
the respective tables?

This may also help -- in my example -- there is a breakpoint associated with
each grade, if the predicted salary falls between two breakpoints, then the
lower of the two grades is the data I need to see.

:
Since it is near impossible to describe a query in terms of
the query design window's grid, queries are almost always
communicated using their SQL view, especially when they
include a subquery or other nontrivial construct.

Open a new query and, without doing anything else, switch to
SQL view. Then Copy/Paste my suggested query to replace
whatever Access put in there by default. Then double check
the names I used to make sure they are the names you have.
Change any names where I made a mistake, then test by
switching to Datasheet view.


Danielle wrote:
Thank you Marshall, unfortuantely I do not understand your response.


Danielle wrote:
Data Information:
table1: tblPayGradeStructure
Fields to be used: CurrGrade and CurrBreakpoint

table2: tblPredSalary
field to be used: PredSalary (note this field is alredy pulled into the
query)

what i need is for the query to look at [PredSalary] and compare it to each
[CurrBreakpoint] in the table1, and where [PredSalary] is either less than or
equal a specific [CurrBreakpoint] return the corresponding [CurrGrade]

I had tried may different select statements and have received syntax or
paran errors on every single one --- spent hours looking at this and can't
get it to work --- any help would be greatly appreciated!!!


:
Not sure I understand that, but maybe this will give you an
idea:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary <= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint) As PayGrade
FROM tblPredSalary As P

 

[Danielle]

Marsh - I can't thank you enough. This works perfectly!!!

ps. Any recommendations for a good text/book resource that a novice like me
would find helpful for SQL?

Thanks again!!! - Danielle

The elipsis in my query was just to show you where to put
any other fields you want the query to return (e.g. employee
name or ???). Either delete the three dots or replace them
with a list of fields.

Also double check that the names I used are correct for your
tables.
--
Marsh
MVP [MS Access]

I seem to be getting the folloiwng
"Syntax error (missing operator) in query expression
'...(SELECT TOP 1 G.CurrGrade FROM tblPayGradeStructure as G WHERE
P.PredSalary >= G.CurrBreakpoint ORDER BY G.CurrBreakpoint DESC)'

The P and G are aliases for the table names. [These are
sometimes necessary so you can distinguish which instance of
a table field is being referenced.] In this case, they are
used just to avoid retyping the table names.

The <= in the subquery's WHERE clause and the ORDER BY
clause determine which breakpoint is used. I guess I got it
wrong so change it to:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary >= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint DESC) As PayGrade
FROM tblPredSalary As P


Danielle wrote:
Thanks Marsh, please bare with me - as I have not worked directly on the SQL
screen in the past.

In your text below - please clarify what the P. and G. represent...are those
the respective tables?

This may also help -- in my example -- there is a breakpoint associated with
each grade, if the predicted salary falls between two breakpoints, then the
lower of the two grades is the data I need to see.

:
Since it is near impossible to describe a query in terms of
the query design window's grid, queries are almost always
communicated using their SQL view, especially when they
include a subquery or other nontrivial construct.

Open a new query and, without doing anything else, switch to
SQL view. Then Copy/Paste my suggested query to replace
whatever Access put in there by default. Then double check
the names I used to make sure they are the names you have.
Change any names where I made a mistake, then test by
switching to Datasheet view.


Danielle wrote:
Thank you Marshall, unfortuantely I do not understand your response.


Danielle wrote:
Data Information:
table1: tblPayGradeStructure
Fields to be used: CurrGrade and CurrBreakpoint

table2: tblPredSalary
field to be used: PredSalary (note this field is alredy pulled into the
query)

what i need is for the query to look at [PredSalary] and compare it to each
[CurrBreakpoint] in the table1, and where [PredSalary] is either less than or
equal a specific [CurrBreakpoint] return the corresponding [CurrGrade]

I had tried may different select statements and have received syntax or
paran errors on every single one --- spent hours looking at this and can't
get it to work --- any help would be greatly appreciated!!!


:
Not sure I understand that, but maybe this will give you an
idea:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary <= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint) As PayGrade
FROM tblPredSalary As P

 

[Marshall Barton]

Terrific! Glas to have helped.

I am a poor resource for book recommendations because I just
skim them for new ideas. However, I did like:

SQL Queries for Mere Mortals
by Hernamdez and Viescas
--
Marsh
MVP [MS Access]

Marsh - I can't thank you enough. This works perfectly!!!

ps. Any recommendations for a good text/book resource that a novice like me
would find helpful for SQL?

Thanks again!!! - Danielle

The elipsis in my query was just to show you where to put
any other fields you want the query to return (e.g. employee
name or ???). Either delete the three dots or replace them
with a list of fields.

Also double check that the names I used are correct for your
tables.
--
Marsh
MVP [MS Access]

I seem to be getting the folloiwng
"Syntax error (missing operator) in query expression
'...(SELECT TOP 1 G.CurrGrade FROM tblPayGradeStructure as G WHERE
P.PredSalary >= G.CurrBreakpoint ORDER BY G.CurrBreakpoint DESC)'

:
The P and G are aliases for the table names. [These are
sometimes necessary so you can distinguish which instance of
a table field is being referenced.] In this case, they are
used just to avoid retyping the table names.

The <= in the subquery's WHERE clause and the ORDER BY
clause determine which breakpoint is used. I guess I got it
wrong so change it to:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary >= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint DESC) As PayGrade
FROM tblPredSalary As P


Danielle wrote:
Thanks Marsh, please bare with me - as I have not worked directly on the SQL
screen in the past.

In your text below - please clarify what the P. and G. represent...are those
the respective tables?

This may also help -- in my example -- there is a breakpoint associated with
each grade, if the predicted salary falls between two breakpoints, then the
lower of the two grades is the data I need to see.

:
Since it is near impossible to describe a query in terms of
the query design window's grid, queries are almost always
communicated using their SQL view, especially when they
include a subquery or other nontrivial construct.

Open a new query and, without doing anything else, switch to
SQL view. Then Copy/Paste my suggested query to replace
whatever Access put in there by default. Then double check
the names I used to make sure they are the names you have.
Change any names where I made a mistake, then test by
switching to Datasheet view.


Danielle wrote:
Thank you Marshall, unfortuantely I do not understand your response.


Danielle wrote:
Data Information:
table1: tblPayGradeStructure
Fields to be used: CurrGrade and CurrBreakpoint

table2: tblPredSalary
field to be used: PredSalary (note this field is alredy pulled into the
query)

what i need is for the query to look at [PredSalary] and compare it to each
[CurrBreakpoint] in the table1, and where [PredSalary] is either less than or
equal a specific [CurrBreakpoint] return the corresponding [CurrGrade]

I had tried may different select statements and have received syntax or
paran errors on every single one --- spent hours looking at this and can't
get it to work --- any help would be greatly appreciated!!!


:
Not sure I understand that, but maybe this will give you an
idea:

SELECT P.PredSalary, . . .
(SELECT TOP 1 G.CurrGrade
FROM tblPayGradeStructure As G
WHERE P.PredSalary <= G.CurrBreakpoint
ORDER BY G.CurrBreakpoint) As PayGrade
FROM tblPredSalary As P