Report problem 1

[bterri]

I am hoping this problem can be solved by someone with a good VB background because I can't do it with the tools in Access.

I have a report that must be sorted on the column called Payment Due. This works fine except that if a person appears twice in the report they need to be listed consecutively in the report --more or less taking a quick break from the sort order! In Access you get one or the other-either by Payment or by Name. I have tried manipulating queries until my fingers hurt. I know there must be a little piece of code someone know that will let me do this.

Thanks,

 

[swilliams]

I think this is what you are looking for:

For testing purposes, I set up a table like so:

PaymentsDue:
ID Name PaymentDue DueDate
1 Gary 1 1/5/2001 12:02:05 AM
2 Ian 2 1/5/2001 12:02:08 AM
3 Greg 3 1/5/2001 12:02:19 AM
4 Simon 4 1/5/2001 12:02:25 AM
5 Graham 5 1/5/2001 12:02:29 AM
6 Ian 6 1/5/2001 12:03:46 AM
7 Simon 7 1/5/2001 12:03:51 AM
8 Greg 8 1/5/2001 12:04:02 AM
9 John 9 1/5/2001 12:04:07 AM
10 James 10 1/5/2001 12:04:12 AM

Then, I set up one query called qryBase, with the SQL statement:

SELECT PaymentsDue.Name, First(PaymentsDue.PaymentDue) AS PaymentDue
FROM PaymentsDue
GROUP BY PaymentsDue.Name
ORDER BY First(PaymentsDue.PaymentDue);

And then set up another query called qryPaymentsDue, with the SQL statement:

SELECT PaymentsDue.*
FROM PaymentsDue INNER JOIN qryBase ON PaymentsDue.Name = qryBase.Name
ORDER BY qryBase.PaymentDue, PaymentsDue.PaymentDue;

The results of the second query are:

ID Name PaymentDue DueDate
1 Gary 1 1/5/2001 12:02:05 AM
2 Ian 2 1/5/2001 12:02:08 AM
6 Ian 6 1/5/2001 12:03:46 AM
3 Greg 3 1/5/2001 12:02:19 AM
8 Greg 8 1/5/2001 12:04:02 AM
4 Simon 4 1/5/2001 12:02:25 AM
7 Simon 7 1/5/2001 12:03:51 AM
5 Graham 5 1/5/2001 12:02:29 AM
9 John 9 1/5/2001 12:04:07 AM
10 James 10 1/5/2001 12:04:12 AM

Hope this helps.

(PS - If you want the results in the reverse order - highest due amount first, plus any associated due amounts, followed by the next highest,... - then you will have to do two things:

1. chnge the first sql statement to:
SELECT PaymentsDue.Name, Last(PaymentsDue.PaymentDue) AS PaymentDue
FROM PaymentsDue
GROUP BY PaymentsDue.Name
ORDER BY Last(PaymentsDue.PaymentDue);

2. change the second sql statement :
SELECT PaymentsDue.*
FROM PaymentsDue INNER JOIN qryBase ON PaymentsDue.Name = qryBase.Name
ORDER BY qryBase.PaymentDue DESC , PaymentsDue.PaymentDue DESC;)
Simon

 

[bterri]

This looks like it will help but I'm running into a problem that possibly you could help me with.

This is the code I type in

SELECT [90 days].Name, first([90 days].[Payment Due]) as [payment due] FROM [90 days];

The table it is pulling from has the following columns:
Acct No, Payment Due, Name, Days OD, Collateral, Last Pmt, Balance, Comments and Collateral Value.

When I try and view the query using the code I typed in above I get "You tried to execute a query tht does not include the specified expresssion 'Name' as part of an aggregate function." I have tried different bracketing but nothing seems to work.

Any ideas?

 

[swilliams]

SELECT [90 days].Name, first([90 days].[Payment Due]) as [payment due] FROM [90 days] GROUP BY [90 days].Name;
Simon

 

[bterri]

Thank you so much. I hope I can return the favor someday.