Report Format question - Grow, Shrink

Access2000
I have a table (which was poorly designed by someone else)

InvNo C1 D1 A1 C2 D2 A2 ... C9 D9 A9
where:
Cx - Charge code
Dx - Description
Ax - Amount
The concept is an Invoice with up to 9 lines.

I need to display a each record on an Invoice report like:

InvNo Code  Desc  Amount
----- ----  ----  ------
12345 C1    D1    A1
      C2    D2    A2
      C3    D3    A3
------------------------------
23456 C1    D1    A1
------------------------------
34567 C1    D1    A1
      C2    D2    A2
------------------------------

Notice that some invoices only have 1 "Line" while others have upto 9 "lines".

The problem is that There may or may not be any values in lines 2 - 9 on any given invoice, so If I put all the fields in the report, Most Invoice records will have a lot of white space between then for no reason.

I want each Invoice record [Detail] record to grow or shrink depending on how many "Lines" are on each.

I set the Detail section property to let is Grow or Shrink as needed, but I still have the white space.

Also a given line may have data in the Dx field but nothing in Cx or Ax.
If Cx, Dx, and Ax are ALL null or 0, I want to shrink up that Detail line so that only "rows" that have data in all three fields have data to show.

Any ideas? Unfortunately redesigning the table is not an option.

Thanks,
Chuck
appelqAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
VolibrawlConnect With a Mentor Commented:
Just 'storming ... you may not even be able to do this..and if order of the lines is imnportant, it may not be feasible:

Union query like:

Select INVNO, C1 AS Chg,D1 as Desc,A1 as Amnt from Table
UNION
Select INVNO, C2 As chg, D1 as Desc, A1 as Amnt from Table
Union, etc.

Then base your report on a select query:

Select * where Chg>0 AND Desc is not null AND Amnt>0 from table


You could group the report on invno, giving total for each group (invno).

0
 
nico5038Commented:
You can use a serie of queries unioned to get your data "normalized".
Like:

Select InvNo, C1, D1, A1 from tblYours
UNION ALL
Select InvNo, C2, D2, A2 from tblYours
UNION ALL
Select InvNo, C3, D3, A3 from tblYours
etc..

This query can be used to filterout all empty C's/D's/A's and will be a perfect detailrow for your report...

Idea ?

Nic;o)
0
 
wdparrCommented:
You need to set the Hide Duplicates property to Yes for the Invno and for all of the fields in the detail section set the Can Grow and Can Shrink properties to Yes.  You will also want to group the data by invno as volibrawl suggested.

To eliminate the rows you don't want the Union queries suggested should work

Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.