Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Report Format question - Grow, Shrink

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

840 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