Solved

Report Format question - Grow, Shrink

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now