Append Table Query

[djkc]

I have a table that keeps up with loans. The following fields are used:
name, account, loan date, due date, last date paid. I run a query with
criteria in the last date paid field to show loans between two date ranges
(ex 7/25-8/25). I need to have access classify these accounts as being
delinquent and update the table. What do I have to do in my query to make it
run those dates and input delinquent in a blank field?

 

[Jerry Whittle]

Show us the SQL for the query that you use now. Open the query in design
view. Next go to View, SQL View and copy and past it here. It will help us
to help you.

 

[djkc]

Jerry here's what I have:
SELECT [New Delinquency].[Correct BranchNumber], [New
Delinquency].AcctNumber, [New Delinquency].[Correct Loan Class], [New
Delinquency].LoanDate, [New Delinquency].Balance AS 210, [New
Delinquency].LastPaymentDate, [New Delinquency].NextDueDate
FROM [New Delinquency]
GROUP BY [New Delinquency].[Correct BranchNumber], [New
Delinquency].AcctNumber, [New Delinquency].[Correct Loan Class], [New
Delinquency].LoanDate, [New Delinquency].Balance, [New
Delinquency].LastPaymentDate, [New Delinquency].NextDueDate
HAVING ((([New Delinquency].LastPaymentDate) Between #11/26/2007# And
#12/25/2007#));

 

[Jerry Whittle]

I re-read your post and the answer is: You don't. You should never, well
almost never, store data in a table where the information can be derived from
other fields in the table.

For example if you have a Date of Birth field in a table, it's wrong to also
have an Age field. You can figure out the person's Age from their DOB. Much
the same as your issue.

You can easily identify the delinquent accounts with a slight modification
to your SQL statement. Actually your SQL statement seems to have an unneeded
Group By clause.

SELECT [New Delinquency].[Correct BranchNumber],
[New Delinquency].AcctNumber,
[New Delinquency].[Correct Loan Class],
[New Delinquency].LoanDate,
[New Delinquency].Balance AS 210,
[New Delinquency].LastPaymentDate,
[New Delinquency].NextDueDate,
"Delinquent"
FROM [New Delinquency]
WHERE [New Delinquency].LastPaymentDate)
Between #11/26/2007# and #12/25/2007#;

Run a query like above whenever you need to see the delinquent accounts.

I could see not even needing to enter the dates in the query. For example if
the business rule says that an account is delinquet if there isn't a payment
in the last 90 days, something like this would work:

SELECT [New Delinquency].[Correct BranchNumber],
[New Delinquency].AcctNumber,
[New Delinquency].[Correct Loan Class],
[New Delinquency].LoanDate,
[New Delinquency].Balance AS 210,
[New Delinquency].LastPaymentDate,
[New Delinquency].NextDueDate,
"Delinquent"
FROM [New Delinquency]
WHERE Balance > 0
AND [New Delinquency].LastPaymentDate) < Date() -90;

I'm assuming that if the Balance is 0, then the account can't be delinquent.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry here's what I have:
SELECT [New Delinquency].[Correct BranchNumber], [New
Delinquency].AcctNumber, [New Delinquency].[Correct Loan Class], [New
Delinquency].LoanDate, [New Delinquency].Balance AS 210, [New
Delinquency].LastPaymentDate, [New Delinquency].NextDueDate
FROM [New Delinquency]
GROUP BY [New Delinquency].[Correct BranchNumber], [New
Delinquency].AcctNumber, [New Delinquency].[Correct Loan Class], [New
Delinquency].LoanDate, [New Delinquency].Balance, [New
Delinquency].LastPaymentDate, [New Delinquency].NextDueDate
HAVING ((([New Delinquency].LastPaymentDate) Between #11/26/2007# And
#12/25/2007#));

Show us the SQL for the query that you use now. Open the query in design
view. Next go to View, SQL View and copy and past it here. It will help us
to help you.

 

[djkc]

Jerry,
What if I do this:
I want to go through the dates and classify 30 days past due 60, 90, 120,
etc etc. Can't I do a make table query then append query to add the field to
a new table? i got to have it say that because it's not just 90 days
delinquent like you said. They are considered 90 days delinquent from for
example 05/26-06/25 = 90 days. Can't I use the 1st sql statement you said and
change "Delinquent" to 90 and do an append query? I really don't want to mess
with the original table because it is tied to so many other queries and
reports.

I re-read your post and the answer is: You don't. You should never, well
almost never, store data in a table where the information can be derived from
other fields in the table.

For example if you have a Date of Birth field in a table, it's wrong to also
have an Age field. You can figure out the person's Age from their DOB. Much
the same as your issue.

You can easily identify the delinquent accounts with a slight modification
to your SQL statement. Actually your SQL statement seems to have an unneeded
Group By clause.

SELECT [New Delinquency].[Correct BranchNumber],
[New Delinquency].AcctNumber,
[New Delinquency].[Correct Loan Class],
[New Delinquency].LoanDate,
[New Delinquency].Balance AS 210,
[New Delinquency].LastPaymentDate,
[New Delinquency].NextDueDate,
"Delinquent"
FROM [New Delinquency]
WHERE [New Delinquency].LastPaymentDate)
Between #11/26/2007# and #12/25/2007#;

Run a query like above whenever you need to see the delinquent accounts.

I could see not even needing to enter the dates in the query. For example if
the business rule says that an account is delinquet if there isn't a payment
in the last 90 days, something like this would work:

SELECT [New Delinquency].[Correct BranchNumber],
[New Delinquency].AcctNumber,
[New Delinquency].[Correct Loan Class],
[New Delinquency].LoanDate,
[New Delinquency].Balance AS 210,
[New Delinquency].LastPaymentDate,
[New Delinquency].NextDueDate,
"Delinquent"
FROM [New Delinquency]
WHERE Balance > 0
AND [New Delinquency].LastPaymentDate) < Date() -90;

I'm assuming that if the Balance is 0, then the account can't be delinquent.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry here's what I have:
SELECT [New Delinquency].[Correct BranchNumber], [New
Delinquency].AcctNumber, [New Delinquency].[Correct Loan Class], [New
Delinquency].LoanDate, [New Delinquency].Balance AS 210, [New
Delinquency].LastPaymentDate, [New Delinquency].NextDueDate
FROM [New Delinquency]
GROUP BY [New Delinquency].[Correct BranchNumber], [New
Delinquency].AcctNumber, [New Delinquency].[Correct Loan Class], [New
Delinquency].LoanDate, [New Delinquency].Balance, [New
Delinquency].LastPaymentDate, [New Delinquency].NextDueDate
HAVING ((([New Delinquency].LastPaymentDate) Between #11/26/2007# And
#12/25/2007#));

Show us the SQL for the query that you use now. Open the query in design
view. Next go to View, SQL View and copy and past it here. It will help us
to help you.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a table that keeps up with loans. The following fields are used:
name, account, loan date, due date, last date paid. I run a query with
criteria in the last date paid field to show loans between two date ranges
(ex 7/25-8/25). I need to have access classify these accounts as being
delinquent and update the table. What do I have to do in my query to make it
run those dates and input delinquent in a blank field?

 

[djkc]

Jerry,
Nevermind I got it to work using your suggestion. THANKS!

Jerry,
What if I do this:
I want to go through the dates and classify 30 days past due 60, 90, 120,
etc etc. Can't I do a make table query then append query to add the field to
a new table? i got to have it say that because it's not just 90 days
delinquent like you said. They are considered 90 days delinquent from for
example 05/26-06/25 = 90 days. Can't I use the 1st sql statement you said and
change "Delinquent" to 90 and do an append query? I really don't want to mess
with the original table because it is tied to so many other queries and
reports.

I re-read your post and the answer is: You don't. You should never, well
almost never, store data in a table where the information can be derived from
other fields in the table.

For example if you have a Date of Birth field in a table, it's wrong to also
have an Age field. You can figure out the person's Age from their DOB. Much
the same as your issue.

You can easily identify the delinquent accounts with a slight modification
to your SQL statement. Actually your SQL statement seems to have an unneeded
Group By clause.

SELECT [New Delinquency].[Correct BranchNumber],
[New Delinquency].AcctNumber,
[New Delinquency].[Correct Loan Class],
[New Delinquency].LoanDate,
[New Delinquency].Balance AS 210,
[New Delinquency].LastPaymentDate,
[New Delinquency].NextDueDate,
"Delinquent"
FROM [New Delinquency]
WHERE [New Delinquency].LastPaymentDate)
Between #11/26/2007# and #12/25/2007#;

Run a query like above whenever you need to see the delinquent accounts.

I could see not even needing to enter the dates in the query. For example if
the business rule says that an account is delinquet if there isn't a payment
in the last 90 days, something like this would work:

SELECT [New Delinquency].[Correct BranchNumber],
[New Delinquency].AcctNumber,
[New Delinquency].[Correct Loan Class],
[New Delinquency].LoanDate,
[New Delinquency].Balance AS 210,
[New Delinquency].LastPaymentDate,
[New Delinquency].NextDueDate,
"Delinquent"
FROM [New Delinquency]
WHERE Balance > 0
AND [New Delinquency].LastPaymentDate) < Date() -90;

I'm assuming that if the Balance is 0, then the account can't be delinquent.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry here's what I have:
SELECT [New Delinquency].[Correct BranchNumber], [New
Delinquency].AcctNumber, [New Delinquency].[Correct Loan Class], [New
Delinquency].LoanDate, [New Delinquency].Balance AS 210, [New
Delinquency].LastPaymentDate, [New Delinquency].NextDueDate
FROM [New Delinquency]
GROUP BY [New Delinquency].[Correct BranchNumber], [New
Delinquency].AcctNumber, [New Delinquency].[Correct Loan Class], [New
Delinquency].LoanDate, [New Delinquency].Balance, [New
Delinquency].LastPaymentDate, [New Delinquency].NextDueDate
HAVING ((([New Delinquency].LastPaymentDate) Between #11/26/2007# And
#12/25/2007#));


:

Show us the SQL for the query that you use now. Open the query in design
view. Next go to View, SQL View and copy and past it here. It will help us
to help you.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a table that keeps up with loans. The following fields are used:
name, account, loan date, due date, last date paid. I run a query with
criteria in the last date paid field to show loans between two date ranges
(ex 7/25-8/25). I need to have access classify these accounts as being
delinquent and update the table. What do I have to do in my query to make it
run those dates and input delinquent in a blank field?