Getting rid of blank spaces in a report

[hazbin via AccessMonster.com]

I have a display issue that I am unable to resolve. I have a table setup as
follows:

Text--Num--Item1--Item2--Item3--Item4
T1-------6-------A--------B-------C
T2-------3-------A--------D
T3-------5-------C--------E-------G-------J

(NOTE: the dashes are just to make my message look clean). Each “item†column
uses a combo box. The sub-table for each combo box is the same. I want to
display a list of the items for each record in one column in a report.
However, each record has a variable number of items. This creates a problem
for the report. Right now, I have blank spaces showing (see below). I have
tried to resolve this with queries and report settings but can’t figure it
out. How can I do this?

Text--Num--Items
T1-------6------A
------------------B
------------------C
------------------(blank)
T2-------3------A
------------------D
-----------------(blank)
-----------------(blank)
T3-------5------C
------------------E
------------------G
------------------J

 

[BruceM]

Items should be in a separate, related table. Without knowing anything
about your database it is difficult to be specific, but if you were making a
phone number table for people who may have several phone numbers you might
have something like:

tblPerson
PersonID (primary key)
FirstName
LastName
other personal information

tblPhoneNumber
PhoneID (primary key)
PersonID (foreign key)
PhoneNumber
NumberType (home, office, cell, etc.)

Create a relationship between the two PersonID fields (click Enforce
Referential Integrity). Create a form based on tblPerson with a subform
based on tblPhoneNumber. Similarly, create a report and subreport when it
is time to print the records.

To line up the items one above the other with your current setup you would
need to set the Can Grow property of a text box and of the report section to
Yes, then use something like this for that text box Control Source (or to
make a calculated query field):

=([Item1] + chr(13) + chr(10)) & ([Item] + chr(13) + chr(10)) & ([Item3] +
chr(13) + chr(10))
Using the + sign as the concatenation operator means that if any part of the
expression is null, the whole thing evaluates to null. If Item1 is null,
the carriage return and line feed don't happen because they are part of an
expression involving a null value.

The better choice by far is the form/subform. I'm not sure what you mean by
"sub-table".

Creating an autoform based on tblPerson will give you basic functionality,
but limited choices.

 

[Duane Hookom]

Normalize your table structure or create a union query like:

SELECT [PkField], 1 as ItemNum, Item1 as Item
FROM [table setup]
WHERE Item1 Is Not Null
UNION ALL
SELECT [PkField], 2, Item2
FROM [table setup]
WHERE Item2 Is Not Null
UNION ALL
SELECT [PkField], 3, Item3
FROM [table setup]
WHERE Item3 Is Not Null
UNION ALL
SELECT [PkField], 4, Item4
FROM [table setup]
WHERE Item4 Is Not Null;

You can then create a subreport based on this union query and include it in
the detail of your main report. Set the Link Master/Child to whatever your
primary key from the [table setup]

 

[hazbin via AccessMonster.com]

I wanted to avoid making changes to the table so I used the union query. I
used a test database and it worked fine. Thank you for your help.

Normalize your table structure or create a union query like:

SELECT [PkField], 1 as ItemNum, Item1 as Item
FROM [table setup]
WHERE Item1 Is Not Null
UNION ALL
SELECT [PkField], 2, Item2
FROM [table setup]
WHERE Item2 Is Not Null
UNION ALL
SELECT [PkField], 3, Item3
FROM [table setup]
WHERE Item3 Is Not Null
UNION ALL
SELECT [PkField], 4, Item4
FROM [table setup]
WHERE Item4 Is Not Null;

You can then create a subreport based on this union query and include it in
the detail of your main report. Set the Link Master/Child to whatever your
primary key from the [table setup]

I have a display issue that I am unable to resolve. I have a table setup as
follows:

[quoted text clipped - 27 lines]

------------------G
------------------J

 

[hazbin via AccessMonster.com]

I have run across a fatal problem. The report will not accept a union query
as a subreport. It is an "unsupported option". What would you suggest now? If
I can just somehow get the report and subreport joined into an Excel document.
That would be good enough.

Normalize your table structure or create a union query like:

SELECT [PkField], 1 as ItemNum, Item1 as Item
FROM [table setup]
WHERE Item1 Is Not Null
UNION ALL
SELECT [PkField], 2, Item2
FROM [table setup]
WHERE Item2 Is Not Null
UNION ALL
SELECT [PkField], 3, Item3
FROM [table setup]
WHERE Item3 Is Not Null
UNION ALL
SELECT [PkField], 4, Item4
FROM [table setup]
WHERE Item4 Is Not Null;

You can then create a subreport based on this union query and include it in
the detail of your main report. Set the Link Master/Child to whatever your
primary key from the [table setup]

I have a display issue that I am unable to resolve. I have a table setup as
follows:

[quoted text clipped - 27 lines]

------------------G
------------------J

 

[Duane Hookom]

I haven't seen this issue. I just tested this by creating a union query and
using it as the Record Source for a report. I then add this union query
report as a subreport on another report. I didn't have any problem doing
this.

--
Duane Hookom
MS Access MVP

I have run across a fatal problem. The report will not accept a union query
as a subreport. It is an "unsupported option". What would you suggest now?
If
I can just somehow get the report and subreport joined into an Excel
document.
That would be good enough.

Normalize your table structure or create a union query like:

SELECT [PkField], 1 as ItemNum, Item1 as Item
FROM [table setup]
WHERE Item1 Is Not Null
UNION ALL
SELECT [PkField], 2, Item2
FROM [table setup]
WHERE Item2 Is Not Null
UNION ALL
SELECT [PkField], 3, Item3
FROM [table setup]
WHERE Item3 Is Not Null
UNION ALL
SELECT [PkField], 4, Item4
FROM [table setup]
WHERE Item4 Is Not Null;

You can then create a subreport based on this union query and include it
in
the detail of your main report. Set the Link Master/Child to whatever your
primary key from the [table setup]

I have a display issue that I am unable to resolve. I have a table setup
as
follows:

[quoted text clipped - 27 lines]

------------------G
------------------J