Cascading Date Help

[BernerOH]

I have one table with loan number and dates "A","B","C","D". The dates are
all different, and the date in "B" is greater than "A", "C" is greater than
"B" etc. Also, If there is a date in field "D", then there will be a date in
"A", "B", "C". Likewise if there is a date in "B" then there will be a date
in "A". If the date is just in "A", there are no other dates.

EX. The date in "C" is 4/1/08, the date in "B" is 3/21/08, and the date in
"A" is 3/10/08. I need to know at what stage the loan was in on 3/22/08.
With one record it is obviously stage "B", but I have approximately 17,000
records. I need to know how many loans were in each stage on a certain date.

Any ideas? If any more information is needed please ask.

 

[Ken Snell \(MVP\)]

Your unnormalized table structure is going to create major problems for you
when you want the answer to these types of questions. Instead of A, B, C, D
fields, you need a separate table that has a record for A date, a record for
B date, a record for C date, and a record for D date. This table should be
related to your current table via the field that identifies the type of
record that the current table is.

Post the exact details of your current table structure, and we can provide
specific suggestions for changing to a normalized table structure that then
can be easily queried for the type of question you have right now.

The specific answer to your question using your current table structure
requires that you build a union query from your current data, then use that
union query as the data set for the query that will answer your current
question.

The union query would be this (name it qryUnionDates):

SELECT LoanNumber, A As StageDate, "A" As StageType
FROM YourTable
UNION ALL
SELECT LoanNumber, B As StageDate, "B" As StageType
FROM YourTable
UNION ALL
SELECT LoanNumber, C As StageDate, "C" As StageType
FROM YourTable
UNION ALL
SELECT LoanNumber, D As StageDate, "D" As StageType
FROM YourTable
ORDER BY LoanNumber, StageDate;


Then you can write a query that returns the information you seek:

PARAMETERS [Enter date for search:] DateTime;
SELECT LoanNumber, Max(StageDate) As LastStageDate, StageType
FROM qryUnionDates
WHERE StageDate <= [Enter date for search:]
GROUP BY LoanNumber, StageType;

 

[strive4peace]

You really should NOT have the multiple dates as fields in one record.
Instead, you should have a Status table

Statuses
- StatusID, autonumber -- primary key
- Status, text

and then a related table for the loan dates

LoanDates
- LoanDateID, autonumber -- primary key
- LoanID, long integer -- FK to Loans
- StatusID, long integer -- FK to Statuses
- LoanDate, date

FK is Foreign Key

WHERE
your Loans table has:
LoanID, autonumber -- primary key

~~~

I realize that you are probably now stuggling with the thought of
converting all your data ... have no fear! Make a record in the
Statuses table for each of the date fields you currently have in your
loans table.

Then, create the records in LoanDates using an append query

for instance, here is the first one:

INSERT INTO LoanDates (LoanID, StatusID, LoanDate)
SELECT LoanID, 1, [fieldname_A]
FROM Loans
WHERE [fieldname_A] Is Not Null;

here is the second one:

INSERT INTO LoanDates (LoanID, StatusID, LoanDate)
SELECT LoanID, 2, [fieldname_B]
FROM Loans
WHERE [fieldname_B] Is Not Null;

.... and so on until you have data from all 5 dates moved

~~~

for better understanding of the basics of Access, download and read this:

Access Basics (on Allen Browne's site)
http://www.allenbrowne.com/casu-22.html
8-part free tutorial that covers essentials in Access

Allen has a wealth of information on his site; after you get to the
bottom of this link, click on 'Index of Tips'



Warm Regards,
Crystal

*
have an awesome day
*

 

[NetworkTrade]

KS's advice is correct. He's an MVP and I am not........But even in your
imperfect state if you really only have ~5 dates then you probably can
survive via brute force.

put an unbound text box in a form that prompts for that "Status Date"? :
5/17/08

use that as the criteria for 5 different queries...

LoanID A Date column
LoanID B Date column
etc....

you'll get five query results for the 5/17/08 date...then you can figure out
how to present them somehow...maybe 5 little subforms/reports or
something....or tie them together into a temp table...

On the otherhand if you have lots of dates....or if they are unstructured in
a way that one record might have 20 dates when others only have 1 or 2...then
your un normalized state will be a killer....

 

[Dale Fye]

Denormalization or Ken's Union Query is probably preferable, but a brute
force method that should work is something like the following (43831 is the
numeric value of 1 Jan 2020):

Parameters [SearchDate] DateTime;
SELECT IIF(CDATE(NZ([D], 43831)) < [SearchDate], "D", _
IIF(CDATE(NZ([C], 43831)) < [SearchDate], "C", _
IIF(CDATE(NZ(, 43831)) < [SearchDate], "B", _
IIF(CDATE(NZ([A], 43831)) < [SearchDate], "A", "N/A")))) as
Status, _
COUNT(LoanID) as StatusCount
FROM yourTable
GROUP BY IIF(CDATE(NZ([D], 43831)) < [SearchDate], "D", _
IIF(CDATE(NZ([C], 43831)) < [SearchDate], "C", _
IIF(CDATE(NZ(, 43831)) < [SearchDate], "B", _
IIF(CDATE(NZ([A], 43831)) < [SearchDate], "A", "N/A"))))

You could get rid of the last comparison if you put a WHERE clause in, that
limits the results to those records where [A] < [SearchDate]. This would
ensure that you were only counting those loans that had actually been opened
prior to the search date (assumes that [A] corresponds with loan initiation).
That might look like:

Parameters [SearchDate] DateTime;
SELECT IIF(CDATE(NZ([D], 43831)) < [SearchDate], "D", _
IIF(CDATE(NZ([C], 43831)) < [SearchDate], "C", _
IIF(CDATE(NZ(, 43831)) < [SearchDate], "B", "A"))) as Status, _
COUNT(LoanID) as StatusCount
FROM yourTable
WHERE NZ([A], 43831) < [SearchDate]
GROUP BY IIF(CDATE(NZ([D], 43831)) < [SearchDate], "D", _
IIF(CDATE(NZ([C], 43831)) < [SearchDate], "C", _
IIF(CDATE(NZ(, 43831)) < [SearchDate], "B", "A")))

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.