Report Help

[CLA]

I've no formal training in Access--learned by trial & error--so this question
may be way too basic, but here goes...I have a report that displays a policy
#, Name, Review Date, and policies numbers that the original policy
references--i.e. policy 8111 is listed in the first column and the last
column may have policies 2021, 3033, 8110 listed on different lines. How do
I get the results (2021, etc.) to print on one line (or two if the list is
long) seperated by a comma instead of having one policy number on one line?
This makes the report way too long!

 

[John W. Vinson]

I've no formal training in Access--learned by trial & error--so this question
may be way too basic, but here goes...I have a report that displays a policy
#, Name, Review Date, and policies numbers that the original policy
references--i.e. policy 8111 is listed in the first column and the last
column may have policies 2021, 3033, 8110 listed on different lines. How do
I get the results (2021, etc.) to print on one line (or two if the list is
long) seperated by a comma instead of having one policy number on one line?
This makes the report way too long!

You'll need some fairly easy VBA code to do this. There's sample code at
http://www.mvps.org/access/modules/mdl0004.htm

 

[Ken Sheridan]

Whether the table is self-referencing or you have two tables the principle is
the same; you iterate through a recordset and built a comma separated list of
policy numbers. A function to do this would go something like this:

Public Function GetPolicies(lngPolicyNumber ) As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strPolicyNumbers As String

strSQL = "SELECT PolicyNumber FROM Policies " & _
"WHERE ReferencedPolicyNumber = " & lngPolicyNumber & _
" ORDER BY Policynumber"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
strPolicyNumbers = strPolicyNumbers & ", " & _
.Fields("PolicyNumber")
.MoveNext
Loop
.Close
' remove leading comma and space
strPolicyNumbers = Mid$( strPolicyNumbers, 3)
End With

GetPolicies = strPolicyNumbers

End Function

In the above I've assume that the table is named Policies, its primary key
column is named PolicyNumber and is a long integer number data type, and
ReferencedPolicyNumber is the name of its foreign key column which
references the original policy by its PolicyNumber.

Put the above function in a standard module and save the module under a
different name from the function, e.g. mdlPolicyStuff. In the report's
underlying query call the above function, passing the PolicyNumber into the
function as its argument. In query design view you'd put the following in
the 'field' row of a blank column in query design view:

ReferencedPolicies: GetPolicies([PolicyNumber]

If you are using two tables one for the original policy and one for the
referenced policies, that's all that's needed. If you are using a single
self-referencing table, however, then if you want one row per original
policy, with the referenced policies shown only in the comma separated list,
you'll need to restrict the query so it returns only the original policies.
The ReferencedPolicyNumber column in this case should be either Null or zero
(the latter if zero is the default value for the column) so the criterion on
the ReferencedPolicyNumber column to restrict the result set would be either
Is Null or 0 as appropriate.

You can achieve a similar result without any code in fact. To do this base
the report on a query which returns the original policies and embed within it
a subreport based on a query which returns the numbers of the referencing
policies, i.e. where the ReferencedPolicyNumber column IS Null or >0 as
appropriate. Make the subreport multi-columned, using an across-then-down
column layout. Set the column number to whatever will fit across the
available space on the page.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Steve:

Not necessarily. The table could be self-referencing. In fact I'd suspect
that's a more likely model in this context.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Oops! Missed a closing parenthesis:

ReferencedPolicies: GetPolicies([PolicyNumber])

Ken Sheridan
Stafford, England

 

[CLA]

Your suspicion would be incorrect. I said I learned by trial and error, not
that I knew nothing--albeit, it may be very little. The table is not
self-referencing. There are two tables with a 1 to many relationship. I
have fixed a switchboard to make it easier for other staff to enter data on a
form and run reports without messing with the tables should they need to use
it. There are possibly 200 policies that can be referenced (125 from other
departments). The main table has Policy#, PolicyName, ReviewDate. The other
table has the referenced policy #. I'm only interested in the 75 policies I
am responsible for and have created queries that asks for a date range so
that I can see what policies are due for review within a certain period. I
have also included the referenced policies in the query so that I can also
make sure that language in the referenced policies has not changed which
would make a difference in our Department's review of our policies (not
interested whether they are due or not unless, of course, they are in my
series). I don't think the "like" scenario will work here. But, then again
I do not understand VBA codes so I'm not sure you gave me the answer I was
looking for (referenced policies displaying on one line seperated by commas
in one column of my report instead of one policy listing on each line.) If I
have five referenced policies for a policy that is due for review, I could
use one line in my report instead of five to list them. Can you simplify
your answer so that I (and other new users) can understand?

 

[Ken Sheridan]

Maybe not that incorrect. It rather sounds to me like you might have what's
known as an 'adjacency list' table via which the main table references
itself. This is most commonly used in manufacturing for bills of materials
or parts explosions, but is equally applicable to other entity types.

Simplify? As Einstein said, when asked how simple an explanation of the
Theory of General Relativity could be made, "As simple as possible, but no
more so".

Firstly add the following function to a standard module. A Standard module
is distinct form a Class Module, and is what you get if you go to the Modules
tab of the database window and add a new module. Essentially it’s a
contained for one or more functions or procedures. You can simply copy and
paste the code below into the module immediately below the couple of lines
you'll already find in the modules 'Declarations' area. That will add the
function to the module. Yiu can then append the table and/or column names as
necessary. Be sure to use a different name when saving the module, not the
same name as the function.

You haven't given your table names so I've called your other table
ReferencedPolicies. Also Its not clear whether Policy# is a policy being
referenced by the main table (i.e what you want included in your list), or
whether it’s a foreign key referencing the main table, so I'll assume it’s
former and that there is another column ReferencedPolicy# as a foreign key
referencing the main table; so if the main table's policy number is 42 and it
references policies 100 and 212 then the rows in ReferencedPolicies to model
this would be:

Policy# ReferencedPolicy#
100 42
212 42

Here's the function:

Public Function GetPolicies(lngPolicyNumber As Long) As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strPolicyNumbers As String

strSQL = "SELECT [Policy#] FROM [ReferencedPolicies] " & _
"WHERE [ReferencedPolicy#] = " & lngPolicyNumber & _
" ORDER BY [Policy#]"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

With rst
Do While Not .EOF
strPolicyNumbers = strPolicyNumbers & ", " & _
.Fields("[Policy#]")
.MoveNext
Loop
.Close
' remove leading comma and space
strPolicyNumbers = Mid$( strPolicyNumbers, 3)
End With

GetPolicies = strPolicyNumbers

End Function

The report's RecordSource will be a query on the main table, which I'll call
Policies. In this you'll call the function as I described in my earlier
post, passing the policy number from the main table into it, so the query
might go something like this:

PARAMETERS [Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT [Policy#], [PolicyName], [ReviewDate],
GetPolicies([Policy#]) AS [Referenced Policies]
FROM [Policies]
WHERE [ReviewDate] BETWEEN [Enter start date:]
AND [Enter end date:];

Note BTW that I've declared the parameters in this query. With date/time
parameters this is advisable as otherwise a date entered at the parameter
prompt in short date format could be interpreted as an arithmetical
expression and give the wrong result.

Base your report on this query and bind a text box to the [Referenced
Policies] column. Make sure the CanGrow property of both the text box and
the detail section is set to True (Yes in the properties sheet) so that if
there are more policy numbers than can be accommodated on one line it will
wrap to a second row.

Ken Sheridan
Stafford, England