Dear Julie:
So, you have a situation in which there are multiple checks on the
same date, right? If you only need to see the LenderCounter,
LoanCounter, and CheckDate there would be no problem. You could just
use DISTINCT. But if you also want to see the LoanBalance, which is
probably NOT the same for all those checks received on the same date
then you must first decide WHICH of thos LoanBalances you want to see.
Unless you program some rule to specify which one you want to see, the
only alternative is to show all of them (I'm still assuming that this
column is different for different rows even where CheckDate is
constant.
Let's say you wanted to show the row where the LoanBalance is largest.
That is based on this thinking: If you get two checks on the same
date, the "first" one is the one resulting in a higher balance for the
account. Of course, if there is interest or a service charge posted
during that day, followed by a second check posting, this may not be
the case. But given the columns you've shown me so far, this is the
only thing I can see.
SELECT LenderCounter, LoanCounter, CheckDate, LoanBalance
FROM [TDS Lender History] T1
WHERE CheckDate = (SELECT MAX(CheckDate)
FROM [TDS Lender History] T2
WHERE T2.LenderCounter = T1.LenderCounter
AND T2.LoanCounter = T1.LoanCounter)
AND T2.LoanBalance = (SELECT MAX(LoanBalance)
FROM [TDS Lender History] T2
WHERE T2.LenderCounter = T1.LenderCounter
AND T2.LoanCounter = T1.LoanCounter
AND T2.CheckDate = (SELECT MAX (CheckDate)
FROM [TDS Lender History] T3
WHERE T3.LenderCounter = T2.LenderCounter
AND T3.LoanCounter = T2.LoanCounter))
ORDER BY LenderCounter, LoanCounter
It looks like its getting messy. I'm trying to get this right just
typing it in, but that's not always going to be perfect. Check
especially whether I have spelled table and column names correctly. I
think the parentheses look right.
If, just by chance, there is another posting, such as interest, that
occurs between two checks that occur on the same date, and if the
amount of the second check is exactly equal to the amount of interest,
then you're going to get the same balance twice on that day. (I'm
making a LOT of assumptions here!) Then, you'd still have two rows
showing up.
The underlying cause of the difficulty is possibly that you do not
uniquely record the two payments on the same day so you can later tell
which came first. If you were to record the time of day as well as
the date, and if you were to create a unique index on this
(Lender/Loan/CheckDate) that would prevent having two payments at the
exact same date/time, then you would not have this problem. So, as I
see it, this is fundamentally a problem resulting from not
anticipating such a situation as you now face back when the table
design was made.
One solution is to NOT show the LoanBalance. You could then use
DISTINCT and your problem is done!
The moral is: Table design is fundamental to proper database
functionality. This should not be news. My considered opinion is
that good table design, which consists of knowing the rules of
normalization PLUS knowing the business well enough to anticipate
needs and correctly model the "real world" inside the computer, is the
most important phase of the development process.
It's hard to build a good house when the foundation is crumbling.
But, if you can patch it up, you may yet succeed.
Hi Tom,
Thank you for your explanation - I thought I understood
it, until I saw the results which are the same results I
was getting... I still have multiple records with the
same loan number for each lender.
Now, I see this is the "potential problem" you were
speaking of.
I do have exact duplicates... and need to remove them but
how would I do that? I don't really care which one is
kept since they are exact duplicates for some reason...
Thank you for your time.
Julie
-----Original Message-----
Dear Julie:
Well, then this should do it:
SELECT DISTINCTROW LenderCounter, LoanCounter, CheckDate, LoanBalance
FROM [TDS Lender History] T1
WHERE CheckDate = (SELECT MAX(CheckDate)
FROM [TDS Lender History] T2
WHERE T2.LenderCounter = T1.LenderCounter
AND T2.LoanCounter = T1.LoanCounter)
ORDER BY LenderCounter, LoanCounter
The only potential problem (and that perhaps a "theoretical" problem)
would occur if there are two checks on the same date for the same
loan. For that situation, and assuming the LoanBalance would be
different for the two rows, you're going to get both rows in the
result. But if they are recorded for the identical date, the computer
can't really tell which one is the most recent.
The above shows the most recent payment, which I put in here because
you were sorting the CheckDate in descending order. However, your
first post asked for the "first record" which could be interpreted as
the oldest, not the most recent. If I've misunderstood this, change
MAX to MIN.
-----Original Message-----
Dear Julie:
The "first record that makes up a group" would probably
mean the
record that has the MAXIMUM value of some other column
(not being
grouped) or perhaps the MAX() of some combination of
such other
columns. This is very commonly needed. I believe
you've indicated
you need to use 3 such columns to determine the "first
record". I'm
hoping that these 3 columns are guaranteed NOT to be
duplicated for
that "first record" - otherwise there is a tie
for "first record" and
the query technique I can show you will return ALL of
them.
Please write your query so it returns all the records
and post the SQL
for that here. State which columns determine a GROUP
and which
columns sort the records within a GROUP thereby
determining which is
the "first record" of each group. I believe I can post
a solution for
you and I'll try to describe it so you can learn the
principles.
OK?
On Mon, 6 Oct 2003 19:19:48 -0700, "Julie Alley"
Anyone know a way to use a query as a recordset or
alone
and only retrieve the first record that makes up a
group -
within a larger data subset?
I have three fields sorted in the order to see the
correct record I want to select as the first record.
When the second field changes, the grouping has changed
and I need to use the first record of this new group
again.
Thanks,
Julie
Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
.
ok,
Here is the SQL,
SELECT DISTINCTROW [TDS Lender History].LenderCounter,
[TDS Lender History].LoanCounter, [TDS Lender
History].CheckDate, [TDS Lender History].LoanBalance
FROM [TDS Lender History]
ORDER BY [TDS Lender History].LenderCounter, [TDS Lender
History].LoanCounter, [TDS Lender History].CheckDate DESC;
The first two fields, LenderCounter, and LoanCounter make
up the groupings, then the 3rd value is used to create
the date sort needed to grab the balance value needed.
Once I have this, I need to write it out to a table.
Thank you again for your guidance.
Julie
Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
.
Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
