Hi,
Consider there are 3 tables as following:
Table1
=============================================================================================
| SerialNum | Date | B | C | D | Table2Number | Table2_Amount | Table3Number | Table3_Amount|
=============================================================================================
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
=============================================================================================
Table2
=============================================
| SerialNum | Date | B | C | D | E | Amount |
=============================================
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
=============================================
Table3
=============================================
| SerialNum | Date | B | C | D | E | Amount |
=============================================
| | | | | | | |
| | | | | | | |
| | | | | | | |
| | | | | | | |
=============================================
Primary keys for each table:
SerialNum field is primary key for the respective tables.
Foreign keys in Table1:
Table2Number :::> Taken from primary key of Table2
Table2_Amount :::> Taken from Amount field of Table2
Table3Number :::> Taken from primary key of Table3
Table3_Amount :::> Taken from Amount field of Table3
Our main area of concern is for Table1. I want to create a DataReport1 in which i will be extracting details from all the above tables based on certain conditions. The situation is that at times either (not both) of the Table2Number or Table3Number field in Table1 will have "NULL" value so, the DataReport1 that i will create must extract all the data from Table1 along with data from Table2 or Table3, whichever's foreign key values in Table1 is not "NULL" value.
NOTE:
1) The extraction is based on either Monthly basis or yearly basis. (i used, WHERE Date <= ? or >= ?)
2) Data entry for Table1 is made such a way that ONLY one of the fields that is, Table2Number or Table3Number must be "NULL".
I had tried many SQL querys to get it right but it extracts both the tables, Table2 and Table3 what ever be the value in the foreign keys Table2Number or Table3Number .
Pls help me solve this. Thank you.
