Collections Application

[Mike W]

After decades of forcing Excel to do what I want, I am trying Access for the
first time.

The application I would like to create for my son's company is collections
of overdue payments. I want to report who should get credit for a payment
that is collected based on who was assigned to that account when the payment
was collected.

As an example, I have set up three accounts and three collection agents.
The people collecting money from any account changes over time.

A sample table with the agent assignment data is shown below. It contains
the account owing money, agent assigned, date of assignment. Example:

Account 27, Annie, 01/01/06
Account 27, Joan, 07/01/06
Account 27, Fred, 01/01/07
Account 04, Fred, 01/01/06
Account 04, Annie, 07/01/06
Account 04, Joan, 01/01/07
Account AA, Joan, 01/01/06
Account AA, Fred, 07/01/06
Account AA, Annie, 01/01/07

I have another table that records payments collected. It contains the
account making a payment, the amount paid and the date paid.

Account AA, 06/16/06, $100
Account 04, 08/01/06, $500
Account 27, 10/01/06, $250
Account 27, 01/15/07, $300

How do I create a report showing the date of payment, account, amount, agent
who gets credit? It should look something like:

06/01/06, Account AA, $100, Joan
08/01/06, Account 04, $500, Annie
10/01/06, Account 27, $250, Joan
01/15/07, Account 27, $300, Fred

I can do this in Excel with an HLOOKUP for the Account combined with a
VLOOKUP for the agent, but Access would cleaner.

 

[Bruce Meneghin]

This would be cake if when changing the assignment of Account 27 from Annie
to Joan you would enter an EndDate for Annie of 06/30/06
Then, finding who belongs to the account when paid is a simple query.
Can you make that change?

 

[Mike W]

Bruce, thanks for the response and yes, I could make the change, but I am
concerned about three things:

1) from a conceptual approach, the end date is redundant data and I was
trying to follow database rules - no redundant data (which I can do if I use
Excel's Lookup)
2) there are chances for errors when you ask for the redundant data to be
entered
3) there is no "end date" for the person currently assigned to an account

Is there no way to get Access to do the calculations without the added data?

I was hoping to use the existing data to do the calculations and report the
date range (start/end) of any assignments

 

[John Nurick]

Hi Mike,

You need to use a query to get the data you need. After that you can
base a report on the query if you need headers, footers and other
formatting.

It sounds as if, for the date of each payment, you want to get the agent
most recently assigned the corresponding account. In other words, for
each payment, you need to find the Assignment record with the same
Account and the latest AssignmentDate that is less than (or is it less
than or equal to?) the PaymentDate and retrieve the Agent from that
record.

Here's one way, though there may well be a neater one. It uses a couple
of nested subqueries that can't be represented in the Access query
design grid (or rather can only be represented as SQL statements);
here's the SQL for the whole thing from my test database.

Create a new query, switch it into SQL view, and paste this in. Then
change the table names and field names to match yours, and with a bit of
luck it will work. Remember that if you have spaces or special
characters in your field and table names you have two choices: (1) get
rid of them; (2) surround them with square brackets [Name with space].
Also, it's a good idea not to use names that are also the names of
common functions or properties (e.g. call your date fields
AssignmentDate and PaymentDate, not just Date).

SELECT Account, PaymentDate, Amount,
(SELECT Agent FROM MikesAssignments AS A
WHERE A.Account = P.Account
AND A.AssignmentDate=(
SELECT MAX(AssignmentDate) FROM MikesAssignments AS B
WHERE B.Account = P.Account
AND B.AssignmentDate < P.PaymentDate
)
) AS Agent
FROM MikesPayments AS P
ORDER BY P.PaymentDate
;

 

[Mike W]

John this looks great! I even understand the query. I'll try it and let you
know.

My apologies about the use of "date". Yes I know "Date" was not a fully
defined Field name, but I was trying to describe my problem briefly.

--
Mike

Hi Mike,

You need to use a query to get the data you need. After that you can
base a report on the query if you need headers, footers and other
formatting.

It sounds as if, for the date of each payment, you want to get the agent
most recently assigned the corresponding account. In other words, for
each payment, you need to find the Assignment record with the same
Account and the latest AssignmentDate that is less than (or is it less
than or equal to?) the PaymentDate and retrieve the Agent from that
record.

Here's one way, though there may well be a neater one. It uses a couple
of nested subqueries that can't be represented in the Access query
design grid (or rather can only be represented as SQL statements);
here's the SQL for the whole thing from my test database.

Create a new query, switch it into SQL view, and paste this in. Then
change the table names and field names to match yours, and with a bit of
luck it will work. Remember that if you have spaces or special
characters in your field and table names you have two choices: (1) get
rid of them; (2) surround them with square brackets [Name with space].
Also, it's a good idea not to use names that are also the names of
common functions or properties (e.g. call your date fields
AssignmentDate and PaymentDate, not just Date).

SELECT Account, PaymentDate, Amount,
(SELECT Agent FROM MikesAssignments AS A
WHERE A.Account = P.Account
AND A.AssignmentDate=(
SELECT MAX(AssignmentDate) FROM MikesAssignments AS B
WHERE B.Account = P.Account
AND B.AssignmentDate < P.PaymentDate
)
) AS Agent
FROM MikesPayments AS P
ORDER BY P.PaymentDate
;

After decades of forcing Excel to do what I want, I am trying Access for the
first time.

The application I would like to create for my son's company is collections
of overdue payments. I want to report who should get credit for a payment
that is collected based on who was assigned to that account when the payment
was collected.

As an example, I have set up three accounts and three collection agents.
The people collecting money from any account changes over time.

A sample table with the agent assignment data is shown below. It contains
the account owing money, agent assigned, date of assignment. Example:

Account 27, Annie, 01/01/06
Account 27, Joan, 07/01/06
Account 27, Fred, 01/01/07
Account 04, Fred, 01/01/06
Account 04, Annie, 07/01/06
Account 04, Joan, 01/01/07
Account AA, Joan, 01/01/06
Account AA, Fred, 07/01/06
Account AA, Annie, 01/01/07

I have another table that records payments collected. It contains the
account making a payment, the amount paid and the date paid.

Account AA, 06/16/06, $100
Account 04, 08/01/06, $500
Account 27, 10/01/06, $250
Account 27, 01/15/07, $300

How do I create a report showing the date of payment, account, amount, agent
who gets credit? It should look something like:

06/01/06, Account AA, $100, Joan
08/01/06, Account 04, $500, Annie
10/01/06, Account 27, $250, Joan
01/15/07, Account 27, $300, Fred

I can do this in Excel with an HLOOKUP for the Account combined with a
VLOOKUP for the agent, but Access would cleaner.

 

[Mike W]

John,

OK. I have tried to implement your great suggestion, but my example did not
sufficiently explain all my tables. I have the following tables.fields
defined:

[_AgentMaster].AgentID
[_AgentMaster].AgentName

[_AccountMaster].AccountID
[_AccountMaster].AccountName

AgentAssignments.AssignmentID
AgentAssignments.AgentID
AgentAssignments.AssignmentStartDate
AgentAssignments.AccountID

AccountPayments.PaymentID
AccountPayments.PaymentAmount
AccountPayments.AccountID
AccountPayments.PaymentDate

I have tried to re-workyour example to suit this table structure, but cannot
get the syntax correct.

The closest I can get is:

SELECT [_AccountMaster].AccountName, AccountPayments.PaymentAmount,
AccountPayments.PaymentDate, [_AgentMaster].AgentName
FROM _AgentMaster INNER JOIN ((_AccountMaster INNER JOIN AccountPayments ON
[_AccountMaster].AccountID=AccountPayments.AccountID) INNER JOIN
AgentAssignments ON [_AccountMaster].AccountID=AgentAssignments.AccountID) ON
[_AgentMaster].AgentID=AgentAssignments.AgentID
WHERE AccountPayments.PaymentDate>=AgentAssignments.AssignmentStartDate;

But that does not include the MAX(AssignmentStartDate) part of your
solution. Can you help me with the syntax?

--
Mike

John this looks great! I even understand the query. I'll try it and let you
know.

My apologies about the use of "date". Yes I know "Date" was not a fully
defined Field name, but I was trying to describe my problem briefly.

--
Mike

Hi Mike,

You need to use a query to get the data you need. After that you can
base a report on the query if you need headers, footers and other
formatting.

It sounds as if, for the date of each payment, you want to get the agent
most recently assigned the corresponding account. In other words, for
each payment, you need to find the Assignment record with the same
Account and the latest AssignmentDate that is less than (or is it less
than or equal to?) the PaymentDate and retrieve the Agent from that
record.

Here's one way, though there may well be a neater one. It uses a couple
of nested subqueries that can't be represented in the Access query
design grid (or rather can only be represented as SQL statements);
here's the SQL for the whole thing from my test database.

Create a new query, switch it into SQL view, and paste this in. Then
change the table names and field names to match yours, and with a bit of
luck it will work. Remember that if you have spaces or special
characters in your field and table names you have two choices: (1) get
rid of them; (2) surround them with square brackets [Name with space].
Also, it's a good idea not to use names that are also the names of
common functions or properties (e.g. call your date fields
AssignmentDate and PaymentDate, not just Date).

SELECT Account, PaymentDate, Amount,
(SELECT Agent FROM MikesAssignments AS A
WHERE A.Account = P.Account
AND A.AssignmentDate=(
SELECT MAX(AssignmentDate) FROM MikesAssignments AS B
WHERE B.Account = P.Account
AND B.AssignmentDate < P.PaymentDate
)
) AS Agent
FROM MikesPayments AS P
ORDER BY P.PaymentDate
;

After decades of forcing Excel to do what I want, I am trying Access for the
first time.

The application I would like to create for my son's company is collections
of overdue payments. I want to report who should get credit for a payment
that is collected based on who was assigned to that account when the payment
was collected.

As an example, I have set up three accounts and three collection agents.
The people collecting money from any account changes over time.

A sample table with the agent assignment data is shown below. It contains
the account owing money, agent assigned, date of assignment. Example:

Account 27, Annie, 01/01/06
Account 27, Joan, 07/01/06
Account 27, Fred, 01/01/07
Account 04, Fred, 01/01/06
Account 04, Annie, 07/01/06
Account 04, Joan, 01/01/07
Account AA, Joan, 01/01/06
Account AA, Fred, 07/01/06
Account AA, Annie, 01/01/07

I have another table that records payments collected. It contains the
account making a payment, the amount paid and the date paid.

Account AA, 06/16/06, $100
Account 04, 08/01/06, $500
Account 27, 10/01/06, $250
Account 27, 01/15/07, $300

How do I create a report showing the date of payment, account, amount, agent
who gets credit? It should look something like:

06/01/06, Account AA, $100, Joan
08/01/06, Account 04, $500, Annie
10/01/06, Account 27, $250, Joan
01/15/07, Account 27, $300, Fred

I can do this in Excel with an HLOOKUP for the Account combined with a
VLOOKUP for the agent, but Access would cleaner.

 

[John Nurick]

Mike,

Take it in stages. My suggestion was

SELECT Account, PaymentDate, Amount,
(SELECT Agent FROM MikesAssignments AS A
WHERE A.Account = P.Account
AND A.AssignmentDate=(
SELECT MAX(AssignmentDate) FROM MikesAssignments AS B
WHERE B.Account = P.Account
AND B.AssignmentDate < P.PaymentDate
)
) AS Agent
FROM MikesPayments AS P
ORDER BY P.PaymentDate
;

Start by taking out the subquery entirely, leaving

SELECT Account, PaymentDate, Amount

FROM MikesPayments AS P
ORDER BY P.PaymentDate
;

Then do a minimal translation:

SELECT P.AccountID, P.PaymentDate, P.PaymentAmount

FROM AccountPayments AS P
ORDER BY P.PaymentDate
;

Next, join the _AccountMaster table to this so you can get the
AccountName. If necessary switch the query from SQL view to Design view.
You'll end up with something like this (and it's a big help touse
careful indenting in SQL view):

SELECT P.AccountID, AM.AccountName,
P.PaymentDate, P.PaymentAmount

FROM AccountPayments AS P INNER JOIN [_AccountMaster] AS AM
ON P.AccountID = AM.AccountID
ORDER BY P.PaymentDate
;

Now it's time to think about the nested subqueries. Note that these
actually only return a single field.

SELECT Agent FROM MikesAssignments AS A
WHERE A.Account = P.Account
AND A.AssignmentDate=(
SELECT MAX(AssignmentDate) FROM MikesAssignments AS B
WHERE B.Account = P.Account
AND B.AssignmentDate < P.PaymentDate
)

Again, start with a minimal translation:

SELECT AgentID FROM AgentAssignments AS A
WHERE A.AccountID = P.AccountID
AND A.AssignmentStartDate=(
SELECT MAX(AssignmentStartDate) FROM AgentAssignments AS B
WHERE B.AccountID = P.AccountID
AND B.AssignmentStartDate < P.PaymentDate
)

And now all we can just join the _AgentMaster table into the outer
subquery so it can return AgentName rather than AgentID:

SELECT M.AgentName FROM AgentAssignments AS A
INNER JOIN [_AgentMaster] AS M
ON A.AgentID = M.AgentID
WHERE A.AccountID = P.AccountID
AND A.AssignmentStartDate=(
SELECT MAX(AssignmentStartDate) FROM AgentAssignments AS B
WHERE B.AccountID = P.AccountID
AND B.AssignmentStartDate < P.PaymentDate
)

before finally putting the subqueries back into the main query,
remembering to add comma, parentheses, and
AS Agent
where needed.

John,

OK. I have tried to implement your great suggestion, but my example did not
sufficiently explain all my tables. I have the following tables.fields
defined:

[_AgentMaster].AgentID
[_AgentMaster].AgentName

[_AccountMaster].AccountID
[_AccountMaster].AccountName

AgentAssignments.AssignmentID
AgentAssignments.AgentID
AgentAssignments.AssignmentStartDate
AgentAssignments.AccountID

AccountPayments.PaymentID
AccountPayments.PaymentAmount
AccountPayments.AccountID
AccountPayments.PaymentDate

I have tried to re-workyour example to suit this table structure, but cannot
get the syntax correct.

The closest I can get is:

SELECT [_AccountMaster].AccountName, AccountPayments.PaymentAmount,
AccountPayments.PaymentDate, [_AgentMaster].AgentName
FROM _AgentMaster INNER JOIN ((_AccountMaster INNER JOIN AccountPayments ON
[_AccountMaster].AccountID=AccountPayments.AccountID) INNER JOIN
AgentAssignments ON [_AccountMaster].AccountID=AgentAssignments.AccountID) ON
[_AgentMaster].AgentID=AgentAssignments.AgentID
WHERE AccountPayments.PaymentDate>=AgentAssignments.AssignmentStartDate;

But that does not include the MAX(AssignmentStartDate) part of your
solution. Can you help me with the syntax?

 

[Mike W]

As before, John, VERY helpful. I'll go work it.
--
Mike

Mike,

Take it in stages. My suggestion was

SELECT Account, PaymentDate, Amount,
(SELECT Agent FROM MikesAssignments AS A
WHERE A.Account = P.Account
AND A.AssignmentDate=(
SELECT MAX(AssignmentDate) FROM MikesAssignments AS B
WHERE B.Account = P.Account
AND B.AssignmentDate < P.PaymentDate
)
) AS Agent
FROM MikesPayments AS P
ORDER BY P.PaymentDate
;

Start by taking out the subquery entirely, leaving

SELECT Account, PaymentDate, Amount

FROM MikesPayments AS P
ORDER BY P.PaymentDate
;

Then do a minimal translation:

SELECT P.AccountID, P.PaymentDate, P.PaymentAmount

FROM AccountPayments AS P
ORDER BY P.PaymentDate
;

Next, join the _AccountMaster table to this so you can get the
AccountName. If necessary switch the query from SQL view to Design view.
You'll end up with something like this (and it's a big help touse
careful indenting in SQL view):

SELECT P.AccountID, AM.AccountName,
P.PaymentDate, P.PaymentAmount

FROM AccountPayments AS P INNER JOIN [_AccountMaster] AS AM
ON P.AccountID = AM.AccountID
ORDER BY P.PaymentDate
;

Now it's time to think about the nested subqueries. Note that these
actually only return a single field.

SELECT Agent FROM MikesAssignments AS A
WHERE A.Account = P.Account
AND A.AssignmentDate=(
SELECT MAX(AssignmentDate) FROM MikesAssignments AS B
WHERE B.Account = P.Account
AND B.AssignmentDate < P.PaymentDate
)

Again, start with a minimal translation:

SELECT AgentID FROM AgentAssignments AS A
WHERE A.AccountID = P.AccountID
AND A.AssignmentStartDate=(
SELECT MAX(AssignmentStartDate) FROM AgentAssignments AS B
WHERE B.AccountID = P.AccountID
AND B.AssignmentStartDate < P.PaymentDate
)

And now all we can just join the _AgentMaster table into the outer
subquery so it can return AgentName rather than AgentID:

SELECT M.AgentName FROM AgentAssignments AS A
INNER JOIN [_AgentMaster] AS M
ON A.AgentID = M.AgentID
WHERE A.AccountID = P.AccountID
AND A.AssignmentStartDate=(
SELECT MAX(AssignmentStartDate) FROM AgentAssignments AS B
WHERE B.AccountID = P.AccountID
AND B.AssignmentStartDate < P.PaymentDate
)

before finally putting the subqueries back into the main query,
remembering to add comma, parentheses, and
AS Agent
where needed.

John,

OK. I have tried to implement your great suggestion, but my example did not
sufficiently explain all my tables. I have the following tables.fields
defined:

[_AgentMaster].AgentID
[_AgentMaster].AgentName

[_AccountMaster].AccountID
[_AccountMaster].AccountName

AgentAssignments.AssignmentID
AgentAssignments.AgentID
AgentAssignments.AssignmentStartDate
AgentAssignments.AccountID

AccountPayments.PaymentID
AccountPayments.PaymentAmount
AccountPayments.AccountID
AccountPayments.PaymentDate

I have tried to re-workyour example to suit this table structure, but cannot
get the syntax correct.

The closest I can get is:

SELECT [_AccountMaster].AccountName, AccountPayments.PaymentAmount,
AccountPayments.PaymentDate, [_AgentMaster].AgentName
FROM _AgentMaster INNER JOIN ((_AccountMaster INNER JOIN AccountPayments ON
[_AccountMaster].AccountID=AccountPayments.AccountID) INNER JOIN
AgentAssignments ON [_AccountMaster].AccountID=AgentAssignments.AccountID) ON
[_AgentMaster].AgentID=AgentAssignments.AgentID
WHERE AccountPayments.PaymentDate>=AgentAssignments.AssignmentStartDate;

But that does not include the MAX(AssignmentStartDate) part of your
solution. Can you help me with the syntax?

 

[Mike W]

John, I got it. My Query is -

SELECT AM.AccountName,
P.PaymentDate, P.PaymentAmount,(
SELECT M.AgentName FROM AgentAssignments AS A
INNER JOIN [_AgentMaster] AS M
ON A.AgentID = M.AgentID
WHERE A.AccountID = P.AccountID
AND A.AssignmentStartDate=(
SELECT MAX(AssignmentStartDate) FROM AgentAssignments AS B
WHERE B.AccountID = P.AccountID
AND B.AssignmentStartDate <= P.PaymentDate
)
) As Agent
FROM AccountPayments AS P INNER JOIN [_AccountMaster] AS AM
ON P.AccountID = AM.AccountID
ORDER BY P.PaymentDate
;

Thank you very much for your help
--
Mike

As before, John, VERY helpful. I'll go work it.
--
Mike

Mike,

Take it in stages. My suggestion was

SELECT Account, PaymentDate, Amount,
(SELECT Agent FROM MikesAssignments AS A
WHERE A.Account = P.Account
AND A.AssignmentDate=(
SELECT MAX(AssignmentDate) FROM MikesAssignments AS B
WHERE B.Account = P.Account
AND B.AssignmentDate < P.PaymentDate
)
) AS Agent
FROM MikesPayments AS P
ORDER BY P.PaymentDate
;

Start by taking out the subquery entirely, leaving

SELECT Account, PaymentDate, Amount

FROM MikesPayments AS P
ORDER BY P.PaymentDate
;

Then do a minimal translation:

SELECT P.AccountID, P.PaymentDate, P.PaymentAmount

FROM AccountPayments AS P
ORDER BY P.PaymentDate
;

Next, join the _AccountMaster table to this so you can get the
AccountName. If necessary switch the query from SQL view to Design view.
You'll end up with something like this (and it's a big help touse
careful indenting in SQL view):

SELECT P.AccountID, AM.AccountName,
P.PaymentDate, P.PaymentAmount

FROM AccountPayments AS P INNER JOIN [_AccountMaster] AS AM
ON P.AccountID = AM.AccountID
ORDER BY P.PaymentDate
;

Now it's time to think about the nested subqueries. Note that these
actually only return a single field.

SELECT Agent FROM MikesAssignments AS A
WHERE A.Account = P.Account
AND A.AssignmentDate=(
SELECT MAX(AssignmentDate) FROM MikesAssignments AS B
WHERE B.Account = P.Account
AND B.AssignmentDate < P.PaymentDate
)

Again, start with a minimal translation:

SELECT AgentID FROM AgentAssignments AS A
WHERE A.AccountID = P.AccountID
AND A.AssignmentStartDate=(
SELECT MAX(AssignmentStartDate) FROM AgentAssignments AS B
WHERE B.AccountID = P.AccountID
AND B.AssignmentStartDate < P.PaymentDate
)

And now all we can just join the _AgentMaster table into the outer
subquery so it can return AgentName rather than AgentID:

SELECT M.AgentName FROM AgentAssignments AS A
INNER JOIN [_AgentMaster] AS M
ON A.AgentID = M.AgentID
WHERE A.AccountID = P.AccountID
AND A.AssignmentStartDate=(
SELECT MAX(AssignmentStartDate) FROM AgentAssignments AS B
WHERE B.AccountID = P.AccountID
AND B.AssignmentStartDate < P.PaymentDate
)

before finally putting the subqueries back into the main query,
remembering to add comma, parentheses, and
AS Agent
where needed.

John,

OK. I have tried to implement your great suggestion, but my example did not
sufficiently explain all my tables. I have the following tables.fields
defined:

[_AgentMaster].AgentID
[_AgentMaster].AgentName

[_AccountMaster].AccountID
[_AccountMaster].AccountName

AgentAssignments.AssignmentID
AgentAssignments.AgentID
AgentAssignments.AssignmentStartDate
AgentAssignments.AccountID

AccountPayments.PaymentID
AccountPayments.PaymentAmount
AccountPayments.AccountID
AccountPayments.PaymentDate

I have tried to re-workyour example to suit this table structure, but cannot
get the syntax correct.

The closest I can get is:

SELECT [_AccountMaster].AccountName, AccountPayments.PaymentAmount,
AccountPayments.PaymentDate, [_AgentMaster].AgentName
FROM _AgentMaster INNER JOIN ((_AccountMaster INNER JOIN AccountPayments ON
[_AccountMaster].AccountID=AccountPayments.AccountID) INNER JOIN
AgentAssignments ON [_AccountMaster].AccountID=AgentAssignments.AccountID) ON
[_AgentMaster].AgentID=AgentAssignments.AgentID
WHERE AccountPayments.PaymentDate>=AgentAssignments.AssignmentStartDate;

But that does not include the MAX(AssignmentStartDate) part of your
solution. Can you help me with the syntax?