Unanswered: Displaying same datafield twice on report
I need to display a same datafield twice on my report with different conditions. When i drag and drop the same field twice on the report. I want a way to define different conditions for both these field.
I am using MS Access and Crystal reports 8.5 version. the front end of my project is Visual Basic 6
eg: I have three tables: BookMaster (Table 1) with bookId as primary key and bookName as additional field, AuthorMaster (Table 2) with authorId as Primary Key and authorName as additional field; bookAuthor (Table 3) which consists of bookId and authorId as foreign keys and authorType as text field (the field authorType is given because i need to show who is the primary author and who is secondary author). The bookAuthor table has two authorId for the same bookId. One primary author and other secondary author. (Real life scenario where same book have multiple author).
I need to display the names of primary and secondary authors for all the books in BookAuthor table. Like: book id, bookName, primaryauthorid, primaryauthorname, secondaryauthorid, secondaryauthorName row by row. Each row displays one books
I did the following steps: I created the relationships in visual table linking and then I created a group with bookId from bookAuthor and then draged the datafield authorId, authorName twice for primary and secondary author. But the report displays the same author name for the book.
String is not a concern for me. I can easily change it to integer.My concern is to show book and all its author in one line. Now that may be on the group header line or single line in Detail section.
When I add one authorName field in detail section, I do get the list of all authors one beneth other but that is not what is required.
I want to create a tabular structure which display a list of all bookNames and their primary, secondary authors.
I also tried creating two formula field and place them in header section.
if authortype = "primary" then authorname else ""
if authortype = "secondary" then authorname else ""
But the report displays only primary authorName and keeps the secondary blank. what i concluded was, may be in groupHeader section, the value for authorType is selected once (i.e. primary) and then both the formulae are evaluated using same value for authorType i.e. "primary". Hence the other formula returns blank.
Some one suggested to use formulas that reference the next record (i.e. the Next() function) but it just gives the immediate next and not all authors (say a tertiary one)
If you want them all on one line, either do a calculated field with both authors concatonated in it or use the underlay following section feature of the section expert and pull you authors in seperate groups.
BookMaster (Table 1) linked to bookAuthor (Table 3) by bookid and then 2 copies of the same table AuthorMaster (Table 2) linked to the bookauthor (Table 3) table by authorid. The first copy is to show the primary author and the selection criteria and/or formulae specify this. Assuming some books may not have 2 authors, the 2nd copy of AuthorMaster (Table 2) will use a left join and the selection criteria and/or formulae will select it only for the secondary author (NB you may need to use an IsNull() statement).
This is clear in my head, but tough to write out, so I hope you can see where I am coming from here.
I agree, however I had assumed that the tables were a given and that tinkering with the database design is not an option.
I use the 2 copies of one table trick when printing out a Bill of Materials (for instance) where the tables are BillOfMaterials and PartsMaster, with 2 copies of the PartsMaster, one to describe the parent part and one to describe the child part(s) (and this is again a scenario where I have no control over the database design).