Calculate the one field with another from a different record

[Guest]

Scenario: I receive a loan on Jan 1 and it's due date is Jan 15, which gives
the loan a 14-day duration. But, then I come back for another loan (a new
record "row" is created on Jan 21. How do I calculate the days between
previous due date and the new loan date, which is 6? I am trying to keep
track of how many days pass before certain customers return.

HELP!!!

 

['69 Camaro]

Hi, Jerold.

How do I calculate the days between
previous due date and the new loan date, which is 6?

Use a subquery to compare with each record's loan date. For example:

SELECT M.UserID, M.LoanDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM tblLoans AS L
WHERE (L.UserID = M.UserID) AND (L.DueDate < M.LoanDate)), M.LoanDate)
AS DaysBetwLoans
FROM tblLoans AS M
GROUP BY M.UserID, M.LoanDate, M.DueDate

.. . . where UserID is each customer's identification, LoanDate is the date
of the loan, and DueDate is the date the loan is due. The alias "M" is used
for the main query, which is the "current" loan, and the alias "L" is used
for the subquery, which is the previous loan (maximum due date previous to
current loan date). Of course, this query assumes that each customer can
have only one loan outstanding at a time, which, if you're concerned with
counting the days between loans, is probably a very good idea to minimize
financial risk.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

I don't mean to sound slow, but I am. I don't write programs. I use Access
2003. Is there another way to explain this without VBA?

Sorry.

******

 

[Guest]

Is there another way to do this without writing program language? I'm new to
this and use Access 2003.

Sorry and thank you!

Jerold

 

[Guest]

I forgot this part. This is what one of my queries:

SELECT Loan.[Amount of Personal Check], [Loan]![Amount of Personal
Check]*0.15 AS Fee, [Loan]![Amount of Personal Check]*0.85 AS [Customer's
Amount], [Loan]![Check Post Date]-[Loan]![Date of Transaction] AS [Loan
Duration], ([Fee]/[Customer's Amount]/[Loan Duration])*365 AS APR
FROM Loan INNER JOIN [Customer Data] ON Loan.[Social Security #] = [Customer
Data].[Social Security #];

Not sure if this makes sense. I copied this from SQL view in Access.

Jerold

 

['69 Camaro]

Hi, Jerold.

Is there another way to explain this without VBA?

It's not VBA. The example I gave you is written in SQL (another programming
language) that relational database engines understand. Jet is a relational
database engine that Access uses for its data.

Translating your question into SQL will produce the data set that answers
your question. One may use the QBE design grid to drag 'n drop column
names, select combo box values, change a few default settings, and type in
some simple expressions that will translate into a simple SQL statement for
the query, but the query you need to answer your question isn't simple, so
the QBE design grid isn't going to get you very far.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi, Jerold.

Is there another way to do this without writing program language?

No. It doesn't have to be you, but _someone_ has to write the SQL for the
database engine to carry out the commands to produce the necessary data set.
If you don't know SQL yet, then you should learn by taking a class or
getting a tutor. The QBE design grid will only get you so far, but it will
help you learn if you switch back and forth between Design View, Datasheet
View, and then SQL View to see what query produced the data set in the
Datasheet View, and how the QBE design grid was manipulated to get the final
SQL statement.

I'm new to
this and use Access 2003.

I'm sure it looks like Greek now, but after you've been working with it for
a while, it will be a piece of cake. Everyone else who is an expert now
started off in your shoes. It takes time and effort, but it's certainly
do-able.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi, Jerold.

Not sure if this makes sense.

It makes sense, but it's full of errors. Remove the brackets and try to run
the query and you'll see what I mean. Only alphanumeric characters and the
underscore are allowed in names. One should never have to add brackets to
names to get the query to run.

What I gave you in my example was a formula for the SQL statement that you
need to write for your query. Since you didn't give the names of your
columns, I provided column names for due date, loan date, and the customer
identification in my example. You aren't using those exact names, but if I
had to guess, Loan is the name of your table, the SSN is the customer
identification, and date of transaction is the loan date. You'll have to
figure out what the DueDate column in my example is equivalent to in your
table. I can help you part way by plugging in names into the formula, but
you need to fix the errors in your names.

If my guesses on your names are correct, your query should use this formula:

SELECT M.SSN, M.TransactionDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = M.SSN) AND (L.DueDate < M.TransactionDate)),
M.TransactionDate) AS DaysBetwLoans
FROM Loan AS M
GROUP BY M.SSN, M.TransactionDate, M.DueDate

Again, you'll have to figure out what's equivalent to DueDate and replace it
in the query above with your column's name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I forgot this part. This is what one of my queries:

SELECT Loan.[Amount of Personal Check], [Loan]![Amount of Personal
Check]*0.15 AS Fee, [Loan]![Amount of Personal Check]*0.85 AS [Customer's
Amount], [Loan]![Check Post Date]-[Loan]![Date of Transaction] AS [Loan
Duration], ([Fee]/[Customer's Amount]/[Loan Duration])*365 AS APR
FROM Loan INNER JOIN [Customer Data] ON Loan.[Social Security #] =
[Customer
Data].[Social Security #];

Not sure if this makes sense. I copied this from SQL view in Access.

Jerold

Hi, Jerold.


Use a subquery to compare with each record's loan date. For example:

SELECT M.UserID, M.LoanDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM tblLoans AS L
WHERE (L.UserID = M.UserID) AND (L.DueDate < M.LoanDate)),
M.LoanDate)
AS DaysBetwLoans
FROM tblLoans AS M
GROUP BY M.UserID, M.LoanDate, M.DueDate

.. . . where UserID is each customer's identification, LoanDate is the
date
of the loan, and DueDate is the date the loan is due. The alias "M" is
used
for the main query, which is the "current" loan, and the alias "L" is
used
for the subquery, which is the previous loan (maximum due date previous
to
current loan date). Of course, this query assumes that each customer can
have only one loan outstanding at a time, which, if you're concerned with
counting the days between loans, is probably a very good idea to minimize
financial risk.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

One day I am going to get this right. Also, I changed some of the names to be
more simplistic. This is what I wrote

SELECT Loan.SSN, Loan.TransactionDate, Loan.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = Loan.SSN) AND (L.DueDate < Loan.TransactionDate)),
Loan.TransactionDate) AS DaysBetwLoans
FROM Loan AS Loan
GROUP BY Loan.SSN, Loan.TransactionDate, Loan.DueDate

And the response was: "ENTER PARAMETER VALUE" (Loan.TransactionDate)

I entered the above language into a "blank" query (sql) sheet. Is this fine
or should I have added the "Loan table" with it?

Hi, Jerold.

Not sure if this makes sense.

It makes sense, but it's full of errors. Remove the brackets and try to run
the query and you'll see what I mean. Only alphanumeric characters and the
underscore are allowed in names. One should never have to add brackets to
names to get the query to run.

What I gave you in my example was a formula for the SQL statement that you
need to write for your query. Since you didn't give the names of your
columns, I provided column names for due date, loan date, and the customer
identification in my example. You aren't using those exact names, but if I
had to guess, Loan is the name of your table, the SSN is the customer
identification, and date of transaction is the loan date. You'll have to
figure out what the DueDate column in my example is equivalent to in your
table. I can help you part way by plugging in names into the formula, but
you need to fix the errors in your names.

If my guesses on your names are correct, your query should use this formula:

SELECT M.SSN, M.TransactionDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = M.SSN) AND (L.DueDate < M.TransactionDate)),
M.TransactionDate) AS DaysBetwLoans
FROM Loan AS M
GROUP BY M.SSN, M.TransactionDate, M.DueDate

Again, you'll have to figure out what's equivalent to DueDate and replace it
in the query above with your column's name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I forgot this part. This is what one of my queries:

SELECT Loan.[Amount of Personal Check], [Loan]![Amount of Personal
Check]*0.15 AS Fee, [Loan]![Amount of Personal Check]*0.85 AS [Customer's
Amount], [Loan]![Check Post Date]-[Loan]![Date of Transaction] AS [Loan
Duration], ([Fee]/[Customer's Amount]/[Loan Duration])*365 AS APR
FROM Loan INNER JOIN [Customer Data] ON Loan.[Social Security #] =
[Customer
Data].[Social Security #];

Not sure if this makes sense. I copied this from SQL view in Access.

Jerold

Hi, Jerold.

How do I calculate the days between
previous due date and the new loan date, which is 6?

Use a subquery to compare with each record's loan date. For example:

SELECT M.UserID, M.LoanDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM tblLoans AS L
WHERE (L.UserID = M.UserID) AND (L.DueDate < M.LoanDate)),
M.LoanDate)
AS DaysBetwLoans
FROM tblLoans AS M
GROUP BY M.UserID, M.LoanDate, M.DueDate

.. . . where UserID is each customer's identification, LoanDate is the
date
of the loan, and DueDate is the date the loan is due. The alias "M" is
used
for the main query, which is the "current" loan, and the alias "L" is
used
for the subquery, which is the previous loan (maximum due date previous
to
current loan date). Of course, this query assumes that each customer can
have only one loan outstanding at a time, which, if you're concerned with
counting the days between loans, is probably a very good idea to minimize
financial risk.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


Scenario: I receive a loan on Jan 1 and it's due date is Jan 15, which
gives
the loan a 14-day duration. But, then I come back for another loan (a
new
record "row" is created on Jan 21. How do I calculate the days between
previous due date and the new loan date, which is 6? I am trying to
keep
track of how many days pass before certain customers return.

HELP!!!

 

['69 Camaro]

Hi, Jerold.

And the response was: "ENTER PARAMETER VALUE" (Loan.TransactionDate)

This means that the Loan table doesn't have a column named TransactionDate.
Check the spelling to ensure that your query matches the name of the column
in the table.

FROM Loan AS Loan

The name of the table doesn't need to be aliased (that's what the "AS"
keyword is telling the database engine: to use the aliased name "Loan" in
place of the actual name "Loan") if it's the same as the original name. You
can change that line of SQL to:

FROM Loan

I entered the above language into a "blank" query (sql) sheet. Is this
fine

Yes.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

One day I am going to get this right. Also, I changed some of the names to
be
more simplistic. This is what I wrote

SELECT Loan.SSN, Loan.TransactionDate, Loan.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = Loan.SSN) AND (L.DueDate < Loan.TransactionDate)),
Loan.TransactionDate) AS DaysBetwLoans
FROM Loan AS Loan
GROUP BY Loan.SSN, Loan.TransactionDate, Loan.DueDate

And the response was: "ENTER PARAMETER VALUE" (Loan.TransactionDate)

I entered the above language into a "blank" query (sql) sheet. Is this
fine
or should I have added the "Loan table" with it?

Hi, Jerold.

Not sure if this makes sense.

It makes sense, but it's full of errors. Remove the brackets and try to
run
the query and you'll see what I mean. Only alphanumeric characters and
the
underscore are allowed in names. One should never have to add brackets
to
names to get the query to run.

What I gave you in my example was a formula for the SQL statement that
you
need to write for your query. Since you didn't give the names of your
columns, I provided column names for due date, loan date, and the
customer
identification in my example. You aren't using those exact names, but if
I
had to guess, Loan is the name of your table, the SSN is the customer
identification, and date of transaction is the loan date. You'll have to
figure out what the DueDate column in my example is equivalent to in your
table. I can help you part way by plugging in names into the formula,
but
you need to fix the errors in your names.

If my guesses on your names are correct, your query should use this
formula:

SELECT M.SSN, M.TransactionDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = M.SSN) AND (L.DueDate < M.TransactionDate)),
M.TransactionDate) AS DaysBetwLoans
FROM Loan AS M
GROUP BY M.SSN, M.TransactionDate, M.DueDate

Again, you'll have to figure out what's equivalent to DueDate and replace
it
in the query above with your column's name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I forgot this part. This is what one of my queries:

SELECT Loan.[Amount of Personal Check], [Loan]![Amount of Personal
Check]*0.15 AS Fee, [Loan]![Amount of Personal Check]*0.85 AS
[Customer's
Amount], [Loan]![Check Post Date]-[Loan]![Date of Transaction] AS [Loan
Duration], ([Fee]/[Customer's Amount]/[Loan Duration])*365 AS APR
FROM Loan INNER JOIN [Customer Data] ON Loan.[Social Security #] =
[Customer
Data].[Social Security #];

Not sure if this makes sense. I copied this from SQL view in Access.

Jerold


:

Hi, Jerold.

How do I calculate the days between
previous due date and the new loan date, which is 6?

Use a subquery to compare with each record's loan date. For example:

SELECT M.UserID, M.LoanDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM tblLoans AS L
WHERE (L.UserID = M.UserID) AND (L.DueDate < M.LoanDate)),
M.LoanDate)
AS DaysBetwLoans
FROM tblLoans AS M
GROUP BY M.UserID, M.LoanDate, M.DueDate

.. . . where UserID is each customer's identification, LoanDate is the
date
of the loan, and DueDate is the date the loan is due. The alias "M"
is
used
for the main query, which is the "current" loan, and the alias "L" is
used
for the subquery, which is the previous loan (maximum due date
previous
to
current loan date). Of course, this query assumes that each customer
can
have only one loan outstanding at a time, which, if you're concerned
with
counting the days between loans, is probably a very good idea to
minimize
financial risk.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact
info.


Scenario: I receive a loan on Jan 1 and it's due date is Jan 15,
which
gives
the loan a 14-day duration. But, then I come back for another loan
(a
new
record "row" is created on Jan 21. How do I calculate the days
between
previous due date and the new loan date, which is 6? I am trying to
keep
track of how many days pass before certain customers return.

HELP!!!

 

[Guest]

YOU ARE THE MAN. There must be a way I can pay you back or return the favor?
I went to the site on your footer; do you do consulting work?

Have an awesome day!

Jerold

Hi, Jerold.

And the response was: "ENTER PARAMETER VALUE" (Loan.TransactionDate)

This means that the Loan table doesn't have a column named TransactionDate.
Check the spelling to ensure that your query matches the name of the column
in the table.

FROM Loan AS Loan

The name of the table doesn't need to be aliased (that's what the "AS"
keyword is telling the database engine: to use the aliased name "Loan" in
place of the actual name "Loan") if it's the same as the original name. You
can change that line of SQL to:

FROM Loan

I entered the above language into a "blank" query (sql) sheet. Is this
fine

Yes.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

One day I am going to get this right. Also, I changed some of the names to
be
more simplistic. This is what I wrote

SELECT Loan.SSN, Loan.TransactionDate, Loan.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = Loan.SSN) AND (L.DueDate < Loan.TransactionDate)),
Loan.TransactionDate) AS DaysBetwLoans
FROM Loan AS Loan
GROUP BY Loan.SSN, Loan.TransactionDate, Loan.DueDate

And the response was: "ENTER PARAMETER VALUE" (Loan.TransactionDate)

I entered the above language into a "blank" query (sql) sheet. Is this
fine
or should I have added the "Loan table" with it?

Hi, Jerold.

Not sure if this makes sense.

It makes sense, but it's full of errors. Remove the brackets and try to
run
the query and you'll see what I mean. Only alphanumeric characters and
the
underscore are allowed in names. One should never have to add brackets
to
names to get the query to run.

What I gave you in my example was a formula for the SQL statement that
you
need to write for your query. Since you didn't give the names of your
columns, I provided column names for due date, loan date, and the
customer
identification in my example. You aren't using those exact names, but if
I
had to guess, Loan is the name of your table, the SSN is the customer
identification, and date of transaction is the loan date. You'll have to
figure out what the DueDate column in my example is equivalent to in your
table. I can help you part way by plugging in names into the formula,
but
you need to fix the errors in your names.

If my guesses on your names are correct, your query should use this
formula:

SELECT M.SSN, M.TransactionDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = M.SSN) AND (L.DueDate < M.TransactionDate)),
M.TransactionDate) AS DaysBetwLoans
FROM Loan AS M
GROUP BY M.SSN, M.TransactionDate, M.DueDate

Again, you'll have to figure out what's equivalent to DueDate and replace
it
in the query above with your column's name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


I forgot this part. This is what one of my queries:

SELECT Loan.[Amount of Personal Check], [Loan]![Amount of Personal
Check]*0.15 AS Fee, [Loan]![Amount of Personal Check]*0.85 AS
[Customer's
Amount], [Loan]![Check Post Date]-[Loan]![Date of Transaction] AS [Loan
Duration], ([Fee]/[Customer's Amount]/[Loan Duration])*365 AS APR
FROM Loan INNER JOIN [Customer Data] ON Loan.[Social Security #] =
[Customer
Data].[Social Security #];

Not sure if this makes sense. I copied this from SQL view in Access.

Jerold


:

Hi, Jerold.

How do I calculate the days between
previous due date and the new loan date, which is 6?

Use a subquery to compare with each record's loan date. For example:

SELECT M.UserID, M.LoanDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM tblLoans AS L
WHERE (L.UserID = M.UserID) AND (L.DueDate < M.LoanDate)),
M.LoanDate)
AS DaysBetwLoans
FROM tblLoans AS M
GROUP BY M.UserID, M.LoanDate, M.DueDate

.. . . where UserID is each customer's identification, LoanDate is the
date
of the loan, and DueDate is the date the loan is due. The alias "M"
is
used
for the main query, which is the "current" loan, and the alias "L" is
used
for the subquery, which is the previous loan (maximum due date
previous
to
current loan date). Of course, this query assumes that each customer
can
have only one loan outstanding at a time, which, if you're concerned
with
counting the days between loans, is probably a very good idea to
minimize
financial risk.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact
info.


Scenario: I receive a loan on Jan 1 and it's due date is Jan 15,
which
gives
the loan a 14-day duration. But, then I come back for another loan
(a
new
record "row" is created on Jan 21. How do I calculate the days
between
previous due date and the new loan date, which is 6? I am trying to
keep
track of how many days pass before certain customers return.

HELP!!!

 

['69 Camaro]

Hi, Jerold.

YOU ARE THE MAN. There must be a way I can pay you back or return the
favor?
I went to the site on your footer; do you do consulting work?

Awesome! You're very welcome! Yes, I do consulting work. If you need to
hire a consultant, please contact me at the E-mail address near the bottom
of the following Web page:

http://www.Access.QBuilt.com/html/expert_contributors2.html

In addition, I'm setting up the Web site so that people can easily make
donations through PayPal to help us through some recent disasters and keep
the Web site going. (Any amount is gratefully accepted!) That should be
finished today or tomorrow. And checks or money orders are accepted, too.
Anyone can contact me at the same E-mail address located on the Web page
listed above for a street address to send a check or money order to. Thank
you to anyone who helps!

Gratefully,
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

YOU ARE THE MAN. There must be a way I can pay you back or return the
favor?
I went to the site on your footer; do you do consulting work?

Have an awesome day!

Jerold

Hi, Jerold.

And the response was: "ENTER PARAMETER VALUE" (Loan.TransactionDate)

This means that the Loan table doesn't have a column named
TransactionDate.
Check the spelling to ensure that your query matches the name of the
column
in the table.

FROM Loan AS Loan

The name of the table doesn't need to be aliased (that's what the "AS"
keyword is telling the database engine: to use the aliased name "Loan"
in
place of the actual name "Loan") if it's the same as the original name.
You
can change that line of SQL to:

FROM Loan

I entered the above language into a "blank" query (sql) sheet. Is this
fine

Yes.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

One day I am going to get this right. Also, I changed some of the names
to
be
more simplistic. This is what I wrote

SELECT Loan.SSN, Loan.TransactionDate, Loan.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = Loan.SSN) AND (L.DueDate < Loan.TransactionDate)),
Loan.TransactionDate) AS DaysBetwLoans
FROM Loan AS Loan
GROUP BY Loan.SSN, Loan.TransactionDate, Loan.DueDate

And the response was: "ENTER PARAMETER VALUE" (Loan.TransactionDate)

I entered the above language into a "blank" query (sql) sheet. Is this
fine
or should I have added the "Loan table" with it?

:

Hi, Jerold.

Not sure if this makes sense.

It makes sense, but it's full of errors. Remove the brackets and try
to
run
the query and you'll see what I mean. Only alphanumeric characters
and
the
underscore are allowed in names. One should never have to add
brackets
to
names to get the query to run.

What I gave you in my example was a formula for the SQL statement that
you
need to write for your query. Since you didn't give the names of your
columns, I provided column names for due date, loan date, and the
customer
identification in my example. You aren't using those exact names, but
if
I
had to guess, Loan is the name of your table, the SSN is the customer
identification, and date of transaction is the loan date. You'll have
to
figure out what the DueDate column in my example is equivalent to in
your
table. I can help you part way by plugging in names into the formula,
but
you need to fix the errors in your names.

If my guesses on your names are correct, your query should use this
formula:

SELECT M.SSN, M.TransactionDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = M.SSN) AND (L.DueDate < M.TransactionDate)),
M.TransactionDate) AS DaysBetwLoans
FROM Loan AS M
GROUP BY M.SSN, M.TransactionDate, M.DueDate

Again, you'll have to figure out what's equivalent to DueDate and
replace
it
in the query above with your column's name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact
info.


I forgot this part. This is what one of my queries:

SELECT Loan.[Amount of Personal Check], [Loan]![Amount of Personal
Check]*0.15 AS Fee, [Loan]![Amount of Personal Check]*0.85 AS
[Customer's
Amount], [Loan]![Check Post Date]-[Loan]![Date of Transaction] AS
[Loan
Duration], ([Fee]/[Customer's Amount]/[Loan Duration])*365 AS APR
FROM Loan INNER JOIN [Customer Data] ON Loan.[Social Security #] =
[Customer
Data].[Social Security #];

Not sure if this makes sense. I copied this from SQL view in Access.

Jerold


:

Hi, Jerold.

How do I calculate the days between
previous due date and the new loan date, which is 6?

Use a subquery to compare with each record's loan date. For
example:

SELECT M.UserID, M.LoanDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM tblLoans AS L
WHERE (L.UserID = M.UserID) AND (L.DueDate < M.LoanDate)),
M.LoanDate)
AS DaysBetwLoans
FROM tblLoans AS M
GROUP BY M.UserID, M.LoanDate, M.DueDate

.. . . where UserID is each customer's identification, LoanDate is
the
date
of the loan, and DueDate is the date the loan is due. The alias
"M"
is
used
for the main query, which is the "current" loan, and the alias "L"
is
used
for the subquery, which is the previous loan (maximum due date
previous
to
current loan date). Of course, this query assumes that each
customer
can
have only one loan outstanding at a time, which, if you're
concerned
with
counting the days between loans, is probably a very good idea to
minimize
financial risk.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact
info.


Scenario: I receive a loan on Jan 1 and it's due date is Jan 15,
which
gives
the loan a 14-day duration. But, then I come back for another
loan
(a
new
record "row" is created on Jan 21. How do I calculate the days
between
previous due date and the new loan date, which is 6? I am trying
to
keep
track of how many days pass before certain customers return.

HELP!!!

 

[Guest]

Hello again Gunny,

I have come across a problem. Everything works fine, except that I came edit
any of the information produced by that query. I can't even delete or make a
new record. This is what it looks like:

SELECT [DueDate]-[LoanDate] AS [Loan Duration], DateDiff("d",(SELECT
MAX(L.DueDate) FROM Loan AS L WHERE (L.SSN = Loan.SSN) AND (L.DueDate
< Loan.LoanDate)),[Loan].[LoanDate]) AS DaysBetwLoans
FROM Loan;

Is there anything wrong?

What are your fees and where are you located?


--
Jerold, One project at a time...

Hi, Jerold.

YOU ARE THE MAN. There must be a way I can pay you back or return the
favor?
I went to the site on your footer; do you do consulting work?

Awesome! You're very welcome! Yes, I do consulting work. If you need to
hire a consultant, please contact me at the E-mail address near the bottom
of the following Web page:

http://www.Access.QBuilt.com/html/expert_contributors2.html

In addition, I'm setting up the Web site so that people can easily make
donations through PayPal to help us through some recent disasters and keep
the Web site going. (Any amount is gratefully accepted!) That should be
finished today or tomorrow. And checks or money orders are accepted, too.
Anyone can contact me at the same E-mail address located on the Web page
listed above for a street address to send a check or money order to. Thank
you to anyone who helps!

Gratefully,
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

YOU ARE THE MAN. There must be a way I can pay you back or return the
favor?
I went to the site on your footer; do you do consulting work?

Have an awesome day!

Jerold

Hi, Jerold.

And the response was: "ENTER PARAMETER VALUE" (Loan.TransactionDate)

This means that the Loan table doesn't have a column named
TransactionDate.
Check the spelling to ensure that your query matches the name of the
column
in the table.

FROM Loan AS Loan

The name of the table doesn't need to be aliased (that's what the "AS"
keyword is telling the database engine: to use the aliased name "Loan"
in
place of the actual name "Loan") if it's the same as the original name.
You
can change that line of SQL to:

FROM Loan

I entered the above language into a "blank" query (sql) sheet. Is this
fine

Yes.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


One day I am going to get this right. Also, I changed some of the names
to
be
more simplistic. This is what I wrote

SELECT Loan.SSN, Loan.TransactionDate, Loan.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = Loan.SSN) AND (L.DueDate < Loan.TransactionDate)),
Loan.TransactionDate) AS DaysBetwLoans
FROM Loan AS Loan
GROUP BY Loan.SSN, Loan.TransactionDate, Loan.DueDate

And the response was: "ENTER PARAMETER VALUE" (Loan.TransactionDate)

I entered the above language into a "blank" query (sql) sheet. Is this
fine
or should I have added the "Loan table" with it?

:

Hi, Jerold.

Not sure if this makes sense.

It makes sense, but it's full of errors. Remove the brackets and try
to
run
the query and you'll see what I mean. Only alphanumeric characters
and
the
underscore are allowed in names. One should never have to add
brackets
to
names to get the query to run.

What I gave you in my example was a formula for the SQL statement that
you
need to write for your query. Since you didn't give the names of your
columns, I provided column names for due date, loan date, and the
customer
identification in my example. You aren't using those exact names, but
if
I
had to guess, Loan is the name of your table, the SSN is the customer
identification, and date of transaction is the loan date. You'll have
to
figure out what the DueDate column in my example is equivalent to in
your
table. I can help you part way by plugging in names into the formula,
but
you need to fix the errors in your names.

If my guesses on your names are correct, your query should use this
formula:

SELECT M.SSN, M.TransactionDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = M.SSN) AND (L.DueDate < M.TransactionDate)),
M.TransactionDate) AS DaysBetwLoans
FROM Loan AS M
GROUP BY M.SSN, M.TransactionDate, M.DueDate

Again, you'll have to figure out what's equivalent to DueDate and
replace
it
in the query above with your column's name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact
info.


I forgot this part. This is what one of my queries:

SELECT Loan.[Amount of Personal Check], [Loan]![Amount of Personal
Check]*0.15 AS Fee, [Loan]![Amount of Personal Check]*0.85 AS
[Customer's
Amount], [Loan]![Check Post Date]-[Loan]![Date of Transaction] AS
[Loan
Duration], ([Fee]/[Customer's Amount]/[Loan Duration])*365 AS APR
FROM Loan INNER JOIN [Customer Data] ON Loan.[Social Security #] =
[Customer
Data].[Social Security #];

Not sure if this makes sense. I copied this from SQL view in Access.

Jerold


:

Hi, Jerold.

How do I calculate the days between
previous due date and the new loan date, which is 6?

Use a subquery to compare with each record's loan date. For
example:

SELECT M.UserID, M.LoanDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM tblLoans AS L
WHERE (L.UserID = M.UserID) AND (L.DueDate < M.LoanDate)),
M.LoanDate)
AS DaysBetwLoans
FROM tblLoans AS M
GROUP BY M.UserID, M.LoanDate, M.DueDate

.. . . where UserID is each customer's identification, LoanDate is
the
date
of the loan, and DueDate is the date the loan is due. The alias
"M"
is
used
for the main query, which is the "current" loan, and the alias "L"
is
used
for the subquery, which is the previous loan (maximum due date
previous
to
current loan date). Of course, this query assumes that each
customer
can
have only one loan outstanding at a time, which, if you're
concerned
with
counting the days between loans, is probably a very good idea to
minimize
financial risk.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact
info.


Scenario: I receive a loan on Jan 1 and it's due date is Jan 15,
which
gives
the loan a 14-day duration. But, then I come back for another
loan
(a
new
record "row" is created on Jan 21. How do I calculate the days
between
previous due date and the new loan date, which is 6? I am trying
to
keep
track of how many days pass before certain customers return.

HELP!!!

 

['69 Camaro]

Hi, Jerold.

Is there anything wrong?

No. It's just that the query is non-updateable, meaning that one cannot add
new records, delete existing records, nor update any of the calculated
columns. The following are both calculated columns:

1. [DueDate]-[LoanDate]

2. DateDiff("d",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = Loan.SSN) AND (L.DueDate <
Loan.LoanDate)),[Loan].[LoanDate])

You really don't want to do these calculations manually. Let the computer
do the computations. If you need to add, delete, or update records, then
create an updateable query using the columns that those calculations are
derived from. A SELECT statement which includes the SSN, DueDate, and
LoanDate would work. For example:

SELECT SSN, LoanDate, DueDate
FROM Loan
ORDER BY SSN, LoanDate;

Notice that this query is only using the individual column names, not
calculations made with the columns' values. Use the above query for your
data entry, then run the non-updateable query to view the loan durations and
days between loans.

What are your fees

There's no charge if you post your questions here in the newsgroups when you
need help with tricky queries or malfunctioning forms or reports, or post a
question asking for advice or why certain things don't work the way you
think they should. Any expert can answer your question in the newsgroups,
and everyone benefits because they can research the archives of answered
questions to find solutions to their own problems. However, if you need a
consultant to build a database application or restructure an existing one,
that goes beyond what can be handled in a newsgroup post or series of posts,
and you should contact a consultant privately, where you can find out his
fees.

and where are you located?

In California, north of Los Angeles.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Hello again Gunny,

I have come across a problem. Everything works fine, except that I came
edit
any of the information produced by that query. I can't even delete or make
a
new record. This is what it looks like:

SELECT [DueDate]-[LoanDate] AS [Loan Duration], DateDiff("d",(SELECT
MAX(L.DueDate) FROM Loan AS L WHERE (L.SSN = Loan.SSN) AND
(L.DueDate
< Loan.LoanDate)),[Loan].[LoanDate]) AS DaysBetwLoans
FROM Loan;

Is there anything wrong?

What are your fees and where are you located?


--
Jerold, One project at a time...

Hi, Jerold.

YOU ARE THE MAN. There must be a way I can pay you back or return the
favor?
I went to the site on your footer; do you do consulting work?

Awesome! You're very welcome! Yes, I do consulting work. If you need
to
hire a consultant, please contact me at the E-mail address near the
bottom
of the following Web page:

http://www.Access.QBuilt.com/html/expert_contributors2.html

In addition, I'm setting up the Web site so that people can easily make
donations through PayPal to help us through some recent disasters and
keep
the Web site going. (Any amount is gratefully accepted!) That should be
finished today or tomorrow. And checks or money orders are accepted,
too.
Anyone can contact me at the same E-mail address located on the Web page
listed above for a street address to send a check or money order to.
Thank
you to anyone who helps!

Gratefully,
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

YOU ARE THE MAN. There must be a way I can pay you back or return the
favor?
I went to the site on your footer; do you do consulting work?

Have an awesome day!

Jerold

:

Hi, Jerold.

And the response was: "ENTER PARAMETER VALUE" (Loan.TransactionDate)

This means that the Loan table doesn't have a column named
TransactionDate.
Check the spelling to ensure that your query matches the name of the
column
in the table.

FROM Loan AS Loan

The name of the table doesn't need to be aliased (that's what the "AS"
keyword is telling the database engine: to use the aliased name
"Loan"
in
place of the actual name "Loan") if it's the same as the original
name.
You
can change that line of SQL to:

FROM Loan

I entered the above language into a "blank" query (sql) sheet. Is
this
fine

Yes.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact
info.


One day I am going to get this right. Also, I changed some of the
names
to
be
more simplistic. This is what I wrote

SELECT Loan.SSN, Loan.TransactionDate, Loan.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = Loan.SSN) AND (L.DueDate < Loan.TransactionDate)),
Loan.TransactionDate) AS DaysBetwLoans
FROM Loan AS Loan
GROUP BY Loan.SSN, Loan.TransactionDate, Loan.DueDate

And the response was: "ENTER PARAMETER VALUE" (Loan.TransactionDate)

I entered the above language into a "blank" query (sql) sheet. Is
this
fine
or should I have added the "Loan table" with it?

:

Hi, Jerold.

Not sure if this makes sense.

It makes sense, but it's full of errors. Remove the brackets and
try
to
run
the query and you'll see what I mean. Only alphanumeric characters
and
the
underscore are allowed in names. One should never have to add
brackets
to
names to get the query to run.

What I gave you in my example was a formula for the SQL statement
that
you
need to write for your query. Since you didn't give the names of
your
columns, I provided column names for due date, loan date, and the
customer
identification in my example. You aren't using those exact names,
but
if
I
had to guess, Loan is the name of your table, the SSN is the
customer
identification, and date of transaction is the loan date. You'll
have
to
figure out what the DueDate column in my example is equivalent to
in
your
table. I can help you part way by plugging in names into the
formula,
but
you need to fix the errors in your names.

If my guesses on your names are correct, your query should use this
formula:

SELECT M.SSN, M.TransactionDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM Loan AS L
WHERE (L.SSN = M.SSN) AND (L.DueDate < M.TransactionDate)),
M.TransactionDate) AS DaysBetwLoans
FROM Loan AS M
GROUP BY M.SSN, M.TransactionDate, M.DueDate

Again, you'll have to figure out what's equivalent to DueDate and
replace
it
in the query above with your column's name.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact
info.


I forgot this part. This is what one of my queries:

SELECT Loan.[Amount of Personal Check], [Loan]![Amount of
Personal
Check]*0.15 AS Fee, [Loan]![Amount of Personal Check]*0.85 AS
[Customer's
Amount], [Loan]![Check Post Date]-[Loan]![Date of Transaction] AS
[Loan
Duration], ([Fee]/[Customer's Amount]/[Loan Duration])*365 AS APR
FROM Loan INNER JOIN [Customer Data] ON Loan.[Social Security #]
=
[Customer
Data].[Social Security #];

Not sure if this makes sense. I copied this from SQL view in
Access.

Jerold


:

Hi, Jerold.

How do I calculate the days between
previous due date and the new loan date, which is 6?

Use a subquery to compare with each record's loan date. For
example:

SELECT M.UserID, M.LoanDate, M.DueDate,
DATEDIFF("D",
(SELECT MAX(L.DueDate)
FROM tblLoans AS L
WHERE (L.UserID = M.UserID) AND (L.DueDate < M.LoanDate)),
M.LoanDate)
AS DaysBetwLoans
FROM tblLoans AS M
GROUP BY M.UserID, M.LoanDate, M.DueDate

.. . . where UserID is each customer's identification, LoanDate
is
the
date
of the loan, and DueDate is the date the loan is due. The alias
"M"
is
used
for the main query, which is the "current" loan, and the alias
"L"
is
used
for the subquery, which is the previous loan (maximum due date
previous
to
current loan date). Of course, this query assumes that each
customer
can
have only one loan outstanding at a time, which, if you're
concerned
with
counting the days between loans, is probably a very good idea to
minimize
financial risk.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact
info.


Scenario: I receive a loan on Jan 1 and it's due date is Jan
15,
which
gives
the loan a 14-day duration. But, then I come back for another
loan
(a
new
record "row" is created on Jan 21. How do I calculate the days
between
previous due date and the new loan date, which is 6? I am
trying
to
keep
track of how many days pass before certain customers return.

HELP!!!