I started off with:
SELECT distinct
Tbl_Resolved.[Loan Acct #], Min(Tbl_Resolved.ResolvedDt) AS
MinOfResolvedDt,
Tbl_Resolved.ResolvedStatus, Tbl_Resolved.PrevStatus
FROM Tbl_Resolved
WHERE (((Tbl_Resolved.ResolvedStatus)="bk") AND
((Tbl_Resolved.PrevStatus)="new"))
GROUP BY Tbl_Resolved.[Loan Acct #], Tbl_Resolved.ResolvedStatus,
Tbl_Resolved.PrevStatus;
Now I have:
SELECT Tbl_Resolved.[Loan Acct #], Tbl_Resolved.ResolvedStatus,
tbl_resolved.resolveddt, Tbl_Resolved.PrevStatus, Tbl_Resolved.[Buying
Center
#], Tbl_Resolved.[Division Name], Tbl_Resolved.[Region Name],
Tbl_Resolved.[Group Name], Tbl_Resolved.[Collection Center Name],
Tbl_Resolved.[Distribution Channel], Tbl_Resolved.[New Institution Name],
Tbl_Resolved.[Origination Channel], Tbl_Resolved.[Sub Channel],
Tbl_Resolved.[Short Name], Tbl_Resolved.[Loan Status],
Tbl_Resolved.[Contract
Date], Tbl_Resolved.[Mat date], Tbl_Resolved.[Next Pmt Due Date],
Tbl_Resolved.[Payoff Date], Tbl_Resolved.[Paid Off?], Tbl_Resolved.[Nbr of
Reg Pmts Made], Tbl_Resolved.[# Reg Pmts Remaining], Tbl_Resolved.[Pmt Scd
Monthly Pmt Amt], Tbl_Resolved.[Last Pmt Date], Tbl_Resolved.[Last Pmt
Amt],
Tbl_Resolved.[Amended?], Tbl_Resolved.[Amended This Mo?],
Tbl_Resolved.[DEL
Group], Tbl_Resolved.[Days Delinquent], Tbl_Resolved.[Tot Past Due Pmts
$],
Tbl_Resolved.[Principal Balance], Tbl_Resolved.[Val Reserve Dollar Amt],
Tbl_Resolved.NETBAL, Tbl_Resolved.[Ext?], Tbl_Resolved.[Extended This
Mo?],
Tbl_Resolved.[Payoff Dollar Amt], Tbl_Resolved.[Flat Void Mth of Orig],
Tbl_Resolved.[Active BK Flag Date], Tbl_Resolved.[Active BK Flag],
Tbl_Resolved.[Inventory Status], Tbl_Resolved.[Charge Off Principal],
Tbl_Resolved.[Chg Off Type], Tbl_Resolved.[CHG OFF Date],
Tbl_Resolved.[Cure
Letter Date], Tbl_Resolved.[Skip Active Date], Tbl_Resolved.[Skip
Completion
Date], Tbl_Resolved.[Skip Completion Sts], Tbl_Resolved.[Redeemed Date],
Tbl_Resolved.[Entered Date], Tbl_Resolved.[Repo Ordered Date],
Tbl_Resolved.[Active Inactive Flag]
FROM Tbl_Resolved
WHERE resolveddt = (SELECT MAX(resolveddt) from tbl_resolved WHERE
tbl_resolved.[loan acct #] = tbl_resolved.[loan acct #]))
AND (((Tbl_Resolved.ResolvedStatus)="bk") AND
((Tbl_Resolved.PrevStatus)="new"))
NOW I am getting another type of error message:
unrecognized database format 'C:\documents and settings\ed45wsw\local
settings\temp\JETE505.tmp'.
yikes! what now?
thanks in advance,
geebee
Tom Ellison said:
Dear GB:
I'm guessing you started out with this:
SELECT [loan act #], MAX(resolveddates)
FROM SomeTable
GROUP BY [loan act #]
When you add columns to this you are likely grouping by those additional
columns.
This would cause exactly the difficulty you mention.
The solution would be:
SELECT *
FROM SomeTable T
WHERE resolvedate =
(SELECT MAX(resolvedate)
FROM SomeTable T1
WHERE T1.[loan act #] = T.[loan act #])
Does that help? You can, and should, list the columns you want to see
explicitly.
Tom Ellison
geebee said:
hi,
I have a table in which there are tons of records. for example, there
can
be 12 different records inwhich therwe is a [loan act #] of 67, all
having
the same [loan acct #] and with different [resolveddates], and
different
values for the other columns in the table for each record. the problem
is,
when i create a query to get the distinct [loan acct #] along with the
first
occurence of [resolved date] for that account number, the query works
right,
therby only returning one loan account number and the first date for
that
account number. however, when iu add all the other table columns to
the
query, it returns every single row of the table. what going on?
thanks in advance,
geebee
