[AccessD] Painted into a corner

John,

This can be done using a "Union" query. The whole thing could possibly be done
in one query I just need to know the table structure.

Scott Marcus

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


I have actually created...easier than I thought...two more queries. Each
of these does one of the sections. My original query giving the first
group of numbers, I already had. Now I have created two more
queries...each doing one of the latter two sections. Now, is there a way
to bring them together?

I am going to try sub-reports, but I am guessing that this won't do
it.

>>> John.Clark at niagaracounty.com 03/11/03 01:34PM >>>
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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com