multiple table sort

[Jennifer C. Swartz]

I have a database which is called Departments. It has 2 tables which are called DepartmentInfo and WSCStaff. I have set up 2 asp files which display the ‘academic’ departments and the ‘administrative’ departments. I would like my users to be able to click on a department name and pull from both DepartmentInfo and WSCStaff certain cells that would make up a listing of each person in that department. I am having a hard time doing this.

I do not know how to write a script that will do this without defining 65 (1 for each department) queries and using them in my SQL statement. I would like to only have to write 1 script which will pull that departments information only.

I hope I have explained this ok… Can someone help?

[John]

Hi Jennifer,

You don't say how the two tables are connected. Assuming there's some "key"
field that joins them, then you can create a query that joins them (INNER
JOIN probably) and display that query - which would combine the tables.

You don't say which database you're using either. If access, go into the
Query Design wizard and drag your two tables in. Then connect the key
fields by dragging one to the other. Next, drag down the fields you want
to display. Finally, choose View SQL and copy the SQL it generates to the
clipboard. Then paste it into your dbSQL property and you should be all set.
See the examples at www.aspdb.com for more info.

Thanks,
John



------------
Jennifer C. Swartz at 3/20/00 12:46:28 PM


I have a database which is called Departments. It has 2 tables which are called DepartmentInfo and WSCStaff. I have set up 2 asp files which display the ‘academic’ departments and the ‘administrative’ departments. I would like my users to be able to click on a department name and pull from both DepartmentInfo and WSCStaff certain cells that would make up a listing of each person in that department. I am having a hard time doing this.

I do not know how to write a script that will do this without defining 65 (1 for each department) queries and using them in my SQL statement. I would like to only have to write 1 script which will pull that departments information only.

I hope I have explained this ok… Can someone help?

[Jennifer C. Swartz]

Thank you very much! I am using access and I will work on doing an inner join. Advice much appreciated!

Jen

------------
John at 3/21/00 2:02:42 AM

Hi Jennifer,

You don't say how the two tables are connected. Assuming there's some "key"
field that joins them, then you can create a query that joins them (INNER
JOIN probably) and display that query - which would combine the tables.

You don't say which database you're using either. If access, go into the
Query Design wizard and drag your two tables in. Then connect the key
fields by dragging one to the other. Next, drag down the fields you want
to display. Finally, choose View SQL and copy the SQL it generates to the
clipboard. Then paste it into your dbSQL property and you should be all set.
See the examples at www.aspdb.com for more info.

Thanks,
John



------------
Jennifer C. Swartz at 3/20/00 12:46:28 PM


I have a database which is called Departments. It has 2 tables which are called DepartmentInfo and WSCStaff. I have set up 2 asp files which display the ‘academic’ departments and the ‘administrative’ departments. I would like my users to be able to click on a department name and pull from both DepartmentInfo and WSCStaff certain cells that would make up a listing of each person in that department. I am having a hard time doing this.

I do not know how to write a script that will do this without defining 65 (1 for each department) queries and using them in my SQL statement. I would like to only have to write 1 script which will pull that departments information only.

I hope I have explained this ok… Can someone help?