Number of Payments

[tim]

I am working with a table that contains the payment history for clients over
the past 6 months. This table is made up of fields for each of these months
with the amounts paid for that month. I need 3 separate queries to return
those records of clients who have missed 0, 1, or 2 payments. The first one
where the client has not missed a payment is no problem, but I'm having
trouble with those that have missed 1 or 2 payments. Any help is
appreciated.


Tim
Access 2000

 

[Van T. Dinh]

It sounds very much an incorrect Table structure. If somehow, the
requirement is 12 months and you need to add 6 more Fields to handle this,
you have an incorrect Table structure.

See the "Database Design 101" of Jeff Conrad's Resources for references:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

 

[MGFoster]

I am working with a table that contains the payment history for clients over
the past 6 months. This table is made up of fields for each of these months
with the amounts paid for that month. I need 3 separate queries to return
those records of clients who have missed 0, 1, or 2 payments. The first one
where the client has not missed a payment is no problem, but I'm having
trouble with those that have missed 1 or 2 payments. Any help is
appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your table is not Normalized, which, ironically, means the query is
easier.

1 query for all your needs:

PARAMETERS [Number of missed payments?] Byte;
SELECT customer_id
FROM Payments
WHERE IIf(month1=0,1,0) +
IIf(month2=0,1,0) +
IIf(month3=0,1,0) +
IIf(month4=0,1,0) +
IIf(month5=0,1,0) +
IIf(month6=0,1,0) = [Number of missed payments?]

The WHERE clause, in effect, counts the number of columns that have zero
as the value, then compares that count against the parameter set by the
user. If you want just one query that returns all the customers with 0,
1, 2 missed payments you could do this:

PARAMETERS [Number of missed payments?] Byte;
SELECT customer_id,
IIf(month1=0,1,0) +
IIf(month2=0,1,0) +
IIf(month3=0,1,0) +
IIf(month4=0,1,0) +
IIf(month5=0,1,0) +
IIf(month6=0,1,0) As MissedPayments
FROM Payments
WHERE IIf(month1=0,1,0) +
IIf(month2=0,1,0) +
IIf(month3=0,1,0) +
IIf(month4=0,1,0) +
IIf(month5=0,1,0) +
IIf(month6=0,1,0) IN (0,1,2)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA+AwUBRE6koYechKqOuFEgEQIn2gCfdKqbJKoVchUkR9Xpt9RX6TrDr4cAmJP4
sO/tSGbhilvPpWh7aXQxf6A=
=5SFl
-----END PGP SIGNATURE-----

 

[tim]

I agree the table structure is incorrect, but I have no choice in the mater,
as this is how I get the data.

Thanks
Tim

 

[tim]

That works!

Thank you
Tim

I am working with a table that contains the payment history for clients over
the past 6 months. This table is made up of fields for each of these months
with the amounts paid for that month. I need 3 separate queries to return
those records of clients who have missed 0, 1, or 2 payments. The first one
where the client has not missed a payment is no problem, but I'm having
trouble with those that have missed 1 or 2 payments. Any help is
appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your table is not Normalized, which, ironically, means the query is
easier.

1 query for all your needs:

PARAMETERS [Number of missed payments?] Byte;
SELECT customer_id
FROM Payments
WHERE IIf(month1=0,1,0) +
IIf(month2=0,1,0) +
IIf(month3=0,1,0) +
IIf(month4=0,1,0) +
IIf(month5=0,1,0) +
IIf(month6=0,1,0) = [Number of missed payments?]

The WHERE clause, in effect, counts the number of columns that have zero
as the value, then compares that count against the parameter set by the
user. If you want just one query that returns all the customers with 0,
1, 2 missed payments you could do this:

PARAMETERS [Number of missed payments?] Byte;
SELECT customer_id,
IIf(month1=0,1,0) +
IIf(month2=0,1,0) +
IIf(month3=0,1,0) +
IIf(month4=0,1,0) +
IIf(month5=0,1,0) +
IIf(month6=0,1,0) As MissedPayments
FROM Payments
WHERE IIf(month1=0,1,0) +
IIf(month2=0,1,0) +
IIf(month3=0,1,0) +
IIf(month4=0,1,0) +
IIf(month5=0,1,0) +
IIf(month6=0,1,0) IN (0,1,2)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA+AwUBRE6koYechKqOuFEgEQIn2gCfdKqbJKoVchUkR9Xpt9RX6TrDr4cAmJP4
sO/tSGbhilvPpWh7aXQxf6A=
=5SFl
-----END PGP SIGNATURE-----