[AccessD] Painted into a corner

John Clark John.Clark at niagaracounty.com
Tue Mar 11 12:37:00 CST 2003


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



More information about the AccessD mailing list