Need to print blank records and or fields

  • Thread starter Thread starter Perplexed
  • Start date Start date
P

Perplexed

My problem is apparently the opposite. I'm using Access 2003 on WinXP.
I have a database which is tracking cancelled appointments. I have three
(3) tables:
the first table has the client's name and address information; the second
table tracks the date, time and reason for cancellation. These two tables
are linked one-to-many. The third table tracks: first warning, letter sent,
suspension date and is linked to the first table one-to-one. My report
prints out all cancellations per client based on a query. I did not want to
have the third table fields printed out for each cancellation in the database
so I made a query on that table and a subreport on the form. This way I get
one occurence of the suspension data for all cancellations from the second
table. My problem is if there is no data in the third data linked to the
client, the subreport does not print. I want it to print the control
headings and a blank field so that when the supervisor gets the report and
contacts the client, he can enter the corresponding dates in the printed
blank fields for entry into the database later.
Hope this isn't too confusing and thanks in advance for your help! This
support group is the greatest!
 
Hi,

Try this (untested): Create a query that is based off of the main
client table and the third table, using an outer join. Then use that as the
source of the subreport instead of the third table.

select [tblTheThird].*
from [tblClientInformation] outer join [tblTheThird] on
[tblTheThird].[ClientID] = [tblClientInformation].[ClientID]

This should provide a third-table "row" for each client no matter
whether there is one or not. When there is not one, it will consist entirely
of null fields.

Clifford Bass
 
Hi,

You may need to include the client ID from the client information table
so that the subreport can link on that:

select [tblClientInformation].[ClientID], [tblTheThird].*
from [tblClientInformation] outer join [tblTheThird] on
[tblTheThird].[ClientID] = [tblClientInformation].[ClientID]

Clifford Bass
 
Clifford Bass said:
Hi,

You may need to include the client ID from the client information table
so that the subreport can link on that:

select [tblClientInformation].[ClientID], [tblTheThird].*
from [tblClientInformation] outer join [tblTheThird] on
[tblTheThird].[ClientID] = [tblClientInformation].[ClientID]

Clifford Bass

Clifford Bass said:
Hi,

Try this (untested): Create a query that is based off of the main
client table and the third table, using an outer join. Then use that as the
source of the subreport instead of the third table.

select [tblTheThird].*
from [tblClientInformation] outer join [tblTheThird] on
[tblTheThird].[ClientID] = [tblClientInformation].[ClientID]

This should provide a third-table "row" for each client no matter
whether there is one or not. When there is not one, it will consist entirely
of null fields.

Clifford Bass
Click to expand...
Click to expand...

Thanks Clifford - you solved my problem where I used this approach to create
a query file that excluded the data I didn't want to appear on my report. I
then used that query file as input to my report and voila, my report is
perfect. Many thanks.
 
Back
Top