Reworking Reports to Enter and Display Many-to-Many Linked Data


After normalizing the database’s tables, and reworking the forms, the reports also need some work. The original reports were based on a query (qryEBooks) that included the author name fields in the old, non-normalized tblEBooks. I replaced this query with a new query, qryEBooksAndAuthors, that links the three tables in the many-to-many relationship, so that I would have the AuthorID available for linking purposes. This query is shown in Design view in Figure 9.37.

click to expand
Figure 9.37

Important

While a query combining several linked tables is rarely useful as a record source (because it is not editable), such queries are very useful as report record sources because reports display data, but don’t permit editing.

qryEBooksAndAuthors has several calculated fields, which manipulate data from several fields in tblEBooks to display it in a more attractive format on the reports. The SeriesInfo field first checks whether the book is part of a series (indicated by a True value in the Series field), and if it is, it next checks whether a number has been entered into the SeriesNumber field. If a number has been entered, the text “Number “ is concatenated with the series number, the linking text “ of the “ and the series name (with the word “The” added only if the series name itself does not start with “The”).

 SeriesInfo: IIf([Series]=True,IIf([SeriesNumber]>0,"Number " & [SeriesNumber]  & " of the " & IIf(Left([SeriesName],4)="The",Mid([SeriesName],5),[SeriesName])  & " series","The " & IIf(Left([SeriesName],4)="The",Mid([SeriesName],5), [SeriesName]) & " series")) 

The expression yields series information for books such as the following:

  • Number 9 of the Man-Kzin Wars series (series number entered)

  • The Company series (no series number entered)

The ReadStat field translates the numeric ReadStatus value into a text phrase that is used as a group value for sorting some of the reports:

 ReadStat: Switch([ReadStatus]=2,"Books Being Read",[ReadStatus]=1, "Unread Books",[ReadStatus]=3,"Read Books") 

The UniqueAuthorName field (used in reports for accurate grouping of author names) simply concatenates the author name (last name first) and the Author ID, with a dash in between:

 UniqueAuthorName: [LastNameFirst] & " - " & [tblEBookAuthors].[AuthorID] 

In the reports themselves, I removed the txtAuthors control and replaced it with a subreport to display authors data as needed. In the case of a single-author book (the standard case), this subform is invisible; if there are two or more authors, the subreport becomes visible and the coauthor(s) are displayed.

The subreport (rsubCoauthors) has as its record source a select query that shows all the authors for a given book except the author for the current record. To determine which books have multiple authors, I made a totals query (qryMultipleAuthorEBooks) based on qryEBooksAndAuthors, with a count on the AuthorID field, and a criterion selecting only records with a value greater than one in this field. This query is shown in Design view in Figure 9.38.

click to expand
Figure 9.38

This query doesn’t (and can’t) contain the author name, because then it couldn’t count the authors. Thus I needed to link it to the main report query, qryEBooksAndAuthors, in order to get the coauthor names to display on the subreport. qryEBookCoauthors, with the two linked queries, is shown in Design view in Figure 9.39.

click to expand
Figure 9.39

The same query is shown in Datasheet view in Figure 9.40.

click to expand
Figure 9.40

The subreport needs to display just the coauthor names. That means that for each report record, if the book has multiple authors, the subreport should display only the author(s) that don’t match the main author name for that record. (The main author name is just the one that comes first in alphabetical order.) Thus, for BookID 2 (Windhaven), on the record that shows it as a book by author George R. R. Martin, Lisa Tuttle should be displayed as the coauthor, and on the record that shows it as a book by Lisa Tuttle, George R. R. Martin should display as the coauthor.

To accomplish this, I created yet another query (qryCoauthors), linking qryEBookCoauthors with qryEBooksAndAuthors on BookID, to limit the query results to just books with multiple authors. I put a criterion of:

 <>[qryEBooksAndAuthors].[AuthorID] 

on the AuthorID field from qryEBookCoauthors, which means that the matching records in the two source queries will have the same BookID, but different AuthorIDs. I aliased the LastNameFirst field in qryEBooksAndAuthors as MainAuthor and the same field in qryEBookCoauthors as Coauthor, and the AuthorID fields in the two tables as MainAuthorID and CoauthorID. This query (qryCoauthors) is shown in Design view in Figure 9.41 and in Datasheet view in Figure 9.42.

click to expand
Figure 9.41

click to expand
Figure 9.42

Note that for a book with three authors (such as BookID 8, Artifact), there are six records, representing all the possible combinations of three authors (two pairs for each author, listing one of the authors with each of the other two coauthors). For the more common two-author book, there are two records, one with the first author as the main author and the other author as the coauthor, and one with the reverse.

With qryCoauthors as the record source, displaying only the Coauthor field, the subreport rsubCoathors is linked to the main report by BookID to Book and AuthorID to MainAuthorID, and it displays the coauthors for the book, for multiple-authors books only. Because there may be multiple records when only coauthor names are displayed, the txtCoauthor textbox’s HideDuplicates property is set to Yes.

In the original reports, the Authors textbox (displaying the author names from the original, non-normalized table) was in the LastNameFirst group header, but in the new version of the report, the subCoauthors subreport is placed in the Detail section, because an author might write some books alone and others with a coauthor. There is code on the Format event of the report’s Detail section that makes the subreport visible only if the book has multiple authors (using the previously created qryMultipleAuthorEBooks), and also makes the series information visible only for series books, depending on the value of the Series field. This event procedure is:

 Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer) On Error GoTo ErrorHandler    Dim lngBookID As Long        lngBookID = Me![BookID]    If Me![Series] = True Then       Me![txtSeries].Visible = True    Else       Me![txtSeries].Visible = False    End If        If Nz(DCount("*", "qryMultipleAuthorEBooks", "BookID = "        & lngBookID)) > 0 Then       Me![subCoauthors].Visible = True    Else       Me![subCoauthors].Visible = False    End If     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

The rptEBooksByAuthor report is shown in Design view in Figure 9.43 and in Print Preview in Figure 9.44.

click to expand
Figure 9.43

click to expand
Figure 9.44

The report controls with a yellow background (txtAuthorID and txtSeries) are invisible—I use the convention of a bright yellow background for controls on forms and reports that are intended to be invisible on the finished form or report, but may be set visible temporarily for debugging purposes. The yellow color reminds me to set these controls to invisible before finishing the application.

Two authors can have the same name (for example, Elizabeth George the mystery writer and Elizabeth George the Christian writer), so I use the UniqueAuthorName concatenated field (created in qryEBooksAndAuthors) for the first group in the Sorting and Grouping dialog, to guarantee that each author will have a distinct group in the report, but I only display the author name on the report. I didn’t need to treat book names similarly—although two books can have the same name (for example, Partner in Crime by J.A. Jance and Partner in Crime by Rajnar Vajra), a book title would be unique for a specific author.

There are several other reports in this database. They differ from rptEBooksByAuthor only in sorting, filtering, and arrangement of the book data, so I will not discuss them specifically here.

After normalizing the database, applying a naming convention, and modifying the existing queries, forms, and reports as needed, you are now ready to proceed with further changes, on a sound foundation of normalized tables, and forms and reports that display the data in the tables.




Expert One-on-One(c) Microsoft Access Application Development
Expert One-on-One Microsoft Access Application Development
ISBN: 0764559044
EAN: 2147483647
Year: 2006
Pages: 124
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net