Field contents become field names in new query

[Guest]

I am using Access 2002. I have a table which is a big list of employees,
listed with their supervisors, and their performance in several different
categories. From there, I created a query that grouped them by supervisor and
category so I could get each supervisor's overall total in each category.

Now, I am running into a problem with my next step. I need to use the
different categories separately as part of an equation for each supervisor.
To further explain, let me give an example:

Say the categories are: Shift, OT, XOT, EQ, Late, NCNS, Unplan

I have summed each category for each supervisor. Problem is, each of the
categories is under a field called "Code." They are not a separate field
themselves. So, here is an example of what I'd now get in the query:

Supervisor Code Total
Meep Shift 4800
Meep OT 2000
Kermit Shift 4800
Kermit OT 500
Kermit XOT 50
Kermit Late 50
Kermit Unplan 480

What I'd need to do now is to use each category separately for the following
equation:

(EQ + Late + NCNS + Unplan)/(Shift + OT) - XOT

So, in the final query, all I want is each supervisor name, and the result
of this equation. Another possible problem is that each supervisor may have
some of those categories missing from their records. In other words, if
supervisor Meep's employees were never late for the time period involved,
late won't show up in her records. So, the equation above would still need to
work even with categories missing. The query would have to either assign 0's
to each missing category, or just know it is null, therefore it would run the
equation even without it.

I know this must sound very confusing, and this may be a pretty tough one.
I'll understand if nobody knows how to help. This one seems really tought to
me. I would GREATLY appreciate any assistance if somebody actually can help.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Douglas J. Steele]

Try creating a crosstab query based on your existing query. That'll give you
the columns named Shift, OT, XOT, EQ, Late, NCNS, Unplan. Base another query
on that crosstab query to give you the calculations (although you may be
able to alter the crosstab query to do that calculation as well)

 

[Guest]

The crosstab works but you need to add a phony set of records for all the
codes. I put the value of one in all of the phony records.

TRANSFORM Sum(Supervisor.Total) AS [The Value]
SELECT Supervisor.Supervisor
FROM Supervisor
GROUP BY Supervisor.Supervisor
PIVOT Supervisor.Code;


I am not sure what your formula is suppose to do but I built a second query
using the crosstab as data source. Weird results.

SELECT Supervisor_Crosstab.Supervisor, Supervisor_Crosstab.eq,
Supervisor_Crosstab.late, Supervisor_Crosstab.ncns,
Supervisor_Crosstab.shift, Supervisor_Crosstab.ot,
Supervisor_Crosstab.unplan, Supervisor_Crosstab.xot,
(IIf([eq]=0,0,[eq])+IIf([late]=0,0,[late])+IIf([ncns]=0,0,[late])+IIf([unplan]=0,0,[unplan]))/(IIf([shift]=0,0,[shift])+IIf([ot]=0,0,[ot]))-IIf([xot]=0,0,[xot]) AS Tot
FROM Supervisor_Crosstab;

 

[Guest]

GRRRR!!!! Access can be so annoying sometimes. Thank you both very much.
You've been a big help. I'm so close, but now I am running into a problem. I
created a crosstab query, and like Douglas J. Steele said, that gave me each
code as a field name in the new query.

The problems begin there. As I mentioned before, if a supervisor did not
have a certain code for the time period, it does not show up in their records
in the original data (the table), therefore it does not show up in the first
query. For example, this means supervisor Kermit may have NCNS, but it won't
even show up for supervisor Meep. Some folks had all the codes. The two that
some folks did not were NCNS and XOT.

It APPEARS that this is no problem for the crosstab query. It still brought
those two over as field names, but it left them blank for those who did not
have them. Silly me, I thought my problems were over, but Access decided to
mess me up some more. First, I began using the equation Karl Dewey supplied.
I had to make a few tweaks, of course, so it matched my information. First,
it gave me negatives, which made absolutely no sense. All numbers are
positive, and the only subtraction is XOT, which is cancelled overtime. Now,
obviously that cannot be more than OT, much less Shift + OT, so there is no
way the value could be negative.

Don't ask me what I did, but something I changed along the way got it right
and I was getting the correct values. Just one big problem.... anybody who
did not have the code for the time period (and therefore had that field blank
in the crosstab query) showed up with absolutely nothing as their result of
the equation. No 0, no error, not even a wrong answer. It just shows up
blank! What the heck?! Below, I am pasting two examples of exactly what I
tried only to get the same result:

