[AccessD] Painted into a corner

It sounds like you need a cross-tab result on your report, that looks
something like this:

Status                     ADC  HR  MA
======================================	
CASES W/NO ERRORS FOUND     ##  ##  ##
DENIED/WITHDRAWN            ##  ##  ##
GRANT REDUCTION             ##  ##  ##

Cross-tab queries are fairly easy to implement, but a little tricky to
describe.  I could give you the boring SQL to do it, but it is WAY easier to
use the Query grid to design them.  They consist of 3 elements: row heading,
column heading, and value.  In this case, the status is the row heading, the
ADC/HR/MA specification is the column heading, and the count is the value.

Another (slower) method, would be to use DCount statements on each of the 9
textboxes.  Its ControlSource would look something like:

=DCount("*","tblCases","Status=1 AND Type='ADC'")
=DCount("*","tblCases","Status=1 AND Type='HR'")
=DCount("*","tblCases","Status=1 AND Type='MA'")
=DCount("*","tblCases","Status=2 AND Type='ADC'")
...


-----Original Message-----
From: John Clark [mailto:John.Clark at niagaracounty.com]
Sent: Tuesday, March 11, 2003 12:34 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Painted into a corner


I got fancy w/my latest program, and now cannot seem to complete the
project. That is, I cannot finish the last report. This due to my own
stubbornness and stupidity, but I'm guessing that somebody on the list
can either guide me to an answer, or at least say, "That can't be done
you idiot!" ...thus saving me time looking for a solution.

Many of you offered advice, while I was building my drop-down box...it
was the one that listed a category, that was not able to be chosen, and
"details", under these categories, that were indented slightly. The box,
in its drop down state, looks like this:

CASES W/NO ERRORS FOUND
   ADC
   HR
   MA
DENIED/WITHDRAWN
   ADC
   HR
   MA
GRANT REDUCTION
   ADC
   HR
   MA

I then found out that they needed a report that had totals for each
type, within a time period, and I have gotten close, with Jim DeMarco's
advice. I used a query that makes a subset of the main table limited by
date...this comes from a form with a start and ending date. I then use
another query that uses this query, along w/the table with the info for
the combo box above, to total each category. I get the following for the
time period that I have chosen:

CASES W/NO ERRORS FOUND ADC	1
CASES W/NO ERRORS FOUND HR	4
CASES W/NO ERRORS FOUND MA	2
DENIED/WITHDRAWN HR		1
GRANT REDUCTION ADC		3
GRANT REDUCTION HR		1
GRANT REDUCTION MA		2
 

I also sum up the counts with the following formula behind a text box:
"=Sum([Count])"

This is close, but the are telling me that they need to know:

1) How many of each detail (i.e. "ADC", "HR", "MA") 

2) How many of each category (i.e. "CASES W/NO ERRORS FOUND",
"DENIED/WITHDRAWN", "GRANT REDUCTION")

It looks like a crosstab query, but I don't even know what a crosstab
query is. I have a total mental block, when it comes to these things...I
just don't get them! Is this what I should be looking at? Or, is there
another way to pull these out? For instance, if I pull apart the "CASES
W/NO ERRORS FOUND" from the "ADC" could I count the common occurrences?
I need to end up with:

CASES W/NO ERRORS FOUND	7
DENIED/WITHDRAWN		1
GRANT REDUCTION		6

ADC				4
HR				6
MA				4

in addition to what I currently have.

I am currently investigating doing each of these in its own query, but
then how do I blend them, assuming I am able to figure it out.


A Humongous thanks to whoever can help me out! I am so far behind
schedule, that it isn't funny. The next project in line has a due date
of April 1st (the start of their fiscal year), and that one is much
bigger than my current one. I am already thinking of just giving them a
"shell" so they can input, and then finish the program around it.

John W Clark
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com