optimise query 1

[taval]

strQuery = "SELECT f.CustId, min(f.UploadDate)as FirstDate, max(fd.UploadDate)as LastDate from FileDescription f, FileDescription fd, Customer where
f.CustId = fd.CustId and f.FileID = fd.FileID
and not(DateDiff(day, f.UploadDate, getdate()) > "& numDays & " )
Group by f.CustId Order By f.CustId"


StrQ = "SELECT CustName from Customer where CustID =" & rsbac("CustId&quot

I currently have two queries, strQuery firstly finds all the CustID's and dates that satisfy the conditions in that SQL string. The second query StrQ finds the CustName using the CustId found from the first query. My question is , is there a way of elminating the StrQ by enabling the first query to extract the CustName as well. I've tried puting CustName in the select part of the first query but I get errors.

Grateful for any help.
Thanks.

 

[foxdev]

You have the Customer table in your query, but you don't have it properly joined to one of the other tables. Try this:

[tt]strQuery = "SELECT f.CustId, min(f.UploadDate)as FirstDate, max(fd.UploadDate)as LastDate, custname from FileDescription f, FileDescription fd, Customer c where
f.CustId = fd.CustId and f.FileID = fd.FileID and c.custid = f.custid and not(DateDiff(day, f.UploadDate, getdate()) > "& numDays & " )
Group by f.CustId Order By f.CustId"
[/tt]

 

[taval]

Thank you Robert, you pointed me on the right track and I got the new query to work.

Taha

 

[PavoneRich]

I think if you make the first query a view and then make the second query a join with the view it will eliminate the error.