retrive data from different tables of Ms access
 

hi
i am designing a database using Ms Access and ASP. i have 3 tables in access namely 'PERSONAL', other as 'POLICY' and 3rd one is named as 'STAFF'. in the contact table i have ID, Name, Children as fields. Also in policy table the firlds are:- ID, date_of_policy, no_policy, amount_paid, amount_balance and similarly the 3rd ie., staff has few fields. all three are linked my a common field which is ID

all i want is when the user searches for a perticular name (from frontend) it should search through table- 'PERSONAL' (fields- Name and Children). once it get the perticular name with the help of the ID it should retrive the datafrom both 'POLICY' and 'STAFF' and display in the front end of ASP.

I know how to search data in the table of database but i m a bit confussed on how to retrive data of that perticular name from different table linking the tables

plz help me its important an urgent.
neha

Re: retrive data from different tables of Ms access
 

I don't know ASP, but I do know access and how to use it from VB.

I would create a query which gives me the results I want, ie.e links tables and prompts you for name in name field.

Then I would call that query from VB.

Hope this helps

Re: retrive data from different tables of Ms access
 

thanks for reply
i have created a query that gives me search result but that query works for only in the same table how do i retrive data from other table of same database. my tables are linked in access(relations have been made)
neha

Re: retrive data from different tables of Ms access
 

If you use the query wizard to build the query then you can add as many linked tables as you wish, with some or all fields from them.

If you have built the query manually, then in the box at top of query definition, you presumably have just one table showing. Right click in space and the click show table. You add tables to query and then fields from that table.

Re: retrive data from different tables of Ms access
 

sQuery="SELECT * FROM personal,policy,staff "_
&"WHERE personal.name="&Request.Form("name")&" "_
&"AND personal.id=policy.id "_
&"AND policy.id=staff.id;"

[connection].execute(sQuery)

I never use VBScript - only J(ava)script in ASP, so syntax maybe not ideal, but you get the idea.

Re: retrive data from different tables of Ms access
 

thankyou Denis for ur reply
nut i dont want to use access query or access with vb for my search....i need asp for the same
thank you richfeed your suggestion has given me an idea about how to progress....thankyou i'll let you know the progress
thanks again both of you

Re: retrive data from different tables of Ms access
 

thanks

Re: retrive data from different tables of Ms access
 

Thanks Denis for ur suggestion but i strickly want this to be done in ASP..........

well Richfeed thanks for the help ur code hv given me a way to go ahead....thanks both of u again
i'll let u know the progress
neha