Limiting amount of entries on a sub report

[Matt Steele]

I have a database, and lets say for this question I use the example of an
invoicing system. I have a report that shows me information about each one
of my customers, and a subreport for each customer which shows their
invoices. How can I get the subreport to show ONLY their 3 or 4 most recent
invoices, rather than the whole lot. I am aware of the option to set the CAN
GROW setting to NO for the subreport, but for my application, that doesn't
work, as some invoices use up mutiple lines, where as others don't.

Thanks.

 

[Clifford Bass]

Hi Matt,

Nice, useful! Here is another way. Create a query for your subreport
if you do not already have a query for it and are using a table. Otherwise,
modify the subreport's query. Use the Top nnn and order by clauses, and
specify for each key field's criteria the key fields from the report. So if
your main report's name is "rptRecent Invoices" and the key field is
displayed in a text box named "txtCustomerID" you subreport's query might
look something like this:

select top 4 tblInvoices.*
from tblInvoices
where tblInvoices.CustomerID = [Reports]![rptRecent Invoices]![txtCustomerID]
order by tblInvoices.InvoiceDate desc;

For your subreport clear out all links in the Link Master Fields and
the Link Child Fields properties. If needed, you subreport can re-sort the
records in a different order.

Hopefully that should do it.

Clifford Bass

 

[Matt Steele]

Thanks Ken, and Thanks Cliff.

Cliff - now you understand the question, the actual application I am
applying this to is a customer record system with interaction records. We
keep track of every phone call (in and out) and also emails etc - so when we
print out a customer records for our salesmen, they get a history of all the
interactions we have had with that customer. Problem is the list is getting
pretty big so that why I want to limit it to the last 3 or 5 interactions. I
could send you a copy of the file if you could create that sub report for me
- I can't get my head around it. Not sure how to safely get your email
address if this is what you want to do.

I'm using Access 2007 - does this change the information you have given me?
It seems the querie builder is a bit different in this later version.

Thanks again.

Hi Matt,

Nice, useful! Here is another way. Create a query for your subreport
if you do not already have a query for it and are using a table. Otherwise,
modify the subreport's query. Use the Top nnn and order by clauses, and
specify for each key field's criteria the key fields from the report. So if
your main report's name is "rptRecent Invoices" and the key field is
displayed in a text box named "txtCustomerID" you subreport's query might
look something like this:

select top 4 tblInvoices.*
from tblInvoices
where tblInvoices.CustomerID = [Reports]![rptRecent Invoices]![txtCustomerID]
order by tblInvoices.InvoiceDate desc;

For your subreport clear out all links in the Link Master Fields and
the Link Child Fields properties. If needed, you subreport can re-sort the
records in a different order.

Hopefully that should do it.

Clifford Bass

I have a database, and lets say for this question I use the example of an
invoicing system. I have a report that shows me information about each one
of my customers, and a subreport for each customer which shows their
invoices. How can I get the subreport to show ONLY their 3 or 4 most recent
invoices, rather than the whole lot. I am aware of the option to set the CAN
GROW setting to NO for the subreport, but for my application, that doesn't
work, as some invoices use up mutiple lines, where as others don't.

Thanks.

 

[Clifford Bass]

Hi Matt,

The version of Access does not matter. Ken's method may be the easiest
to implement as long as you have a unique interaction ID across all customers
across all interactions. If you do not, but have to deal with two or more
key fields, my method may be better in terms of writing the query and in
terms of performance. So, if you have a totally unique interaction ID, give
his method a try. If it works and performs speedily, go with that. If not
for either, post back with the specifics of your report name, your report's
key field text boxes' names and your subreport's query or table information.
Then I can use your specifics to specify exactly what to do, which should
help you grasp the concept of how to use my method.

Clifford Bass

 

[Matt Steele]

Thank you both for your replies.

Both my Customers and Interactions have a unique ID number - the whole
system is based on the Projects template (Access 2007) which I have modified
slightly. The "Projects" have become Customers, and the "Tasks" have become
Interactions. The ID number is automatically assigned.

Thanks,

Matt

