Need help with query

[Jim]

I'm using a query to print a monthly investment income report. All I enter
in the query is the month (1,2,3,etc). The printed report is based on the
results of this query.

The query first checks to see if there is a value in the PAYMENTS field. If
there is, it then checks the month number against 4 seperate payment fields
(PAY1, PAY2, PAY3, PAY4). If there is a value in any of these fields it will
be printed in the report. The reason for this is because these investments
pay quarterly or semiannually.

This all works fine except that my IRA investment income is listed in the
same column with my regular investment income. I've now made a separate
column in my report for IRA income and I'm trying to change my query so the
IRA income prints in this column - no luck so far.

I've added a new field called IRA PAYMENTS. I need to do the same checks on
this field as I do on PAYMENTS but I can't seem to make it work. If there is
a value in either the PAYMENTS or IRA PAYMENTS fields, I need do the
remaining check on the payment fields (PAY1, etc.)

Can someone explain how I can accomplish this?

TIA

Jim

 

[MGFoster]

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

Your table is incorrect designed. Instead of having PAY1, PAY2, etc.
and IRA PAYMENTS. YOu should have a table like this:

CREATE TABLE Payments (
pay_date DateTime NOT NULL ,
pay_type CHAR(2) NOT NULL
CHECK (pay_type IN ('P1','P2','P3','P4','IR'))
payment_value DOUBLE NOT NULL,
CONSTRAINT PK_Payments PRIMARY KEY (pay_date, pay_type)
)

The CHECK constraint means that only the values in the IN () predicate
can be entered in the column. 'IR' = IRA, 'P1-4' are payment 1-4,
respectively.

Then your query would be like this:

PARAMETERS [Enter Month Number] Byte;
SELECT pay_type, Sum(payment_value) As TotalPayments
FROM Payments
WHERE Month(pay_date) = [Enter month number]
GROUP BY pay_type

Output would be something like this:

pay_type TotalPayments
======== ==============
P1 25000.00
P3 500.00
IR 0.25

You could get just the payment periods you want by adding to the WHERE
clause:

WHERE pay_type = 'P1'

Read a book on db design to find out about table Normalization. You
were violating the 2nd Normal form. A db design book I liked was
_Database Design for Mere Mortals_ by Hernandez.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBREWYN4echKqOuFEgEQIbtwCg51L93nJQb45lyf8xxdHLWz9XPy4An11w
e9EYQFzZ2HWt9/qQjNk+oQJh
=imhM
-----END PGP SIGNATURE-----