Microsoft Office Tutorials and References
In Depth Information
Before you start creating the custom report, you need to create a query that’ll serve as
the record source for the report.
Creating a Query for a Custom Report
The data for a report or form can come from a single table, from a single query based on one
or more tables, or from multiple tables and/or queries. Sarah’s report will contain data from
the tblInvoice, tblContract, and tblCustomer tables, and from the qryCustomersByName
query. You’ll use the Simple Query Wizard to create a query to retrieve all the data required
for the custom report and to serve as the report’s record source.
Tip
For reports and forms that
use data from multiple
tables and/or queries, you
should create a query to
serve as the record source.
If the report or form
requirements change, you
can easily add fields,
including calculated fields,
to the query.
To create the query using the Simple Query Wizard:
1. If you took a break after the previous session, make sure that the Panorama data-
base is open and the Navigation Pane is closed.
2. Click the Create tab on the Ribbon, in the Other group on the Create tab, click the
Query Wizard button, make sure Simple Query Wizard is selected, and then click
the OK button. The first Simple Query Wizard dialog box opens.
You need to select fields from the tblInvoice, tblContract, and tblCustomer tables,
and from the qryCustomersByName query, in that order.
3. Make sure Table: tblInvoice is selected in the Tables/Queries box, and then move
the InvoiceItem , InvoiceDate , InvoiceAmt , and InvoicePaid fields, in that order,
to the Selected Fields list box.
4. Select Table: tblContract in the Tables/Queries box, and then move the
ContractType field to the Selected Fields list box.
5. Select Table: tblCustomer in the Tables/Queries box, and then move the City field
to the Selected Fields list box.
6. Select Query: qryCustomersByName in the Tables/Queries box, move the
Customer calculated field to the Selected Fields list box, and then click the
Next button.
7. Make sure the Detail (shows every field of every record) option button is
selected, and then click the Next button to open the final Simple Query Wizard
dialog box.
After entering the query name and creating the query, you’ll need to set the sort
fields for the query.
8. Change the query name to qryInvoicesByItem , click the Modify the query
design option button, and then click the Finish button.
The InvoiceItem field will be a grouping field, which means it’s the primary sort
field, and the InvoiceDate field is the secondary sort field.
9. Set the InvoiceItem Sort text box to Ascending , set the InvoiceDate Sort text box
to Ascending , save your query changes, and then click below the field lists and
above the design grid to deselect all values. The completed query contains seven
fields from three tables and one query and has two sort fields, the InvoiceItem pri-
mary sort field and the InvoiceDate secondary sort field. See Figure 7-18.
Search JabSto ::




Custom Search