473,326 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Complex query question

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
Nov 12 '05 #1
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

Nov 12 '05 #2
"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.




Nov 12 '05 #3

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.



Nov 12 '05 #4
"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




Nov 12 '05 #5
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

Nov 12 '05 #6
"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.
Nov 12 '05 #7
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
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
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...
4
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...
2
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...
1
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...
8
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...
0
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...
6
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...
13
by: DDonnachie | last post by:
Hi folks, The following SQL statment works SELECT StaffHoursTable.EngName, StaffHoursTable.JobNumberShadow, ++++++ AS Norm_Hours, ++++++ AS OT_Hours,...
3
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...
0
crystal2005
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 ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
0
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...
1
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)...
1
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...
1
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....
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.