Business Solutions:  Multiple Queries in Access 2003 Reports

        by James D. Duncan, CPA   April 2005

           

This month is a continuation of last month’s project.  It is also a work in progress and I am sure there is a much smarter way to do this.  However, I will trudge through this until I see the light and start over.  When you are up against deadlines you do not always have time to do it the best or the smartest way.

            The definition of this project is we have a 15-page questionnaire of various areas of demographics.  We have a master file describing the clients.  This has various fields, date enter into program, name, income, number in the family, and so on.  The first question is only concerned with the numbers on the first day, number entering the program, number who left and the numbers on the last day of the program.

 

 

            Open a report in design view and enter the first question.  Cut and paste that 3 more times.  Then put the headings in and the footer.  We can then go back and name the text boxes appropriately.  Under the heading of question 2 we have the questions.  The first text box to the right of question “a” is “txtAnswer2a1” and the next box to the right is “txtAnswer2a2”. The first text box for question “b” is txtAnswer2b1 and so on to name all the text boxes.

            Last year we made 20 or 30 queries and wrote the separate answers to the questionnaire.  This year I am striving to get more automated.  I went to the query to the first question and opened it in design view.  Selected the SQL view and cut all the code there. Then pasted this into the detail format code section of the report. The SQL from the query is pasted into the variable sSQL with the semicolon left off.  Here is the code.

 

 

 

What I am trying to demonstrate here is that you can use multiple queries in the report to pass the SQL variables into the text fields on the report.  You notice that in the first record set (rs variable) I load txtAnswer2a1 from CountOfLocation.  Then I run another SQL and load the two field’s txtAnswer2b2 and txtAnswer2b3.

            When the report runs, it only fills in the 3 fields I have computed.  I have a lot more work to do.  This gives you the basics on how you can use the SQL from the queries to fill in fields on reports.

 

 

What I am trying to demonstrate here is that you can use multiple queries in the report to pass the SQL variables into the text fields on the report.  You notice that in the first record set (rs variable) I load txtAnswer2a1 from CountOfLocation.  Then I run another SQL and load the two field’s txtAnswer2b2 and txtAnswer2b3.

            When the report runs, it only fills in the 3 fields I have computed.  I have a lot more work to do.  This gives you the basics on how you can use the SQL from the queries to fill in fields on reports.

 

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James.