Always output LOV

[Guest]

Have a HR db that is used for rating distribution. I always need to output
the below values even if there is not corresponding values. Tried the Nz
function but only seems to be working if there are ee's in the specific bucket

1
2
3
4
5
Y
Null

Thanks in advance for your assistance

 

[Jason Lepack]

Have a HR db that is used for rating distribution. I always need to output
the below values even if there is not corresponding values. Tried the Nz
function but only seems to be working if there are ee's in the specific bucket

1
2
3
4
5
Y
Null

Thanks in advance for your assistance

Some people are psychic. Some are not. I think that what you need is
a Left Join instead of an inner join

Now, since some people are not psychic... a little bit more info is
required.

Step 1)
Pretend that you know absolutely nothing about the database that you
are currently working on... ok, ready?

Step 2)
Read your post. If you understand what it is that you are asking then
that's cool and I won't bug you anymore on this post, but if you would
like more help then rephrase your question and give detail about what
exactly it is that you are trying to do.

Some things that are useful are:
- Expected Output
- Current Output
- the SQL of the query that you are working on
- structure of the tables that are being used in the query
- description of what the thought process behind the query is.

I appologize if I was too critical, but you need to make yourself
understood if you're going to get help.

Cheers,
Jason Lepack

 

[Guest]

Ok let me try again.

Below is an example of the sql to capture the number of ee's by rating. The
rating scale is
1
2
3
4
5
Y
Null