As Clifford said, it depends on the keys; if the table of customer
interactions has a single column primary key, with my method you'd correlate
the subquery with the outer query on whatever column is the subreport's
table's foreign key referencing your customers table's primary key; with
Clifford's it will work whether the key is a single column or multiple
columns as you'd reference whatever control or controls in the parent report
are bound to its primary key column or columns.

If you can post back with details of the structure of the table of customer
interactions and which columns you want in the subreport we could probably be
more specific in our replies.

There is another method, which is to design the subreport so that it would
normally list all interactions per customer in date descending order and link
it to the parent report in the normal way, but group the subreport by
CustomerID (or whatever the linking column is named), giving it a group
header. The header can be empty and of zero height if you don't need to show
anything in it. Then in the subreport's module declare a module-level
variable of integer data type, initialize it to zero in the group header's
Format event procedure, and increment it by 1 in the detail section's Format
event procedure, setting the return value of the Cancel argument to True when
the value of the variable passes the number of rows you wish to return in
each instance of the subreport. So, to limit the subreport to 3 rows per
customer, the subreport's module would look something like this:

Option Compare Database
Option Explicit

Dim intCounter As Integer


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

intCounter = 0

End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

intCounter = intCounter + 1
Cancel = (intCounter > 3)

End Sub

Ken Sheridan
Stafford, England

Thanks Ken, and Thanks Cliff.

Cliff - now you understand the question, the actual application I am
applying this to is a customer record system with interaction records. We
keep track of every phone call (in and out) and also emails etc - so when we
print out a customer records for our salesmen, they get a history of all the
interactions we have had with that customer. Problem is the list is getting
pretty big so that why I want to limit it to the last 3 or 5 interactions. I
could send you a copy of the file if you could create that sub report for me
- I can't get my head around it. Not sure how to safely get your email
address if this is what you want to do.

I'm using Access 2007 - does this change the information you have given me?
It seems the querie builder is a bit different in this later version.

Thanks again.

Hi Matt,

[quoted text clipped - 28 lines]

Thanks.

 

[Matt Steele]

I'm sorry - I still can't get this working. Here is some aditional
information:

The Table with the customer info is called Projects. The key column is
called ID

The Table with the interactions info is called Tasks. The key column is
called ID.

Thanks,

Matt

As Clifford said, it depends on the keys; if the table of customer
interactions has a single column primary key, with my method you'd correlate
the subquery with the outer query on whatever column is the subreport's
table's foreign key referencing your customers table's primary key; with
Clifford's it will work whether the key is a single column or multiple
columns as you'd reference whatever control or controls in the parent report
are bound to its primary key column or columns.

If you can post back with details of the structure of the table of customer
interactions and which columns you want in the subreport we could probably be
more specific in our replies.

There is another method, which is to design the subreport so that it would
normally list all interactions per customer in date descending order and link
it to the parent report in the normal way, but group the subreport by
CustomerID (or whatever the linking column is named), giving it a group
header. The header can be empty and of zero height if you don't need to show
anything in it. Then in the subreport's module declare a module-level
variable of integer data type, initialize it to zero in the group header's
Format event procedure, and increment it by 1 in the detail section's Format
event procedure, setting the return value of the Cancel argument to True when
the value of the variable passes the number of rows you wish to return in
each instance of the subreport. So, to limit the subreport to 3 rows per
customer, the subreport's module would look something like this:

Option Compare Database
Option Explicit

Dim intCounter As Integer


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

intCounter = 0

End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

intCounter = intCounter + 1
Cancel = (intCounter > 3)

End Sub

Ken Sheridan
Stafford, England

Thanks Ken, and Thanks Cliff.

Cliff - now you understand the question, the actual application I am
applying this to is a customer record system with interaction records. We
keep track of every phone call (in and out) and also emails etc - so when we
print out a customer records for our salesmen, they get a history of all the
interactions we have had with that customer. Problem is the list is getting
pretty big so that why I want to limit it to the last 3 or 5 interactions. I
could send you a copy of the file if you could create that sub report for me
- I can't get my head around it. Not sure how to safely get your email
address if this is what you want to do.

