unique loan acct #s

G

geebee

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
 
T

Tom Ellison

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
 
G

geebee

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
 
T

Tom Ellison

Dear GB:

Yikes is a good technical description for what you have.

I hate Jet! It's so wonderful!

But, we still have to work with it at times.

I recommend this. Start with a new query. Put only a very few columns in
it, maybe just what I posted. Build it up slowly to the whole thing you
want. Find out what makes it fail.

Tom Ellison


geebee said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query fails to eliminate duplicate information 4
conditional criteria 3
yikes 7
Problem querying table linked ODBC Database 2
delete query 4
query speed 5
delete query 1
Query Error 2

Top