Need to format report even with NULL value (1 Viewer)

[jepoysaipan]

Hi Guys,

I again need your expertise, I now have a problem formatting the report, below is the table structure:

Case_No Seq_No Auth_Rep
0001 01 John Doe
0001 02 Jane Doe
0001 03 Jhane Doe
0002 01 Jhon Doe
0002 02 Jhonn Doe
0003 01 Jon Doe

As you can see each Case_No can have a maximum Seq_No of up to 3 as Authorize Rep. and a minimum of 1, I tried grouping the data and no luck, got no adequate knowlodge in coding so I am again stuck.

Now here's the report Format I'm dealing with:

Case No: 0001

John Doe
Authorize Representative I

Jane Doe
Authorize Representative II

Jhane Doe
Authorize Representative III

___________________________________
Case No: 0002

Jhon Doe
Authorize Representative I

Jhonn Doe
Authorize Representative II


Authorize Representative III

________________________________
Case No: 0003

Jon Doe
Authorize Representative I


Authorize Representative II


Authorize Representative III
________________________________

I just need to get the record to be grouped into 3, regardless if there is an Authorize Rep. 2 & 3.

Thanks in advance.

Jeff

 

[gemma-the-husky]

normally in a relational database, you would not have non-existent entries (be that 1s, 2s or 3s), so I dont think any normal query will produce them So in order to invent null rows for that purpose, i think you need to address how the report's query is formed (by the same token, the dbs will automatically deal with level 4s, 5s etc - although i presume other things in your code control that possibilitiy)

To do what oyu want, i would consider saving the query results into a temporary table, and basing the report on that table. Once you have the temp table, you will have to process it (probably manually, by iterating the table) to add in the extra missing null values you need.

 

[khawar]

Download the attached sample and open report "SampleReport"

 

[jepoysaipan]

Hi Khawar & Gemma,

WOW! I owe you big time!

Exactly what I had dreamed of, works like magic!

Thanks a lot guys!

Really love this forum!

BTW, one more thing Khawar & Gemma can you help me on my post "Crosstab Like Query Data Entry"? Iv'e got it up & running but my problem on my query is that when I extract all the info. I need to get the their average on an unbound label so everytime the query runs it will just compute the average of the report.

Cheers!!!

Jeff