Zero:

SELECT qry13Aug05MTDAttendByTL_Crosstab.Supervisor,
(IIf([EARLYQ]=0,0,[EARLYQ])+IIf([LATE]=0,0,[LATE])+IIf([NCNS]=0,0,[NCNS])+IIf([UNPLAN]=0,0,[UNPLAN]))/((IIf([SHIFT]=0,0,[SHIFT])+IIf([OT]=0,0,[OT]))-IIf([XOT]=0,0,[XOT])) AS TLTotalAttend
FROM qry13Aug05MTDAttendByTL_Crosstab;

Null:

SELECT qry13Aug05MTDAttendByTL_Crosstab.Supervisor,
(IIf([EARLYQ]=Null,0,[EARLYQ])+IIf([LATE]=Null,0,[LATE])+IIf([NCNS]=Null,0,[NCNS])+IIf([UNPLAN]=Null,0,[UNPLAN]))/((IIf([SHIFT]=Null,0,[SHIFT])+IIf([OT]=Null,0,[OT]))-IIf([XOT]=Null,0,[XOT])) AS TLTotalAttend
FROM qry13Aug05MTDAttendByTL_Crosstab;

I also tried doing Null,Null instead of Null,0 in each IIf clause in the
second example. I also tried removing the name of the query before Supervisor
(don't know why Access puts it there when I'm using one query) and instead
adding it before each code in the IIf clauses (Ex:
qry13Aug05MTDAttendByTL_Crosstab.NCNS) so everything had that. Same result
both ways.

This makes absolutely no freaking sense! With the IIf clauses I used, it
should plug a 0 (or a null in the one example) in for any code that is
missing for a particular person. This means there is no reason whatsoever
that this should be happening. I even took out all the extra crap so I could
just double check that I had entered the equation right. I did. Stripping all
the SQL crappola, here is EXACTLY what I got:

(EARLYQ + LATE+ NCNS + UNPLAN) / (SHIFT + OT) – XOT

That is EXACTLY the equation I needed, so obviously something is wrong with
Access's logic, and not mine or anybody who has tried to help me out. If
anybody can lend further help as to the possible problem, I would be so
thankful. This is really starting to frustrate me, because this should be
working!

-----------
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

The crosstab works but you need to add a phony set of records for all the
codes. I put the value of one in all of the phony records.

TRANSFORM Sum(Supervisor.Total) AS [The Value]
SELECT Supervisor.Supervisor
FROM Supervisor
GROUP BY Supervisor.Supervisor
PIVOT Supervisor.Code;


I am not sure what your formula is suppose to do but I built a second query
using the crosstab as data source. Weird results.

SELECT Supervisor_Crosstab.Supervisor, Supervisor_Crosstab.eq,
Supervisor_Crosstab.late, Supervisor_Crosstab.ncns,
Supervisor_Crosstab.shift, Supervisor_Crosstab.ot,
Supervisor_Crosstab.unplan, Supervisor_Crosstab.xot,
(IIf([eq]=0,0,[eq])+IIf([late]=0,0,[late])+IIf([ncns]=0,0,[late])+IIf([unplan]=0,0,[unplan]))/(IIf([shift]=0,0,[shift])+IIf([ot]=0,0,[ot]))-IIf([xot]=0,0,[xot]) AS Tot
FROM Supervisor_Crosstab;

Try creating a crosstab query based on your existing query. That'll give you
the columns named Shift, OT, XOT, EQ, Late, NCNS, Unplan. Base another query
on that crosstab query to give you the calculations (although you may be
able to alter the crosstab query to do that calculation as well)

 

[Guest]

I got it! I GOT IT! Okay, after all that, I just needed to take a break.
There was a slight mistake in both equations. I'll repaste them below so you
know what I mean:

Zero:

SELECT qry13Aug05MTDAttendByTL_Crosstab.Supervisor,
(IIf([EARLYQ]=0,0,[EARLYQ])+IIf([LATE]=0,0,[LATE])+IIf([NCNS]=0,0,[NCNS])+IIf([UNPLAN]=0,0,[UNPLAN]))/((IIf([SHIFT]=0,0,[SHIFT])+IIf([OT]=0,0,[OT]))-IIf([XOT]=0,0,[XOT])) AS TLTotalAttend
FROM qry13Aug05MTDAttendByTL_Crosstab;

