Find Bad Debtors: Show select data from query

[Confused87]

I'm creating a database for a charity which tells me who is late forwarding
thier donations to us. I need a screen which tells me this info. A Bad Debt
is someone who has not forwarded us thier donations after 90 days from the
agreed Due Date. I have created a query from the Events Table which shows me
how many days from Now a donation is overdue. I now need to flag the bad
debtors up in some way.

Problem is, every event is one record, so, there are three possible places
that number can show up, meaining I can't use Criteria. This will finish up
as a Subform within a Form - at which stage, and how can I do this?

Welcome to (the relevant part of) my query:

Funds Due 1 || Recieved Date 1 || Expr3 || Funds Due 2|| Recieved Date 2||
Expr4
10/03/2007 ||11/03/2007 || || 20/03/2007 ||
|| 749
01/03/2008 || || 402 || || ||
10/02/2009 || || 56 || || ||

Expr3: IIf(IsNull([Recieved Date 1]),DateDiff("d",[Funds Due 1],Now()))

So, I want record 1 and 2 to be highlighted(either with text next to
them(saying Bad Debt) or as the only ones showing up). 1 because the second
payment is overdue, 2 because the first is overdue. 3 shouldn't show because
it is not overdue even though it is outstanding.

Many Thanks
C

 

[KARL DEWEY]

there are three possible places that number can show up, meaining I can't
use Criteria.
Your database is really a spreadsheet. You should change it to look like
this --
Donor - Text
FundsDue - DateTime
Amount - number - currency
Recieved - DateTime
Donation - number - currency

This way you CAN use criteria to see if overdue or received, if contribution
was equal to promised, how late.

You create another record for each pledged donation.

 

[Confused87]

This part is in effect a spreadsheet, however each record following your
example would have the following:

Donor - Text
FundsDue1 - DateTime
Amount1 - number - currency
Recieved1 - DateTime
Donation1 - number - currency
FundsDue2 - DateTime
Amount2 - number - currency
Recieved2 - DateTime
Donation2 - number - currency
FundsDue3 - DateTime
Amount3 - number - currency
Recieved3 - DateTime
Donation3 - number - currency

I also have a day calculation field, so insert
Days Overdue1
Days Overdue2
Days Overdue3
This is where the criteria would be - however, because I have 3 in one
record it wouldn't work as if Days Overdue 1 was set as the Criteria(the
calculation only enters a value if there is nothing in Recieved and is Is Not
Null), then if the first payment wasn't overdue but the second was it
wouldn't show up, and if I had it written as criteria for all, it would only
show if they were all Not Null.

Thanks for the attention
C

use Criteria.
Your database is really a spreadsheet. You should change it to look like
this --
Donor - Text
FundsDue - DateTime
Amount - number - currency
Recieved - DateTime
Donation - number - currency

This way you CAN use criteria to see if overdue or received, if contribution
was equal to promised, how late.

You create another record for each pledged donation.

I'm creating a database for a charity which tells me who is late forwarding
thier donations to us. I need a screen which tells me this info. A Bad Debt
is someone who has not forwarded us thier donations after 90 days from the
agreed Due Date. I have created a query from the Events Table which shows me
how many days from Now a donation is overdue. I now need to flag the bad
debtors up in some way.

Problem is, every event is one record, so, there are three possible places
that number can show up, meaining I can't use Criteria. This will finish up
as a Subform within a Form - at which stage, and how can I do this?

Welcome to (the relevant part of) my query:

Funds Due 1 || Recieved Date 1 || Expr3 || Funds Due 2|| Recieved Date 2||
Expr4
10/03/2007 ||11/03/2007 || || 20/03/2007 ||
|| 749
01/03/2008 || || 402 || || ||
10/02/2009 || || 56 || || ||

Expr3: IIf(IsNull([Recieved Date 1]),DateDiff("d",[Funds Due 1],Now()))

So, I want record 1 and 2 to be highlighted(either with text next to
them(saying Bad Debt) or as the only ones showing up). 1 because the second
payment is overdue, 2 because the first is overdue. 3 shouldn't show because
it is not overdue even though it is outstanding.

Many Thanks
C

 

[John W. Vinson]

This part is in effect a spreadsheet, however each record following your
example would have the following:


I also have a day calculation field, so insert
Days Overdue1
Days Overdue2
Days Overdue3
This is where the criteria would be - however, because I have 3 in one
record it wouldn't work as if Days Overdue 1 was set as the Criteria(the
calculation only enters a value if there is nothing in Recieved and is Is Not
Null), then if the first payment wasn't overdue but the second was it
wouldn't show up, and if I had it written as criteria for all, it would only
show if they were all Not Null.

Let me agree with and emphasize Karl's suggestion.

The root of your problem is that your table design IS WRONG. It needs to be
restructured!

If you have a one (donor) to many (donations) relationship, you should NOT
have multiple Donation fields; you should have a separate table:

Donors
DonorID (probably autonumber, primary key)
LastName
FirstName
<contact information, biographical information as needed>

Donations
DonationID (autonumber primary key)
DonorID (link to Donors, who made the donation)
PledgeDate (DateTime)
PledgeAmount (Currency)
DonationDate (DateTime)
DonationAmount (Currency)

You could also consider separate Pledges and Donations tables to allow for the
possibility that someone could pledge $1000 and pay it off in four $250
payments, for example.

You should not store the "days overdue" anywhere - instead calculate it using
DateDiff in a query. With the multiple table approach that Karl and I are
suggesting you only have one field to search rather than having to search over
multiple columns in your table.

You're using a relational database - use it relationally!

 

[Confused87]

Ah okay, I think I missed what Karl was saying. Thankyou for spelling it out!


So it would seem since there 2000 members there will need to be 2000
seperate tables for each one of them? Hmmm. Unfortunatly I think I may need
to carry on gimping along with what I've got and not use this function :S

Thank you for your help though, 'tis appreciated

 

[John W. Vinson]

Ah okay, I think I missed what Karl was saying. Thankyou for spelling it out!


So it would seem since there 2000 members there will need to be 2000
seperate tables for each one of them? Hmmm. Unfortunatly I think I may need
to carry on gimping along with what I've got and not use this function :S

Thank you for your help though, 'tis appreciated

I'm afraid you're missing what both Karl and I are saying.

YOU DO NOT NEED 2000 TABLES.
You need *two* tables.

Read it again:

If you have a one (donor) to many (donations) relationship, you should NOT
have multiple Donation fields; you should have a separate table:

Donors
DonorID (probably autonumber, primary key)
LastName
FirstName
<contact information, biographical information as needed>

Donations
DonationID (autonumber primary key)
DonorID (link to Donors, who made the donation)
PledgeDate (DateTime)
PledgeAmount (Currency)
DonationDate (DateTime)
DonationAmount (Currency)

If you have 2000 donors there will be 2000 records in the Donors table (not
2000 donors tables).

If each donor makes four donations you will have 8000 records in the Donations
table - four for each donor.

If some donors make fewer or more donations, there will be fewer or more
records in the Donations table. For instance you could have records like


Donors
124; "Vinson"; "John"; <other information I won't post here>

Donations
31228; 124; #4/14/2009#; $0.00; #4/14/2009#; $0.00
30441; 124; #7/1/2009#; $60.00; NULL; NULL

meaning that this guy named Vinson pledged and donated zero dollars today, and
has pledged $60 for payment on July 1 (and hasn't paid it).

You're using a relational database. USE IT RELATIONALLY!

 

[Confused87]

I feel dense. Sorted now. Thanks for your help, despite its labouriousnes :/