Hide a row based on one textbox

[Tandy]

Hi! Right now I have a report that looks as following

Detail (Header/Footer Thing)
---------------------------------
Employee Employee Name Employee DOB NEWMG Employee/Provider Premium
Spouse Spouse Name Spouse DOB NEWMG Spouse Premium
First Child First Child Name First Child DOB NEWMG First Child Premium
Second Child Second Child Name ...
Third Child ...
Fourth Child ...
Fifth Child ...

Employee, Spouse, First Child, Second Child, etc. are labels, rest are all
textboxes. The problem is an employee could only have a spouse and one child,
but the labels and textboxes for "Third Child" on will still be there
creating a gap in between employees. I would like to group the rows (such as
Third Child label and Third Child Name, Third Child DOB and Third Child
Premium textboxes) and make them not show up on report if the NEWMG Premium
is blank or a 0, thus not creating a gap. Please help!

 

[Duane Hookom]

Consider normalizing your table by removing the children and placing them in
a related table with one record per child. You could then use a subreport or
other solution that would involve fancy work-arounds for what should be
simple.

 

[Tandy]

Duane,

The way my tables and queries are set up to calculate the NEWMG
Premiums I can't change the tables. Thank you for your help though!

Tandy

 

[Duane Hookom]

You could use a union query to normalize your wide table(s). This would
create a normalized view that can more easily be reported.

 

[Tandy]

Duane,

I do not know how to make a union query to normalize my wide tables.
Honestly, I do not even know what a union query is. Is what I am asking not a
possibility? I do not mean to be rude at all, this report is just one of the
final pieces to a very frustrating database. I'm sure you can understand why
I am hesitate to start going back and trying to change everything around.
However, if making a union query is what I have to end up doing I would
greatly appreciate your help!

Tandy

 

[Duane Hookom]

Assuming a table like:
tblFamily
==============
FamID
Mom
Dad
Child1
Child2
Child3
Child4
Child5

You would create a query like:
SELECT FamID, Child1 as Child
FROM tblFamily
WHERE Child1 is not Null
UNION ALL

SELECT FamID, Child2
FROM tblFamily
WHERE Child2 is not Null
UNION ALL

SELECT FamID, Child3
FROM tblFamily
WHERE Child3 is not Null
UNION ALL

SELECT FamID, Child4
FROM tblFamily
WHERE Child4 is not Null
UNION ALL

SELECT FamID, Child5
FROM tblFamily
WHERE Child5 is not Null;

You can then create a "Child" subreport based on this union query. A
subreport can be set to grow or shrink depending on the number of related
records in the family.

 

[PC Datasheet]

Tandy,

Open your query in design view. Click on the view button at the far left of
the toolbar at the top of the screen. Choose SQL. You'll see the SQL for
your query.

A Union query combines two or more single queries. You can't create it in
the query design grid; you have to create it in the SQL view by typing it
in, A union query looks like:
Select ............
From Table1
Union
Select ............
From Table2: