swilliams-Help me Again Please

[bterri]

swilliams gave me great help on my problem but I have one little tweak that needs attention.

Swilliams are you out there?

IE: Thread 222-45160 Report Problem

The code works great but the groupings come out in reverse order. IE:

Jim Smith 8/12/00
Mike Will 9/10/00
Jenny King 11/25/00
Jenny King 10/25/00
Jenny King 9/10/00
Bill White 9/15/00

I need Jenny to be listed at 9/10/00, 10/25/00 etc. Any ideas on how to leave everything alone but fix the groupings themselves?

 

[swilliams]

The order by clause on the second query needs to be looked at. In my example on the previous thread, I ordered by PaymentDue (numeric field), so you probably need to have the due date ordered in ascending order. To alter the example query I gave before:

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

Try this. If it does not work, post the queries that you are using and I will take a look.
Simon

 

[bterri]

I tinkered with the code a little and everything is now working except for the first set of records it finds. (In the first query I had to use a combination of first and last to make Access behave.) The first group it finds puts the listing with the second date. See how it throws Patrick Browns record in the 10/3 area instead of 9/6. All the rest, like Catherine's, are great. Any ideas?

Account No Last Name Payment Due
55010159 Diana Siwa 9/26/00
55011267 Nicholas K Nagy 9/27/00
55011332 Richard D Powelson 9/30/00
55003991 Patrick F Brown 9/6/00
55013678 Patrick F Brown 10/3/00
55011878 Gerald Plum 10/6/00
55009131 Catherine I Griewahn 10/7/00
55007529 Catherine I Griewahn 11/25/00
55006965 Janelle A Mathews 10/8/00


My code is:

First query:
SELECT [Delinquent Loans].[Last Name], last([Delinquent Loans].[Payment Due]) AS [payment due]
FROM [Delinquent Loans]
GROUP BY [Delinquent Loans].[Last Name]
ORDER BY first([Delinquent Loans].[Payment Due]);


Second query:

SELECT [Delinquent Loans].[Account No], [Delinquent Loans].[Last Name], [Delinquent Loans].Collateral, [Delinquent Loans].Balance, [Delinquent Loans].Payment, [Delinquent Loans].[Last Payment], [Delinquent Loans].[Payment Due], [Delinquent Loans].Comments, [Delinquent Loans].[New this month]
FROM [Delinquent Loans] INNER JOIN qryInstOrd ON [Delinquent Loans].[Last Name] = qryInstOrd.[Last Name]
ORDER BY qryInstOrd.[payment due], [Delinquent Loans].[payment due];

 

[swilliams]

When I run the queries posted above on the data posted above I do not get the same results.

I get Catherine I Griewahn appearing last, as the first query picks the last date for each person.

Changing the last in the first query to first gives the correct results for me:

Account No Last Name Payment Due
55003991 Patrick F Brown 9/6/00
55013678 Patrick F Brown 10/3/00
55010159 Diana Siwa 9/26/00
55011267 Nicholas K Nagy 9/27/00
55011332 Richard D Powelson 9/30/00
55011878 Gerald Plum 10/6/00
55009131 Catherine I Griewahn 10/7/00
55007529 Catherine I Griewahn 11/25/00
55006965 Janelle A Mathews 10/8/00
Simon

 

[bterri]

The situation is extrememly odd. If I change the 10/3/00 date for Patrick Brown to 8/25. The records will group correctly and place his two records with the 8/35 date. If I leave it as 10/3 the records group (only his) by the 10/3 date instead of the 9/6/00 date like it should. Why would one date through everything off. Do you have any ideas what could be throwing it. If I can solve this, I'm done with this miserable project!!

 

[swilliams]

It is working for me. I have no idea why you would be getting different results if you are using the same queries as me.
Simon