Stuart McLachlan
stuart at lexacorp.com.pg
Tue Mar 11 17:10:00 CST 2003
You don't need a crosstab. Create a query with a series of fields like: NoErrors:Sum(IIf(Instr(Description,"CASES",1,0))) ADC: Sum(Iif(Instr(Description,"ADR",1,0))) etc > 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 -- Lexacorp Ltd http://www.lexacorp.com.pg Information Technology Consultancy, Software Development,System Support.