I have a table with a "Status" column (lookup field to the status
table) and an "EscalationID" column (which may or may not be filled
in). I want a report that indicates complete or not for 2 categories:
"Escalation" and "Other".
Row headers should be "Escalation" (EscalationID = not null) and
"Other" (EscalationID = null).
Column headers should be "Complete" (Status = "Complete"),
"Incomplete" (Status <> "Complete") and "Total" (count all).
It's a crosstab query, but I don't know how to make a crosstab sort by
"null or not" and "complete or not" instead of just values.
Any help will be greatly appreciated. Thanks!
Keith 7 1350
In the Field row of the query, enter:
(EscalationID Is Null)
in one column, and in the next:
(Status <> "Complete")
This will generate a pair of fields containing true or false.
You should be able to generate the crosstab from there.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Keith Work" <wk****@movieland.com> wrote in message
news:4a**************************@posting.google.c om... I have a table with a "Status" column (lookup field to the status table) and an "EscalationID" column (which may or may not be filled in). I want a report that indicates complete or not for 2 categories: "Escalation" and "Other".
Row headers should be "Escalation" (EscalationID = not null) and "Other" (EscalationID = null).
Column headers should be "Complete" (Status = "Complete"), "Incomplete" (Status <> "Complete") and "Total" (count all).
It's a crosstab query, but I don't know how to make a crosstab sort by "null or not" and "complete or not" instead of just values.
Any help will be greatly appreciated. Thanks!
Keith
"Keith Work" <wk****@movieland.com> wrote in message
news:4a**************************@posting.google.c om... I have a table with a "Status" column (lookup field to the status table) and an "EscalationID" column (which may or may not be filled in). I want a report that indicates complete or not for 2 categories: "Escalation" and "Other".
Row headers should be "Escalation" (EscalationID = not null) and "Other" (EscalationID = null).
Column headers should be "Complete" (Status = "Complete"), "Incomplete" (Status <> "Complete") and "Total" (count all).
It's a crosstab query, but I don't know how to make a crosstab sort by "null or not" and "complete or not" instead of just values.
you could do it with subqueries, but it will probably be slow
select nz(k.EscalationID, "Other") AS escalation,
(
select count(*) from keith k2
where k2.Status = "Complete"
and k2.EscalationID = k.EscalationID
) as complete,
(
select count(*) from keith k2
where k2.Status <>"Complete"
and k2.EscalationID = k.EscalationID
) as incomplete,
(
select count(*) from keith k2
where k2.EscalationID = k.EscalationID
) as total
from keith as k
group by k.EscalationID
A better might be to use a derived table to calculate the complete status of
each row as an integer, 1 or zero, and then sum them up.
select A.escalation, sum(A.complete) as complete,
count(*) - sum(A.complete) as incomplete,
count(*) AS total
from
(
select nz(EscalationID,"Other") as escalation,
IIf(Status="Complete",1,0) as complete
from keith
) AS A
group by A.escalation
These aren't tested. If you need any more help please post some sample data
with the output you require.
Haven't had a chance to try it yet (I will tomorrow), but here's the data
and results I need. I'm converting some job-aids from various sources into a
common tool and need to provide reporting to the managers through the whole
process:
Sample data:
RecordNumber Description Status EscalationID
123 Job aid to do A... Pending 321
234 Job aid to do B... Rewrite 432
345 Job aid to do C... Complete
456 Job aid to do D... Complete 543
567 Job aid to do E... Deleted
I need returned a table that reads thusly:
Complete Incomplete Total
Escalation 1 2 3
Other 1 1 2
Total 2 3 5
Hopefully the formatting doesn't get hosed too badly. Thanks. :)
Keith
"John Winterbottom" <as******@hotmail.com> wrote in message
news:2g************@uni-berlin.de... "Keith Work" <wk****@movieland.com> wrote in message news:4a**************************@posting.google.c om... I have a table with a "Status" column (lookup field to the status table) and an "EscalationID" column (which may or may not be filled in). I want a report that indicates complete or not for 2 categories: "Escalation" and "Other".
Row headers should be "Escalation" (EscalationID = not null) and "Other" (EscalationID = null).
Column headers should be "Complete" (Status = "Complete"), "Incomplete" (Status <> "Complete") and "Total" (count all).
It's a crosstab query, but I don't know how to make a crosstab sort by "null or not" and "complete or not" instead of just values.
you could do it with subqueries, but it will probably be slow
select nz(k.EscalationID, "Other") AS escalation, ( select count(*) from keith k2 where k2.Status = "Complete" and k2.EscalationID = k.EscalationID ) as complete, ( select count(*) from keith k2 where k2.Status <>"Complete" and k2.EscalationID = k.EscalationID ) as incomplete, ( select count(*) from keith k2 where k2.EscalationID = k.EscalationID ) as total from keith as k group by k.EscalationID
A better might be to use a derived table to calculate the complete status
of each row as an integer, 1 or zero, and then sum them up.
select A.escalation, sum(A.complete) as complete, count(*) - sum(A.complete) as incomplete, count(*) AS total from ( select nz(EscalationID,"Other") as escalation, IIf(Status="Complete",1,0) as complete from keith ) AS A group by A.escalation These aren't tested. If you need any more help please post some sample
data with the output you require.
"Keith Work" <no@spam> wrote in message
news:L7********************@comcast.com... Haven't had a chance to try it yet (I will tomorrow), but here's the data and results I need. I'm converting some job-aids from various sources into
a common tool and need to provide reporting to the managers through the
whole process:
Sample data:
RecordNumber Description Status EscalationID 123 Job aid to do A... Pending 321 234 Job aid to do B... Rewrite 432 345 Job aid to do C... Complete 456 Job aid to do D... Complete 543 567 Job aid to do E... Deleted
I need returned a table that reads thusly:
Complete Incomplete Total Escalation 1 2 3 Other 1 1 2 Total 2 3 5
this looks ugly but it should work and performance shouldn't be too bad:
select escalation, Incomplete, Complete, Total
from
(
select escalation, sum(c) as complete,
count(*) - sum(c) as Incomplete,
count(*) as total, 0 as sort
from
(
select IIf(EscalationID is not null,"Escalation", "Other")
as escalation,
IIf(Status="Complete",1,0) as c
from keith
) as A
group by escalation
union all
select "Total", sum(c), count(*)-sum(c), count(*), 1
from
(
select IIf(Status="Complete",1,0) as c
from keith
) as A
) as B
order by sort
Thanks John. I plugged it into my database and replaced "keith" with
the table name but I get a "syntax error in FROM clause". Access also
keeps replacing the first and last parenthesis in the whole statement
with brackets []. I can save it with the brackets, but it asks me what
"escalation" is when I run it.
Sorry I can't help more. I can make sense out of a statement written
but just don't know SQL well enough to troubleshoot it myself. Thanks
again.
Keith
"John Winterbottom" <as******@hotmail.com> wrote in message news:<2g************@uni-berlin.de>... this looks ugly but it should work and performance shouldn't be too bad:
select escalation, Incomplete, Complete, Total from ( select escalation, sum(c) as complete, count(*) - sum(c) as Incomplete, count(*) as total, 0 as sort from ( select IIf(EscalationID is not null,"Escalation", "Other") as escalation, IIf(Status="Complete",1,0) as c from keith ) as A group by escalation union all select "Total", sum(c), count(*)-sum(c), count(*), 1 from ( select IIf(Status="Complete",1,0) as c from keith ) as A ) as B order by sort
"Keith Work" <wk****@movieland.com> wrote in message
news:4a*************************@posting.google.co m... Thanks John. I plugged it into my database and replaced "keith" with the table name but I get a "syntax error in FROM clause". Access also keeps replacing the first and last parenthesis in the whole statement with brackets []. I can save it with the brackets, but it asks me what "escalation" is when I run it.
Sorry I can't help more. I can make sense out of a statement written but just don't know SQL well enough to troubleshoot it myself. Thanks again.
What version of Access are you running? I tested this with Access 2003 but
it should work all the way back to Access 2000. If you're still using 97
then we need to modify it slightly - let me know.
Found the problem. I had one of our in-house guys take a look and he
found the problem. We changed EscalationsID to [Escalations ID] (the
correct field name - doh!) and that did it.
Excellent code there. Thanks a LOT!
Keith This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Paul Bramscher |
last post by:
Here's one for pathological SQL programmers.
I've got a table of things called elements. They're components, sort of
like amino acids, which come together to form complex web pages -- as
nodes...
|
by: Starbuck |
last post by:
OK, first let me say that I am no DB person.
But I have a user here who keeps getting this error
whenever she does, whatever it is she does, with
databases...
A google search takes me to...
|
by: Mikel |
last post by:
I am trying to get around the problem "The expression you have entered
is too complex" for a select query. (The example below is not the
expression that is giving me headaches.)
So I am thinking...
|
by: Giloosh |
last post by:
hello,
i dont know if what i want to do is really considered complex, but i
sure cannot figure it out.
i need to create a query that shows the payments a person makes every
day for a variable...
|
by: Matt |
last post by:
Hi all, Thank you for taking the time.
I have a database with 45 tables on it. 44 tables are linked to a main
table through a one to one relationship.
My question is, is there no way i can...
|
by: schan |
last post by:
Hi there,
I was wondering if someone could shed some light on a problem I have no
idea on how to fix.
I created an Excel Add-In that uses an ADO connection to an Access
database on a file...
|
by: pippapippa |
last post by:
I should be most grateful for a little advice.
I have used Access 2000 & latterly 2002. Am about to upgrade since it is evident that documentation, tutorials etc are more readily available in...
|
by: DDonnachie |
last post by:
Hi folks,
The following SQL statment works
SELECT StaffHoursTable.EngName, StaffHoursTable.JobNumberShadow, ++++++ AS Norm_Hours, ++++++ AS OT_Hours,...
|
by: BUmed |
last post by:
Ok let me start from the start. I have a form that has question in it and the person chooses 0 1 2 -99 for each. The form then needs to add up the numbers for the sub categories in the form. For...
|
by: crystal2005 |
last post by:
Hi,
I am having trouble with some complex SQL queries. I’ve got winestore database, taken from Web Database Application with PHP and MySQL book. And some question about queries as the following
...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |