2 reports using 1 query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a table of questions categorized by 7 subjects. (Math, Physics, English, etc.) More or less I have 1000 questions for each subjects. At one time an exam (for a specific subject) will be generated where 40 questions are selected randomly. Is it possible to generate 2 reports from a single query? I need two reports, one for the examinee and one for the examiner where the answers for each question is only written on the examiner's copy. I am new at access and I only use macros since I don’t know VB

Thanks in advance
 
Lei,

Yes, it's possible, and it's easy too!
To begin with, make sure your query returns all the fields, including the
answers.
Then make the examiner report first.
When you're happy with it, just save it as <another name for examinee
report>, open in design view, delete the answer control(s) and make any
other changes you might want.
You can print them one after another manually, or you can use a macro or
code behind a command button on a form to print them both with a single user
action.

HTH,
Nikos

Lei said:
Hi.

I have a table of questions categorized by 7 subjects. (Math, Physics,
Click to expand...
English, etc.) More or less I have 1000 questions for each subjects. At
one time an exam (for a specific subject) will be generated where 40
questions are selected randomly. Is it possible to generate 2 reports from
a single query? I need two reports, one for the examinee and one for the
examiner where the answers for each question is only written on the
examiner's copy. I am new at access and I only use macros since I don't
know VB.
 
Another approach, rather than deleting the answer controls, might be to
change the Visible property on the second report.

JOPO (just one person's opinion)

Jeff Boyce
<Access MVP>
 
Lei,

"using an append query ..."
"using the append query as my filter..."

Not quite clear on what exactly you've done, but it sounds like you have
taken some extra, unnecessary steps...
Are you appending you query results in a table without deleting all previous
records first, so you then need to re-apply a filter on OpenReport?
The idea is to make a select query on the table and apply your filter there,
then use that query as the recordsource for your reports and just use
OpenReport actions in your macro; no need for extra filters on the reports,
no need for temporary tables to append to or clear up before you re-run.

"I don't know how to do the one you suggested in 1 command"

It sounds like you're already doing this, if you have a macro with two
OpenReport actions! No other action required for running append or delete
queries if you base the reports on a plain select query as suggested.

If you still prefer the intermediate table approach, you could use a
make-table query instead of an append one. This way each time it is run the
new results will overwrite the previous, thus eliminating the need for a
delete query before or a filter on the report. Also note that you can run
your action queries (in the case of the intermediate table) from within the
same macro, using OpenQuery action(s) before the OpenReport actions, so you
still do it all with one move.

HTH,
Nikos

Lei said:
Nikos,

Thanks for the reply. I was able to work out my problem using an append
Click to expand...
query since I don't know how to do the one you suggested in 1 command. All
the questions generated at random are stored in another table (which I will
delete the contents after I printed the 2 reports). I used this table for
my reports. When I run the query, it was able to perform the required
actions (select by random and store the top value). In macro, I used the
OpenReport action using the append query as my filter. The result is a
blank report. Was I wrong to use the append query as filter?
 
Jeff,

Good point. I would use a single report and set/reset the Visible property
programatically at runtime, but didn't suggest so as it seems Lei is not
there yet.

Nikos
 
Nikos

Got your point. Thanks. Now, it's working properly. I have a number field in my Questions table that I should increment everytime the question is selected in a particular exam. It's more like a counter how many times the question (particular record) is used. It's kind of a proof also that random selection is working. How can I insert this on my query?

Lei
 
Hi Nikos

Please ignore my previous reply. I already solved my problem. Thanks for all the help

God bless

Lei
 
Nikos

Thought I did it. When I checked my Counter, only one record was updated. In every exam (w/40 questions), only one question record has a counter that was incremented. How can I update the counter for EACH question selected

thanks

Lei
 
OK! It's just beginning to get interesting! Are you still using the table? I
hope you are... If yes:
Make anew query on the questions table, and get the question ID and counter
fields in the grid. Then in the criteria line below the question ID field,
type in the following expression:

IN (SELECT Question_ID FROM Temporary_Questions_Table)

replacing the table and field names with the actual ones. Run the query just
to verify that it is indeed returning the correct records. When happy with
this go back to design view, and change the query type to Update. Then in
the grid under the counter field put the following expression in the Update
To line:

[Counter] = [Counter] + 1

replacing, again, the field name with the actual one. What this query does
is it increments the counter in each selected record by one every time it is
run. So when you're ahppy with it, add a new action OpenQuery towards the
end of your macro, after the two OpenReport, but before the action that
clears up the temporary table (unless you do the clearing at the beginning,
before running the reports).

HTH,
Nikos

Lei said:
Nikos,

Thought I did it. When I checked my Counter, only one record was updated.
Click to expand...
In every exam (w/40 questions), only one question record has a counter that
was incremented. How can I update the counter for EACH question selected?
 
Back
Top