?
Solved

Report Format question - Grow, Shrink

Posted on 2002-03-26
3
Medium Priority
?
351 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:appelq
3 Comments
 
LVL 9

Accepted Solution

by:
Volibrawl earned 400 total points
ID: 6897567
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
 
LVL 54

Expert Comment

by:nico5038
ID: 6897574
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
 
LVL 1

Expert Comment

by:wdparr
ID: 6897673
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question