I'm using Access 2007 - does this change the information you have given me?
It seems the querie builder is a bit different in this later version.

Thanks again.

Hi Matt,

[quoted text clipped - 28 lines]

Thanks.

 

[Matt Steele]

Ken,

Thanks.

ID the name of the primary key of Tasks - at least in design veiw, the key
icon is next to the ID column.

The date column is called Start Date

Thanks,

Matt

We'll need a little more info:

1. Is ID the name of the primary key of Tasks or of the foreign key column
which references the primary key of Projects? If not the latter what is the
name of the foreign key column?

2. What is the name of the column in Tasks which holds the date of the
interaction? Is this a date/time data type?

Ken Sheridan
Stafford, England

I'm sorry - I still can't get this working. Here is some aditional
information:

The Table with the customer info is called Projects. The key column is
called ID

The Table with the interactions info is called Tasks. The key column is
called ID.

Thanks,

Matt

As Clifford said, it depends on the keys; if the table of customer
interactions has a single column primary key, with my method you'd correlate

[quoted text clipped - 64 lines]

Thanks.

 

[Matt Steele]

Ken,

I feel like giving up! Ok - on the relationships table the ID field in the
Projects database (which for me is the customer database) is linked to the
Project field in the Tasks Database (which for me is the Interaction
database).

Is there anyway to PM you?

Thanks,

Matt

In that case you must also have a foreign key column, with a different name,
in Tasks which, references the primary key of Projects. Normally keys are
given more specific names, so the primary key of projects might be named
ProjectID and that of tasks TaskID. Tasks would also contain a foreign key
column ProjectID referencing the primary key of Projects. It’s the
equivalent of this ProjectID column in Tasks which I'm trying to ascertain.

Unlike a primary key, a foreign key column is not identified as such in table
design view. It exists by virtue of the relationship between the tables. As
an example take a look at the Orders table in the sample Northwind database
which comes with Access. You'll see that it has a CustomerID column, which
is a foreign key referencing the primary key of Customers. If you open the
relationships window you'll se that these two tables are related on the
CustomerID columns.

To give you chapter and verse on exactly what you need for the subreport's
underlying query I need to know the name of the foreign key column in your
Tasks table.

Ken Sheridan
Stafford, England

Ken,

Thanks.

ID the name of the primary key of Tasks - at least in design veiw, the key
icon is next to the ID column.

The date column is called Start Date

Thanks,

Matt

We'll need a little more info:

[quoted text clipped - 26 lines]

Thanks.

 

[Matt Steele]

Dear Ken,

Many thanks - that has achieved what I want. Just for the records, there
seemed to be a problem with this line:

DER BY TaskDate DESC);

I did change it to the date column name but I got syntax errors. I deleted
this line and added the ); to the end of the line above it and this sorted it
out.

Thanks again.

Matt

In that case the subreport's underlying query would be:

SELECT *
FROM Tasks As T1
WHERE ID IN
(SELECT TOP 3 ID
FROM Tasks AS T2
WHERE T2.Project = T1.Project
ORDER BY TaskDate DESC);

Create the query by opening the query designer; switch to SQL view without
adding any tables; paste in the above SQL statement in place of whatever's
there. Change TaskDate to whatever is the real name of the date column.
Open the query in datasheet view and it should return the last three tasks
per project. They won't necessarily be in order, but tht doesn't matter as
that will be done in the report.

Make the subreport's (i.e. the underlying report object's) RecordSource
property the above query and in report design view sort it by TaskDate (or
whatever its called) in descending order by means of the report's 'sorting
and grouping' mechanism.

In the main parent report set the LinkMasterFields property of the subreport
control to ID and the LinkChildFields property to Project.

Ken Sheridan
Stafford, England

Ken,

I feel like giving up! Ok - on the relationships table the ID field in the
Projects database (which for me is the customer database) is linked to the
Project field in the Tasks Database (which for me is the Interaction
database).

Is there anyway to PM you?

Thanks,

Matt

In that case you must also have a foreign key column, with a different name,
in Tasks which, references the primary key of Projects. Normally keys are

[quoted text clipped - 36 lines]

Thanks.