I'm still trying to isolate issues with my SELECT query. I have a table in my veterinary software which stores my sales transactions. The pertinent columns are dat_staff_code (stores the doctor who gets credit for the sale), sys_tran_number (stores a unique transaction #), cli_credit_adj_trans_no (stores the sys_tran_number that a credit is put against).
My problem is that while dat_staff_code is populated for sales, it is not populated when a credit against a sale is issued. So I end up with data as follows: |dat_staff |sys_tran |cli_credit |cli_tran| |_code |_number |_adj_tran_no |_amount | |----------|---------|-------------|--------| |mm |91112 | |50.00 | | |95402 |91112 |-50.00 | What I want to end up with is net sales (sales - credits) GROUP BY dat_staff_code. Where I'm stuck is I can't link the credits to a doctor for the life of me. I use a subquery to get amt where cli_credit_adj_tran_no matches sys_tran_number but I can't associate it with the doctor of the original transaction. This is my query: SELECT (SELECT SUM(cli_tran_amount) FROM vetpmardet WHERE cli_credit_adj_trans_no IN (SELECT sys_tran_number from vetpmardet WHERE cli_tran_trans_date BETWEEN '$pro_week_start_date_yyyymmdd' AND '$pro_week_end_date_yyyymmdd')) + SUM(cli_tran_amount) AS amount FROM vetpmardet WHERE cli_tran_trans_date BETWEEN '$pro_week_start_date_yyyymmdd' AND '$pro_week_end_date_yyyymmdd' AND TRIM(dat_staff_code) LIKE 'mm' Should I be switching to a union query or something? TIA, Caleb ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])