Sorting by Sub Reports.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report of tickets for cliental. Each ticket is displayed with
cliental information and a sub report listing dates,etc in which they have
called. I am seeking to order the sub reports on the form with the oldest
date first. Its appearing that this may not be able to be done. The issue I
believe consists of how the tables where formed and I am stuck doing it this
way. Because of 10,000+ records already stored in the DB I really cannot be
greatly modifying the structure of the tables. Any help would be appreciated.

William
 
Bill,
You should be able to set up the subreport to sort by Date Ascending.
This would show the first date they called as the first/top record in the
subreport.
Sorting of subreports is accomplished through the Sorting and Grouping
dialog box of the subreport itself... the Main report has it's own Sorting
and Grouping.
In Design Mode for the subreport, set your Date field as the first field
in the Sorting and Grouping box, and select Ascending.
 
Al,

It still does not seem to be working correctly. Doing this:

"Sorting of subreports is accomplished through the Sorting and Grouping
dialog box of the subreport itself... the Main report has it's own Sorting
and Grouping."

This seems to just order the items within the sub report. I actually need
the sub reports ordered by thier last known entry. Maybe we are having a
miss understanding in communication. Let me try to draw out what I need.

------------------------------------------------------------------
Other various Info
sub report
{
Call Date: 07/ 05/ 05 Notes: blah blah, etc.
Call Date: 10/ 01/ 05 Notes: blah blah, etc. // This report first
because of this date.
}
------------------------------------------------------------------
Other various Info
sub report
{
Call Date: 03/ 05/ 02 Notes: blah blah, etc.
Call Date: 10/ 10/ 05 Notes: blah blah, etc.
}
------------------------------------------------------------------
Other various Info
sub report
{
Call Date: 02/ 05/ 05 Notes: blah blah, etc.
Call Date: 10/ 11/ 05 Notes: blah blah, etc. // This report last because
of this date.
}
 
Subreport record sources can sometimes be summarized/grouped into the record
source of the main report. For instance if the main report has customers and
the subreport has their purchase orders, you could create a totals query
that groups by customer and includes the latest/max of PODate. Add this
totals query to your main report's record source so that you can sort/group
by Max of PODate in the main report.
 
Sorry Bill,
I don't understand what you're doing here. It appears as though you have
2 CallDates... the first one Descends, while the second Ascends?
The first one is always older than the second, but that's to be expected
if you have CallDate Ascending in your sorting and grouping.
Tell you what...
Give me a good example of how your displays "wrong" as opposed to how you
want it to display. If I still can't get what you're doing, then you can
always create a brand new post, and someone may be better able to help out.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
No problem Al. I believe I want to do something similar to what Duane has
suggested in his post. I believe it was my fault for not being clear enough.

FYI I have a call log of customers. A customer calls in with a bug/issue
and then a Tech submits a 'ticket'. The ticket contains information as per
incident and any follow ups. The report is for any tickets that have yet to
be 'closed'. The report lists each open ticket proceeded by the history of
the follow ups. The follow ups are currently a sub report on the main form. I
was trying to order the report so that the tickets with the oldest last know
follow up are first. That way we know to call that customer first.

I did implement the query Duane suggested. However, I am having an issue
where several people have entered tickets without any follow up information.
Therefore there is never an Maximum Date for several tickets. When this
happens the ticket is omitted from the report. To fix this, I could make
every employee who has done this fix thier tickets , and then add some code
to ensure the data is fully entered in the future. Thanks for the help guys.
Any other suggestions would be appreciated.

Bill
 
Bill,
Sounds like you have the situation under control.
Of course, having your users obey rules about not leaving daiary entries
blank for a transaction is the best way to go. As I always preach...
"better to prevent a problem than code to fix it."

A bit odd though, because I thought you were reporting on all calls from
customers on your main report, and all the details of those calls in the
subreport. So, I would expect the main to display all the calls, and if
there are no transactions against that call, the subreport should just be
blank... it shouldn't "drop" the call itself from the main report.
There may be something wrong with your query against the main report that
causes "no transaction" entries to drop it from the data.

But, having your users enter the data properly is the best solution for
that
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
I have a feeling your right about about the query. The query itself is
dropping the entries that do not have any dairy entries. I'll take a look
into it and see if I can prevent it. Otherwise I will enter some data for the
few blank entries and then write some VBA code to prevent it from reoccuring.
They should be entering that data anyways. Thanks for the help.

Bill
 
You can use a LEFT or RIGHT join in your report's record source to include
all the records from a particular table or query.
 
Bill,
Looks like Duane beat me to the punch... but that's exactly the problem.
Your asking for the query to "Show all records in Transactions, and only
those in Calls that match" It should be the other way around "Show all
Calls, and only those in Transactions that match."
Change the link bewteen the tables in your query accordingly, and that
should take care of the disappearing Calls.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
Back
Top