Solved

Report Format question - Grow, Shrink

Posted on 2002-03-26
3
331 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 100 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

726 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