Help with code

[mwagoner]

Currently I have a query that brings all the information i need except
for one piece found in another table. My current query looks like:

strConnect = "Driver={Microsoft Access Driver (*.mdb)};
DBQ=\\CALSJ1\PMAPPS\pmdata.mdb"

Set objRecordset = Server.CreateObject ("ADODB.Recordset")

objRecordset.Open "mstJobs", strConnect, adOpenStatic,
adLockOptimistic, adCmdTable
I need to pull the JobName from another table in which both tables have
the JobNumber in common.

I am not sure how what the SQL syntax for doing this would be...

Any help is appreciated.

Thanks

[zman]

mw******@caldrywall.com wrote:

Currently I have a query that brings all the information i need except
for one piece found in another table. My current query looks like:

strConnect = "Driver={Microsoft Access Driver (*.mdb)};
DBQ=\\CALSJ1\PMAPPS\pmdata.mdb"

Set objRecordset = Server.CreateObject ("ADODB.Recordset")

objRecordset.Open "mstJobs", strConnect, adOpenStatic,
adLockOptimistic, adCmdTable
I need to pull the JobName from another table in which both tables have
the JobNumber in common.

I am not sure how what the SQL syntax for doing this would be...

Any help is appreciated.

Thanks

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=c:\anydatabase.mdb"

set RS = Conn.Execute("Select table1.field1 as f1, table2.field2 as f2
FROM table1, table2 WHERE table1.field1=table2.field2")

If not RS.eof then
RS.movefirst
Do
Response.write "table1 field1 = " & RS("f1") & "table2 field2 = " &
RS("f2")
Rs.movenext
Loop until RS.eof
End if
hope it helps

zafar
--------------------------------
http://www.vbuniverse.com/

[Mike Brind]

mw******@caldrywall.com wrote:

Currently I have a query that brings all the information i need except
for one piece found in another table. My current query looks like:

strConnect = "Driver={Microsoft Access Driver (*.mdb)};
DBQ=\\CALSJ1\PMAPPS\pmdata.mdb"

Set objRecordset = Server.CreateObject ("ADODB.Recordset")

objRecordset.Open "mstJobs", strConnect, adOpenStatic,
adLockOptimistic, adCmdTable
I need to pull the JobName from another table in which both tables have
the JobNumber in common.

I am not sure how what the SQL syntax for doing this would be...

Any help is appreciated.

Thanks

You need to use a JOIN. The easiest way to learn this syntax is to do
create some queries linking the two tables in Access, then looking at
the resulting SQL. You haven't given any field or table names in your
post, so here's an example:

tblJobDetails
ID
JobNumber
JobDate
JobManager

tblJob
JobNumber
JobName

SELECT tblJobDetails.ID, tblJobDetails.JobDate,
tblJobDetails.JobManager, tblJob.JobName FROM tblJobDetails INNER JOIN
tblJob ON tblJobDetails.JobNumber = tblJob.JobNumber.

By the way, you are using the old ODBC driver. You should switch to
the native OLEDB driver. See here:
http://www.aspfaq.com/show.asp?id=2126

--
Mike Brind

[Mike Brind]

mw******@caldrywall.com wrote:

Currently I have a query that brings all the information i need except
for one piece found in another table. My current query looks like:

strConnect = "Driver={Microsoft Access Driver (*.mdb)};
DBQ=\\CALSJ1\PMAPPS\pmdata.mdb"

Set objRecordset = Server.CreateObject ("ADODB.Recordset")

objRecordset.Open "mstJobs", strConnect, adOpenStatic,
adLockOptimistic, adCmdTable
I need to pull the JobName from another table in which both tables have
the JobNumber in common.

I am not sure how what the SQL syntax for doing this would be...

Any help is appreciated.

Thanks

You need to use a JOIN. The easiest way to learn this syntax is to do
create some queries linking the two tables in Access, then looking at
the resulting SQL. You haven't given any field or table names in your
post, so here's an example:

tblJobDetails
ID
JobNumber
JobDate
JobManager

tblJob
JobNumber
JobName

SELECT tblJobDetails.ID, tblJobDetails.JobDate,
tblJobDetails.JobManager, tblJob.JobName FROM tblJobDetails INNER JOIN
tblJob ON tblJobDetails.JobNumber = tblJob.JobNumber.

By the way, you are using the old ODBC driver. You should switch to
the native OLEDB driver. See here:
http://www.aspfaq.com/show.asp?id=2126

--
Mike Brind

[Mike Brind]

mw******@caldrywall.com wrote:

Currently I have a query that brings all the information i need except
for one piece found in another table. My current query looks like:

strConnect = "Driver={Microsoft Access Driver (*.mdb)};
DBQ=\\CALSJ1\PMAPPS\pmdata.mdb"

Set objRecordset = Server.CreateObject ("ADODB.Recordset")

objRecordset.Open "mstJobs", strConnect, adOpenStatic,
adLockOptimistic, adCmdTable
I need to pull the JobName from another table in which both tables have
the JobNumber in common.

I am not sure how what the SQL syntax for doing this would be...

Any help is appreciated.

Thanks

You need to use a JOIN. The easiest way to learn this syntax is to do
create some queries linking the two tables in Access, then looking at
the resulting SQL. You haven't given any field or table names in your
post, so here's an example:

tblJobDetails
ID
JobNumber
JobDate
JobManager

tblJob
JobNumber
JobName

SELECT tblJobDetails.ID, tblJobDetails.JobDate,
tblJobDetails.JobManager, tblJob.JobName FROM tblJobDetails INNER JOIN
tblJob ON tblJobDetails.JobNumber = tblJob.JobNumber.

By the way, you are using the old ODBC driver. You should switch to
the native OLEDB driver. See here:
http://www.aspfaq.com/show.asp?id=2126

--
Mike Brind

[Mike Brind]

Mike Brind wrote:

mw******@caldrywall.com wrote:

Currently I have a query that brings all the information i need except
for one piece found in another table. My current query looks like:

strConnect = "Driver={Microsoft Access Driver (*.mdb)};
DBQ=\\CALSJ1\PMAPPS\pmdata.mdb"

Set objRecordset = Server.CreateObject ("ADODB.Recordset")

objRecordset.Open "mstJobs", strConnect, adOpenStatic,
adLockOptimistic, adCmdTable
I need to pull the JobName from another table in which both tables have
the JobNumber in common.

I am not sure how what the SQL syntax for doing this would be...

Any help is appreciated.

Thanks

You need to use a JOIN. The easiest way to learn this syntax is to do
create some queries linking the two tables in Access, then looking at
the resulting SQL. You haven't given any field or table names in your
post, so here's an example:

tblJobDetails
ID
JobNumber
JobDate
JobManager

tblJob
JobNumber
JobName

SELECT tblJobDetails.ID, tblJobDetails.JobDate,
tblJobDetails.JobManager, tblJob.JobName FROM tblJobDetails INNER JOIN
tblJob ON tblJobDetails.JobNumber = tblJob.JobNumber.

By the way, you are using the old ODBC driver. You should switch to
the native OLEDB driver. See here:
http://www.aspfaq.com/show.asp?id=2126

--
Mike Brind

Just like busses - you wait for ages for one to come along, then 3 come
along all at the same time....

*%^*^%$"^!! Google!

--
Mike Brind

[Matt]

Nice!!! Thanks for the advice