SELECT Nz([new Rating],"NA") AS Ratings, Count(nz([New Rating],0)) AS [# of
EE's by Rating]
FROM [Ent Wide Tot Comp Wksht]
GROUP BY Nz([new Rating],"NA");

In this instance the 5 rating was dropped as there were no employee's rated
a 5. I would still want to see 5 in the output but with a zero for the count.

 

[Douglas J. Steele]

Assuming you have a table (Let's call it RatingTable) that has the 7 values
in your rating scale (with NA, rather than Null for the last value of what
we'll call RatingValue), try:

SELECT RatingTable.RatingValue AS Ratings,
Sum(IIf(IsNull([New Rating]),0, 1)) AS [# of EE's by Rating]
FROM RatingTable LEFT JOIN [Ent Wide Tot Comp Wksht]
ON RatingTable.RatingValue = Nz([new Rating],"NA")
GROUP BY RatingTable.RatingValue

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok let me try again.

Below is an example of the sql to capture the number of ee's by rating.
The
rating scale is
1
2
3
4
5
Y
Null

SELECT Nz([new Rating],"NA") AS Ratings, Count(nz([New Rating],0)) AS [#
of
EE's by Rating]
FROM [Ent Wide Tot Comp Wksht]
GROUP BY Nz([new Rating],"NA");

In this instance the 5 rating was dropped as there were no employee's
rated
a 5. I would still want to see 5 in the output but with a zero for the
count.

Some people are psychic. Some are not. I think that what you need is
a Left Join instead of an inner join

Now, since some people are not psychic... a little bit more info is
required.

Step 1)
Pretend that you know absolutely nothing about the database that you
are currently working on... ok, ready?

Step 2)
Read your post. If you understand what it is that you are asking then
that's cool and I won't bug you anymore on this post, but if you would
like more help then rephrase your question and give detail about what
exactly it is that you are trying to do.

Some things that are useful are:
- Expected Output
- Current Output
- the SQL of the query that you are working on
- structure of the tables that are being used in the query
- description of what the thought process behind the query is.

I appologize if I was too critical, but you need to make yourself
understood if you're going to get help.

Cheers,
Jason Lepack

 

[Douglas J. Steele]

Sorry, all you should need is:


SELECT RatingTable.RatingValue AS Ratings,
Count([Ent Wide Tot Comp Wksht].[XXX]) AS [# of EE's by Rating]
FROM RatingTable LEFT JOIN [Ent Wide Tot Comp Wksht]
ON RatingTable.RatingValue = Nz([new Rating],"NA")
GROUP BY RatingTable.RatingValue

where XXX is any field in [Ent Wide Tot Comp Wksht] that will always have a
value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Assuming you have a table (Let's call it RatingTable) that has the 7
values in your rating scale (with NA, rather than Null for the last value
of what we'll call RatingValue), try:

SELECT RatingTable.RatingValue AS Ratings,
Sum(IIf(IsNull([New Rating]),0, 1)) AS [# of EE's by Rating]
FROM RatingTable LEFT JOIN [Ent Wide Tot Comp Wksht]
ON RatingTable.RatingValue = Nz([new Rating],"NA")
GROUP BY RatingTable.RatingValue

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok let me try again.

Below is an example of the sql to capture the number of ee's by rating.
The
rating scale is
1
2
3
4
5
Y
Null

SELECT Nz([new Rating],"NA") AS Ratings, Count(nz([New Rating],0)) AS [#
of
EE's by Rating]
FROM [Ent Wide Tot Comp Wksht]
GROUP BY Nz([new Rating],"NA");

In this instance the 5 rating was dropped as there were no employee's
rated
a 5. I would still want to see 5 in the output but with a zero for the
count.

Have a HR db that is used for rating distribution. I always need to
output
the below values even if there is not corresponding values. Tried the
Nz
function but only seems to be working if there are ee's in the
specific bucket

1
2
3
4
5
Y
Null

Thanks in advance for your assistance

Some people are psychic. Some are not. I think that what you need is
a Left Join instead of an inner join

Now, since some people are not psychic... a little bit more info is
required.

Step 1)
Pretend that you know absolutely nothing about the database that you
are currently working on... ok, ready?

Step 2)
Read your post. If you understand what it is that you are asking then
that's cool and I won't bug you anymore on this post, but if you would
like more help then rephrase your question and give detail about what
exactly it is that you are trying to do.

Some things that are useful are:
- Expected Output
- Current Output
- the SQL of the query that you are working on
- structure of the tables that are being used in the query
- description of what the thought process behind the query is.

I appologize if I was too critical, but you need to make yourself
understood if you're going to get help.

Cheers,
Jason Lepack

 

[Guest]

Thanks Douglas

I'm just learning how to use the Nz and sum if's so I apologize in advance
if my question seems redundant.

I did have a table called rating table I modified my sql as stated below and
got

join expression not supported.

I have the two tables in the design mode and joined them using the join
properties and choose left outer join and just modified in sql mode

here is the sql.

SELECT Ratingtable.ratingvalue AS Ratings,
Sum(IIf(IsNull([New Rating]),0,1)) AS [# of EE's by Rating]
FROM RatingTable LEFT JOIN [Ent Wide Tot Comp Wksht]
ON RatingTable.RatingValue = Nz([new Rating],"NA")
GROUP BY RatingTable.RatingValue

It's exactly like your's as my table and fields are the same

Assuming you have a table (Let's call it RatingTable) that has the 7 values
in your rating scale (with NA, rather than Null for the last value of what
we'll call RatingValue), try:

SELECT RatingTable.RatingValue AS Ratings,
Sum(IIf(IsNull([New Rating]),0, 1)) AS [# of EE's by Rating]
FROM RatingTable LEFT JOIN [Ent Wide Tot Comp Wksht]
ON RatingTable.RatingValue = Nz([new Rating],"NA")
GROUP BY RatingTable.RatingValue

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok let me try again.

Below is an example of the sql to capture the number of ee's by rating.
The
rating scale is
1
2
3
4
5
Y
Null

SELECT Nz([new Rating],"NA") AS Ratings, Count(nz([New Rating],0)) AS [#
of
EE's by Rating]
FROM [Ent Wide Tot Comp Wksht]
GROUP BY Nz([new Rating],"NA");

In this instance the 5 rating was dropped as there were no employee's
rated
a 5. I would still want to see 5 in the output but with a zero for the
count.

Have a HR db that is used for rating distribution. I always need to
output
the below values even if there is not corresponding values. Tried the
Nz
function but only seems to be working if there are ee's in the specific
bucket

1
2
3
4
5
Y
Null

Thanks in advance for your assistance

Some people are psychic. Some are not. I think that what you need is
a Left Join instead of an inner join

Now, since some people are not psychic... a little bit more info is
required.

Step 1)
Pretend that you know absolutely nothing about the database that you
are currently working on... ok, ready?

Step 2)
Read your post. If you understand what it is that you are asking then
that's cool and I won't bug you anymore on this post, but if you would
like more help then rephrase your question and give detail about what
exactly it is that you are trying to do.

Some things that are useful are:
- Expected Output
- Current Output
- the SQL of the query that you are working on
- structure of the tables that are being used in the query
- description of what the thought process behind the query is.

I appologize if I was too critical, but you need to make yourself
understood if you're going to get help.

Cheers,
Jason Lepack

 

[Guest]

HI Douglas

I saw your new note and tried that as well and am still getting the same error

join expression not supported

Thanks Douglas

I'm just learning how to use the Nz and sum if's so I apologize in advance
if my question seems redundant.

I did have a table called rating table I modified my sql as stated below and
got

join expression not supported.

I have the two tables in the design mode and joined them using the join
properties and choose left outer join and just modified in sql mode

here is the sql.

SELECT Ratingtable.ratingvalue AS Ratings,
Sum(IIf(IsNull([New Rating]),0,1)) AS [# of EE's by Rating]
FROM RatingTable LEFT JOIN [Ent Wide Tot Comp Wksht]
ON RatingTable.RatingValue = Nz([new Rating],"NA")
GROUP BY RatingTable.RatingValue

It's exactly like your's as my table and fields are the same

Assuming you have a table (Let's call it RatingTable) that has the 7 values
in your rating scale (with NA, rather than Null for the last value of what
we'll call RatingValue), try:

SELECT RatingTable.RatingValue AS Ratings,
Sum(IIf(IsNull([New Rating]),0, 1)) AS [# of EE's by Rating]
FROM RatingTable LEFT JOIN [Ent Wide Tot Comp Wksht]
ON RatingTable.RatingValue = Nz([new Rating],"NA")
GROUP BY RatingTable.RatingValue

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok let me try again.

Below is an example of the sql to capture the number of ee's by rating.
The
rating scale is
1
2
3
4
5
Y
Null

SELECT Nz([new Rating],"NA") AS Ratings, Count(nz([New Rating],0)) AS [#
of
EE's by Rating]
FROM [Ent Wide Tot Comp Wksht]
GROUP BY Nz([new Rating],"NA");

In this instance the 5 rating was dropped as there were no employee's
rated
a 5. I would still want to see 5 in the output but with a zero for the
count.

:

Have a HR db that is used for rating distribution. I always need to
output
the below values even if there is not corresponding values. Tried the
Nz
function but only seems to be working if there are ee's in the specific
bucket

1
2
3
4
5
Y
Null

Thanks in advance for your assistance

Some people are psychic. Some are not. I think that what you need is
a Left Join instead of an inner join

Now, since some people are not psychic... a little bit more info is
required.

Step 1)
Pretend that you know absolutely nothing about the database that you
are currently working on... ok, ready?

Step 2)
Read your post. If you understand what it is that you are asking then
that's cool and I won't bug you anymore on this post, but if you would
like more help then rephrase your question and give detail about what
exactly it is that you are trying to do.

Some things that are useful are:
- Expected Output
- Current Output
- the SQL of the query that you are working on
- structure of the tables that are being used in the query
- description of what the thought process behind the query is.

I appologize if I was too critical, but you need to make yourself
understood if you're going to get help.

Cheers,
Jason Lepack

 

[Douglas J. Steele]

I copied-and-pasted the SQL from my test query, so I know it works!

What are the date types of the fields in your two tables?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas

I'm just learning how to use the Nz and sum if's so I apologize in advance
if my question seems redundant.

I did have a table called rating table I modified my sql as stated below
and
got

join expression not supported.

I have the two tables in the design mode and joined them using the join
properties and choose left outer join and just modified in sql mode

here is the sql.

SELECT Ratingtable.ratingvalue AS Ratings,
Sum(IIf(IsNull([New Rating]),0,1)) AS [# of EE's by Rating]
FROM RatingTable LEFT JOIN [Ent Wide Tot Comp Wksht]
ON RatingTable.RatingValue = Nz([new Rating],"NA")
GROUP BY RatingTable.RatingValue

It's exactly like your's as my table and fields are the same

Assuming you have a table (Let's call it RatingTable) that has the 7
values
in your rating scale (with NA, rather than Null for the last value of
what
we'll call RatingValue), try:

SELECT RatingTable.RatingValue AS Ratings,
Sum(IIf(IsNull([New Rating]),0, 1)) AS [# of EE's by Rating]
FROM RatingTable LEFT JOIN [Ent Wide Tot Comp Wksht]
ON RatingTable.RatingValue = Nz([new Rating],"NA")
GROUP BY RatingTable.RatingValue

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok let me try again.

Below is an example of the sql to capture the number of ee's by rating.
The
rating scale is
1
2
3
4
5
Y
Null

SELECT Nz([new Rating],"NA") AS Ratings, Count(nz([New Rating],0)) AS
[#
of
EE's by Rating]
FROM [Ent Wide Tot Comp Wksht]
GROUP BY Nz([new Rating],"NA");

In this instance the 5 rating was dropped as there were no employee's
rated
a 5. I would still want to see 5 in the output but with a zero for the
count.

:

Have a HR db that is used for rating distribution. I always need to
output
the below values even if there is not corresponding values. Tried
the
Nz
function but only seems to be working if there are ee's in the
specific
bucket

1
2
3
4
5
Y
Null

Thanks in advance for your assistance

Some people are psychic. Some are not. I think that what you need is
a Left Join instead of an inner join

Now, since some people are not psychic... a little bit more info is
required.

Step 1)
Pretend that you know absolutely nothing about the database that you
are currently working on... ok, ready?

Step 2)
Read your post. If you understand what it is that you are asking then
that's cool and I won't bug you anymore on this post, but if you would
like more help then rephrase your question and give detail about what
exactly it is that you are trying to do.

Some things that are useful are:
- Expected Output
- Current Output
- the SQL of the query that you are working on
- structure of the tables that are being used in the query
- description of what the thought process behind the query is.

I appologize if I was too critical, but you need to make yourself
understood if you're going to get help.

Cheers,
Jason Lepack