Creating a table from a recordset query then having a report form use the resulting table.

Creating a table from a recordset query then having a report form use the resulting table.

Post by LordHo » Thu, 20 Oct 2005 03:00:01

Hello all,

I am building a report that pulls data from several different
databases on our network. I am using VBA/ADODB.Recordset to build the
queries based upon selections on a Form. I was finally able to build a
query that works and seems to pull in the data in the format I need,
but I am not sure how I get the resulting data to a Report Form. A
typical query might look like

SELECT lifecycle_phase, COUNT(lifecycle_phase) AS defect_cycle
FROM Defect
WHERE repoproject = 33554994 AND lifecycle_phase IN ( 'Planning',
'Requirements', 'Design', 'Coding', 'Integration', 'Verification' )
GROUP BY lifecycle_phase

which the result looks like

lifecycle_phase defect_cycle
Coding 35
Design 28
Integration 19
Planning 29
Requirements 25
Verification 25

So, my next hurdle is passing this data to a Report Form so I can
display it, something like

n |
. |
. | _
. | _ | |
1 | | | | |
Planning Requirements ....

Any help is greatly appreciated.


1. Printing report b after report a prints using report a's variables

2. Counting A's in a table column 1, F's in column 2, etc...


I have a database where you can type in students' responses to
questions on a test.
I was able to create a count on a query that shows me how many
people selected the answer A (the correct answer) to question 1, F to
question 2, etc... I would like to have my query show the responses
for all of the questions that way I don't have to open
up 25 different queries to get a sense of how many students got each
question right.

When I just do a query with a count for question 1, the function works
accordingly telling me I have 10 A's, 2 C's, etc... However, when I
add more columns to show question 2 it
still ultimately shows me how many A's were selected for the first
question, but it does not put all the A's together as the table below
shows. Instead, it has three categories for A, 2 for D, etc... (the
numbers do add up correctly though). I'm not sure why it is doing
that. Any ideas?? I've included the SQL code below the table.

SA1 CountOfA SA2 CountOfF
10 10
A 8 F 8
A 1 H 1
A 1 J 1
C 1 F 1
D 1 F 1
D 2 G 2
E 1 F 1

Here is the SQL code that is producing this table.

SELECT Table1.SA1, Count(Table1.A) AS CountOfA, Table1.[SA2],
Count(Table1.F) AS CountOfF
FROM Table1
GROUP BY Table1.SA1, Table1.A, Table1.SA2, Table1.F;

much appreciative,

3. Why does Form-A's instance fire Form-B's event??

4. Vlookup reporting #N/A's

5. using "if" but omitting the #n/a's

6. equation that will filter out #N/A's

7. "Matching" column A's values in column B

8. Formula's with #N/A's

9. How do I only get valid "answers" in a VLOOKUP function (no #N/A's

10. 'Send As' messages stay in User A's sent folder.

11. Scottie A's Hall of Flame

12. CAnnot type lowercAse 'A's Anymore (solved!)

13. more a's than b's

14. how do u invoke Tag b's Tag Handler from within Tag a's tag Handler?

15. Capture image of App#A's window from App#B