Null:

SELECT qry13Aug05MTDAttendByTL_Crosstab.Supervisor,
(IIf([EARLYQ]=Null,0,[EARLYQ])+IIf([LATE]=Null,0,[LATE])+IIf([NCNS]=Null,0,[NCNS])+IIf([UNPLAN]=Null,0,[UNPLAN]))/((IIf([SHIFT]=Null,0,[SHIFT])+IIf([OT]=Null,0,[OT]))-IIf([XOT]=Null,0,[XOT])) AS TLTotalAttend
FROM qry13Aug05MTDAttendByTL_Crosstab;

The problem with the first equation: When a supervisor did not have a
specific code for the time frame involved, it wound up being blank for them
in the crosstab query, NOT 0, so looking for it to be 0, that equation did
not catch these. Instead of 0,0 in the IIf clauses, it should have been
Null,0.

The problem with the second equation: The way I did this, it was looking for
this field to actually say "Null," otherwise, it used what was in the field.
I should have changed it to "is Null," not "= Null." Once I changed it, it
worked beautifully, and I got the results I wanted. Below, I am pasting the
final CORRECT SQL to get the result I wanted:

SELECT qry13Aug05MTDAttendByTL_Crosstab.Supervisor, (IIf([EARLYQ] is
Null,0,[EARLYQ])+IIf([LATE] is Null,0,[LATE])+IIf([NCNS] is
Null,0,[NCNS])+IIf([UNPLAN] is Null,0,[UNPLAN]))/((IIf([SHIFT] is
Null,0,[SHIFT])+IIf([OT] is Null,0,[OT]))-IIf([XOT] is Null,0,[XOT])) AS
TLTotalAttend
FROM qry13Aug05MTDAttendByTL_Crosstab;

Thanks a million to the folks who helped! This was a tough one, but we got it!

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

GRRRR!!!! Access can be so annoying sometimes. Thank you both very much.
You've been a big help. I'm so close, but now I am running into a problem. I
created a crosstab query, and like Douglas J. Steele said, that gave me each
code as a field name in the new query.

The problems begin there. As I mentioned before, if a supervisor did not
have a certain code for the time period, it does not show up in their records
in the original data (the table), therefore it does not show up in the first
query. For example, this means supervisor Kermit may have NCNS, but it won't
even show up for supervisor Meep. Some folks had all the codes. The two that
some folks did not were NCNS and XOT.

It APPEARS that this is no problem for the crosstab query. It still brought
those two over as field names, but it left them blank for those who did not
have them. Silly me, I thought my problems were over, but Access decided to
mess me up some more. First, I began using the equation Karl Dewey supplied.
I had to make a few tweaks, of course, so it matched my information. First,
it gave me negatives, which made absolutely no sense. All numbers are
positive, and the only subtraction is XOT, which is cancelled overtime. Now,
obviously that cannot be more than OT, much less Shift + OT, so there is no
way the value could be negative.

Don't ask me what I did, but something I changed along the way got it right
and I was getting the correct values. Just one big problem.... anybody who
did not have the code for the time period (and therefore had that field blank
in the crosstab query) showed up with absolutely nothing as their result of
the equation. No 0, no error, not even a wrong answer. It just shows up
blank! What the heck?! Below, I am pasting two examples of exactly what I
tried only to get the same result:

Zero:

SELECT qry13Aug05MTDAttendByTL_Crosstab.Supervisor,
(IIf([EARLYQ]=0,0,[EARLYQ])+IIf([LATE]=0,0,[LATE])+IIf([NCNS]=0,0,[NCNS])+IIf([UNPLAN]=0,0,[UNPLAN]))/((IIf([SHIFT]=0,0,[SHIFT])+IIf([OT]=0,0,[OT]))-IIf([XOT]=0,0,[XOT])) AS TLTotalAttend
FROM qry13Aug05MTDAttendByTL_Crosstab;

Null:

SELECT qry13Aug05MTDAttendByTL_Crosstab.Supervisor,
(IIf([EARLYQ]=Null,0,[EARLYQ])+IIf([LATE]=Null,0,[LATE])+IIf([NCNS]=Null,0,[NCNS])+IIf([UNPLAN]=Null,0,[UNPLAN]))/((IIf([SHIFT]=Null,0,[SHIFT])+IIf([OT]=Null,0,[OT]))-IIf([XOT]=Null,0,[XOT])) AS TLTotalAttend
FROM qry13Aug05MTDAttendByTL_Crosstab;

I also tried doing Null,Null instead of Null,0 in each IIf clause in the
second example. I also tried removing the name of the query before Supervisor
(don't know why Access puts it there when I'm using one query) and instead
adding it before each code in the IIf clauses (Ex:
qry13Aug05MTDAttendByTL_Crosstab.NCNS) so everything had that. Same result
both ways.

This makes absolutely no freaking sense! With the IIf clauses I used, it
should plug a 0 (or a null in the one example) in for any code that is
missing for a particular person. This means there is no reason whatsoever
that this should be happening. I even took out all the extra crap so I could
just double check that I had entered the equation right. I did. Stripping all
the SQL crappola, here is EXACTLY what I got:

(EARLYQ + LATE+ NCNS + UNPLAN) / (SHIFT + OT) – XOT

That is EXACTLY the equation I needed, so obviously something is wrong with
Access's logic, and not mine or anybody who has tried to help me out. If
anybody can lend further help as to the possible problem, I would be so
thankful. This is really starting to frustrate me, because this should be
working!

-----------
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

The crosstab works but you need to add a phony set of records for all the
codes. I put the value of one in all of the phony records.

TRANSFORM Sum(Supervisor.Total) AS [The Value]
SELECT Supervisor.Supervisor
FROM Supervisor
GROUP BY Supervisor.Supervisor
PIVOT Supervisor.Code;


I am not sure what your formula is suppose to do but I built a second query
using the crosstab as data source. Weird results.

SELECT Supervisor_Crosstab.Supervisor, Supervisor_Crosstab.eq,
Supervisor_Crosstab.late, Supervisor_Crosstab.ncns,
Supervisor_Crosstab.shift, Supervisor_Crosstab.ot,
Supervisor_Crosstab.unplan, Supervisor_Crosstab.xot,
(IIf([eq]=0,0,[eq])+IIf([late]=0,0,[late])+IIf([ncns]=0,0,[late])+IIf([unplan]=0,0,[unplan]))/(IIf([shift]=0,0,[shift])+IIf([ot]=0,0,[ot]))-IIf([xot]=0,0,[xot]) AS Tot
FROM Supervisor_Crosstab;

Try creating a crosstab query based on your existing query. That'll give you
the columns named Shift, OT, XOT, EQ, Late, NCNS, Unplan. Base another query
on that crosstab query to give you the calculations (although you may be
able to alter the crosstab query to do that calculation as well)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am using Access 2002. I have a table which is a big list of employees,
listed with their supervisors, and their performance in several different
categories. From there, I created a query that grouped them by supervisor
and
category so I could get each supervisor's overall total in each category.

Now, I am running into a problem with my next step. I need to use the
different categories separately as part of an equation for each
supervisor.
To further explain, let me give an example:

Say the categories are: Shift, OT, XOT, EQ, Late, NCNS, Unplan

I have summed each category for each supervisor. Problem is, each of the
categories is under a field called "Code." They are not a separate field
themselves. So, here is an example of what I'd now get in the query:

Supervisor Code Total
Meep Shift 4800
Meep OT 2000
Kermit Shift 4800
Kermit OT 500
Kermit XOT 50
Kermit Late 50
Kermit Unplan 480

What I'd need to do now is to use each category separately for the
following
equation:

(EQ + Late + NCNS + Unplan)/(Shift + OT) - XOT

So, in the final query, all I want is each supervisor name, and the result
of this equation. Another possible problem is that each supervisor may
have
some of those categories missing from their records. In other words, if
supervisor Meep's employees were never late for the time period involved,
late won't show up in her records. So, the equation above would still need
to
work even with categories missing. The query would have to either assign
0's
to each missing category, or just know it is null, therefore it would run
the
equation even without it.

I know this must sound very confusing, and this may be a pretty tough one.
I'll understand if nobody knows how to help. This one seems really tought
to
me. I would GREATLY appreciate any assistance if somebody actually can
help.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor