 |  |  |  |  |  |  |  |
Extracting data from 3 tables for a DataReport
 |
•
•
Join Date: Jun 2007
Posts: 68
Reputation: 
Solved Threads: 0
Junior Poster in Training
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.
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.
•
•
Join Date: Jun 2007
Posts: 68
Reputation:
Solved Threads: 0
Junior Poster in Training
I know its a bit hard to perform
|
Other Threads in the Visual Basic 4 / 5 / 6 Forum
- Previous Thread: how to resize the coordinates of the picture
- Next Thread: mshflexgrid
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest Visual Basic 4 / 5 / 6 Posts
- Today's Posts
- Unanswered Threads
* 6 429 2007 access activex add age application basic beginner birth bmp calculator cd cells.find click client code college component connection connectionproblemusingvb6usingoledb copy creat ctrl+f data database datareport date delete dissertations dissertationthesis dissertationtopic edit error excel excelmacro file filename form hardware header iamthwee image inboxinvb internetfiledownload keypress label listbox listview liveperson login looping machine microsoft movingranges number objectinsert open oracle password prime program prompt range-objects readfile reading record refresh remotesqlserverdatabase report save search sendbyte sites sort sql sql2008 sqlserver subroutine tags textbox time urldownloadtofile vb vb6 vb6.0 vba visual visualbasic visualbasic6 web window windows
