multiple one-many reporting...

B

Brad Pears

I have a report that includes two different 1-M child tables - hence varying
amounts of data per detail line...

The SQL that this report is based on, is a dynamically created statement
based on a criteria screen. Because of this, if there happen to be one or
more entries in one or both of the 1-M tables I am selecting values from,
you get multiple rows back in your query - for the same client but with
differnt 1-M values (get my drift?)

I do not want duplicate values printing so I have some code in the detail
print line of the report that looks at "previous" values and if they are the
same, I set that particular controls visible property false so that the same
"name" perhaps does not get listed multiple times. The only item that is
printed on the 2nd, 3rd or 4th line (whateveer) would be the various values
related to the primary record.

To attempt to save space the report was designed with the 1-M values on the
same line as the rest of the detail line - as opposed to under it in the
following fashion... (this is why I did not use the "grouping"
functionality...)

ID Name Field1 Repeating Field1
Repeating Field2
xxxx xxxxxxxxxxxx xxxxxxx xxxxxxxxxxxxxx xxxxxxxxxxxxxxx
xxxxxxxxxxxxxx
xxxxxxxxxxxxxx
xxxxxxxxxxxxxx
As opposed to this (which is what you would get using group by etc...)

ID Name Field1
xxxx xxxxxxxxxxxxxx xxxxxxxxxxxxxxx

Repeating field1 Repeating Field 2
xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxx


The problem is that if for instance repeating line 1 has 2 related rows in
it and repeating line 2 also has 2 related rows in it, Repeating line 1 will
display it's values 4 times (twice for each of the values) and repeating
line 2 prints it's first field value on the top line and then again on the
3rd line as follows...

ID Name Field1 Repeating Field1
Repeating Field2
0001 xxxxxxxxxxxx xxxxxxx This is a test Value
1
This is another
test
This is a test
Value 2
This is another
test

To kind of overcome this I put some code in when the detail line prints on
print that will not display "Repeating Field 1" if the value in it has
already been printed for that detail line - so then I get the following....
(I've show how it looks with 2 records in there...

ID Name Field1 Repeating Field1
Repeating Field2
0001 xxxxxxxxxxxx xxxxxxx This is a test Value 1
This is another
test

Value 2
0002 xxxxxxxxxxxx xxxxxxx This is a test Value 1
This is another
test

Value 2


As you can see above (well I hope you can see anyway... ) the second
"Repeating Field2 value field still prints on line of it's own. - a 3rd
line.

Question.... How would one go about attempting to get Repeating Field 2 to
print on the first 2 lines - as the repeating field 1 is doing??? Like
this...


ID Name Field1 Repeating Field1
Repeating Field2
0001 xxxxxxxxxxxx xxxxxxx This is a test Value 1
This is another
test Value 2

0002 xxxxxxxxxxxx xxxxxxx This is a test Value 1
This is another
test Value2

Value 2


Any help would be much appreciated! In general it woul dbe nice to have any
repeating fields to always print one after the other - not skip rows. I
guess I could loop through the intital SQL sstatment before anything prints
and generate my own table from which to base the report and load the 1-M
values into a listbox perhaps??


Any help in this confusing mess would be greatly appreciated...

Brad
 
B

Brad Pears

Ya, I am beginning to think I should have just started out this way and not
had the hassle... Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top