how to do this

deepakaa
01-08-2004, 06:13 AM
in my database application i have two tables as given below

(just omit the dotted lines)
table 1 :

student id.....name.............family name..........country........
....001...........X......................A.....................USA.... ....
....002...........Y......................B......................CANADA .....
....003...........z......................c......................Brazil .....
....
....

table 2 :

student id........subject............marks
....001............maths.................50
....001...........chemistry..............60
....002............physics................80
....003............english................75
....003............french.................85

now in the application i let user search above database using student id..
for example....in the textbox named txtsearch..they will write the student id they want to search, for example, 001 and they will click on command button named cmdsearch which will display the result using simple datareport in the following format...

student id.......001
name.............X
family name.....A
country..........USA

subject.............marks..............

maths.................50
chemistry.............60


i want to search database for the student id using sql statment but i dont know how to write sql statement for this purpose...

pls someone help

thanx alot....

rufen101
01-08-2004, 06:38 AM
Try something like that.

select * from table1 t1, table2 t2 where t1.studentid = t2.studentid;

deepakaa
01-08-2004, 06:56 AM
thanx for the rply...

could u pls tell me what is t1 & t2 in ur statement...and in this statement how do u indentify which student id is demanded by the user in the textbox txtsearch to search the database

deepakaa
01-08-2004, 07:58 AM
:confused: i m still confused with this....pls help

Dennis DVR
01-08-2004, 08:17 AM
:confused: i m still confused with this....pls help

the t1 and t2 is an alias name for table1 and table2

may i ask you want this recordset on your dataenvironment?

deepakaa
01-08-2004, 08:37 AM
thanx duane for the rply

actually i tried to search the database using dataenvironment as well...

but the thing is that sometimes user may want to search the database using studen id...sometimes using counrty name...sometimes using subjects....

with dataenvironment i could search only by student id but i cant understand how can i search database using country or subject or name or family name...

pls help me..

thanx

Dennis DVR
01-08-2004, 08:49 AM
thanx duane for the rply

actually i tried to search the database using dataenvironment as well...

but the thing is that sometimes user may want to search the database using studen id...sometimes using counrty name...sometimes using subjects....

with dataenvironment i could search only by student id but i cant understand how can i search database using country or subject or name or family name...

pls help me..

thanx

to be able to do the above mentioned output you will need a hierarchical recordset you need a shape statement as I recall you want the output of this result in your data report right and considering the output that you want it can only be achieved using the hierarchical recordset.

why don't try using your existing query and get the Student ID and pass it to your dataenviroment query and bind your datareport to dataenvironment.

deepakaa
01-08-2004, 09:02 AM
u r right i want the above mentioned output on datareport and i m getting it using dataenvironment as u had told me on this forum before...

but that was while entering new data i generate datareport....now i m coding for the search engine but believe me i m a newbie and first time in my life i m coding a search engine...

by binding dataenvironment to the datareport i could search only by student id but i cant understand how can i search database using country or subject or name or family name...


pls tell me what is hierarchical recordset and what is shape statement how to use it....u can also give me reference to any online documentation or tutorial....

thanx

Dennis DVR
01-08-2004, 09:29 AM
u r right i want the above mentioned output on datareport and i m getting it using dataenvironment as u had told me on this forum before...

but that was while entering new data i generate datareport....now i m coding for the search engine but believe me i m a newbie and first time in my life i m coding a search engine...

by binding dataenvironment to the datareport i could search only by student id but i cant understand how can i search database using country or subject or name or family name...


pls tell me what is hierarchical recordset and what is shape statement how to use it....u can also give me reference to any online documentation or tutorial....

thanx

let's not make things complicated ok you have existing query right in that query you can search for other fields like subject right?
now my question is what if the user decide to search for subject as far as I know searching the subjects will return multiple student id now how you're going to print the records to datareport?

deepakaa
01-08-2004, 09:41 AM
ya execlty if user decides to search using subject it will return multiple student ids....and i want to put them on the report ...and not only subject search by family name or country may as well return multiple studen id...and in both the tables there are around 10 fields in my application....so i want to let user decide by using which field they want to search...

i dont know how to do this....is it easier to link ms excel to the database and to display search results directly in the ms excel worksheet as a table rather than datareport ...??? my application is built in the vb6 and database is access database.

thanx..

Dennis DVR
01-08-2004, 09:51 AM
ya execlty if user decides to search using subject it will return multiple student ids....and i want to put them on the report ...and not only subject search by family name or country may as well return multiple studen id...and in both the tables there are around 10 fields in my application....so i want to let user decide by using which field they want to search...

i dont know how to do this....is it easier to link ms excel to the database and to display search results directly in the ms excel worksheet as a table rather than datareport ...??? my application is built in the vb6 and database is access database.

thanx..

add a dataenvironment and create a parent child command there and in your program populate the recordset in your dataenviroment

i.e
you can change the query to fit your needs or as a user decide what to search this is only a pattern. Search for Student ID
Note : Not tested

Datenvironment1.Commands("cmdMaster").CommandText = "SHAPE {SELECT [Student ID],[Name],[Family Name], [Country] FROM Table1 WHERE [Student ID]='" & Text1.Text & "'" & "} AS cmdMaster APPEND ({SELECT [Student ID], Subject, Marks FROM Table2} RELATE [Student ID]TO [Student ID]) AS cmdChild"

Datenvironment1.Commands("cmdMaster").Execute

deepakaa
01-08-2004, 09:55 AM
ok thanx for that i will check it out...

and what about generating report directly in excel....is it easier..bcos i think if we generate a report as a excel file, it could give more flexibility to the user.....

thanx.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum