my asp code looks like...

[taval]

my asp code looks like :

-----------------------------------

set conntemp=server.createobject("adodb.connection&quot
conntemp.open myDSN

StrQuery = "SELECT f.CustId, max(fd.UploadDate)as LastDate into tempererer from FileDescription f, FileDescription fd, Customer where f.CustId = fd.CustId and f.FileID = fd.FileID Group by f.CustId Order By f.CustId"
srt = "Select tempererer.CustId,Customer.CustName,tempererer.LastDate from Customer, tempererer where Customer.CustID = Tempererer.CustId"

set allcust=conntemp.execute(StrQuery)
set allcust=conntemp.execute(srt)
allcust.movefirst

-----------------------------------

As you can see I have two queries executed on the recordeset, this creates a new table called tempererer and this creates problems when the query is run more then once, How would I write my SQL query to give me the same effect without creating a new table? I have posted this message before but never got a complete answer, so I'm sorry if you're reading this again. I was also wondering that I could delete the table after I have created it? How would I do this? Would/Could this create problems?

Grateful for any help thanks.

Taha

 

[Karl Blessing]

are you trying to figure out how to join the two queries or what?

 

[taval]

Dear Karl,

Yes, in a sense I want to combine the queries so that the need for creating a table is eliminated. So I can get the
CustId,CustName,LastDate while meeting the conditions required in StrQuery. Hope that helps, event though I'm a bit confused myself now, :].

Thanks.
Taha

 

[Karl Blessing]

first off I am kind of confused.

[tt]
set allcust=conntemp.execute(StrQuery)
set allcust=conntemp.execute(srt)
allcust.movefirst
[/tt]

by doing that above on the same recordset, you never use StrQuery, in fact you have opened StrQuery, but reassign allcust to srt, leaving StrQuery still hanging open in memory, this would likely create a memory leak after a while. (unless it is automatically destoryed when reassigned)

 

[taval]

Dear Karl,

I perform both queries (StrQuery and srt) on the same recordset (allcust). The first query (StrQuery) executed on the recordset creates a table arccording to the requirements stated in StrQuery string. I then run another query on the same recordset (allcust) to get the required details. My problem is that because I create a new table this causes problems, how would I be able to achieve the same results from the queries without creating a new db table. Hope that clears things up.

Thanks.
Taha

 

[Karl Blessing]

um, it still doesnt clear things up, both queries are Select statments, when you make the first call, you load the recordset into allcust, when you make the second call, you lose the recordset stored in allcust, replacing it with the newrecordset, you could just remove the first query, and you'd be getting the same result because you overwrite the first one.

unless I could be mistaken by the "into" portion of the string, I think this question is best placed in the SQL forum least someone there would have a better understanding at how to set up your SQL Queries.

 

[taval]

Dear karl,

The into part of the statement creates a new table and puts all the information gathered by the select into it. Then the second query (srt) uses the table to extract the information from the table. I was wondering if there was a way to not create the table but uses an ordinary SQL query to get the information that binds to all the conditions posed in the first SQL statement (StrQuery). Thank you for all your help, I think I might try going to an SQL forum.

Thanks again.
Taha