Not sure what you are trying to do with that SQL statement. If I am not wrong, try this directly in your database first and if the result is what you expected, then use the same in your ASP code.
Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

I need to select Password, IsCurrent, Assigned, and ID from the password table. using the ID I need to link it with the Client_Contacts table and get the email addy's of all the people with the same id. Then I need to also link to the Contacts table and select the Company name using the ID. That is what the sql statement is the start of. Hence my question of two inner joins.

I don't really see what you mean using this directly in the DB. In access as a query SELECT P.[Assigned], P.[ID], P.[Password], CC.[CLIENT EMAIL]
FROM [Password] P, [CLIENT_CONTACTS] CC WHERE P.[ID] = CC.[CLIENT ID]
and P.[Assigned] = #4/16/2004# AND P.[IsCurrent] = -1

I get an error saying to enclose it in brackets. And I don't want to select a table to use for that being the criteria since the ASP page and SQL code don't select a specific table. So yeah, not sure what you mean here.

As per the Select statement that you posted here in your code, one could not make out what you are trying to do. And now when you explain what you needed, it looks like you are missing out some of the columns that are to be put in select list, and instead you were using that in where clause. So it is always better to explain what you require in the first place, else one would have to go through many hops.
In your previous post, it reads as "Executing on DB database directly:", so I don't think that should be something really hard to understand what it means.
I am not sure what you mean here.

Still I assume that you are looking for this. Since some of your field names are reserved words and use spaces inbetween, use []s wherever needed, I am not going to suggest on that.
Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Let me try to answer a few of your questions.

First: The select statement was something that someone else offered. Its an innerjoin between the password table and the client contacts table I believe. You said execute it directly in the DB so I used a query and put that info in under the criteria field since thats what this is, a query. Or to my knowledge it is.

Second: When I try to execute that as a query it gives me the error saying to use brackets. This is solved by putting the whole select statement between ().

Third: Yes, I know to use the [] and in my statement they are used. Will your statement work in ASP or is this for a Access test? Also, when I said in the criteria field in the query I meant like this. You have to select a field first in a query. Then a table and then the criteria at the bottom. I'm assuming you use the select statement as the criteria but if thats the case what goes in the field/table section since it is required.

Lastly this statement you posted: Select Password, IsCurrent, Assigned, ID, CLIENT EMAIL, COMPANY NAME
From PASSWORD, CLIENT_CONTACTS, CONTACTS
Where Password.Id = Client Id and Password.Id = Contacts.ID

This is different than an inner join, dunno if this even works in ASP but you're forgetting it needs to check if the IsCurrent is true and if the Assigned is that day. Also, how does it know that Assigned from the Password table instead of say...contacts?

Hope this makes more sense to you.

Hi there,

There seems to be a lot of confusion with this.

When it was asked to be executed directly on the DB, There is SQL view in your Access Query, which lets you just paste this query and see how that behaves on execution, based on its behaviour you can use that in your ASP code. I really dont understand what you mean by saying "Will your statement work in ASP or is this for a Access test?" anything that works in access would work from ASP too, provided it is properly formatted in ASP if any variables are involved, but there seems to be no variables in this, so there is no change needed in the query when you wanted to run this from ASP. Why you are asked to run that from ACCESS first is because, everytime you don't have to refresh your ASP page that results in some error and go for debugging. Instead, when a Query works in Access that should cause no problem when used with ASP. So, this is easiest and most common way of troubleshooting errors.

I would suggest you better go through some books on T-SQL select statements and its syntax. I think you are using Query by Example Grid in Access for constructing your select statement. But it can also be written using sql view there.

Yes this looks different from inner join, by seeing the statement, but this is the premitive way of approach before Joins were introduced. And I didn't forget about checking those fields, but thought your requirement has changed the way you explained about that in your previous post. Pls go above and read you previous post where you explained about what all fields you wanted and how that needs to be joined with other tables. You haven't said anything about IsCurrent, Assigned, etc... to be used in the where clause there.
Unless Assigned column exists in both the tables used in join, you dont have to use Table referencing. If that exists in multiple tables, then you can go ahead and use that table referencing there. That should always work for you.

Also you haven't used the CONTACTS table in your previous statement posted, so I am not sure if that has to be used in this same select query. If not you can formulate separate query based on this to do that. Try this.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Ah, I finally figured out what you ment. I never realized there was a SQL view and I was never taught it existed. I got the query working correctly thanks to you though I have a problem.

The query:
Select Password, IsCurrent, Assigned, ID,[CLENT_CONTACTS].[CONTACT EMAIL], [CLIENT NAME] From PASSWORD, CLIENT_CONTACTS, CLIENT Where Password.ID = [CLIENT_CONTACTS].[CLIENT ID] and Password.ID = CLIENT.[CLIENT ID] and Password.[Assigned] = #4/16/2004# AND Password.[IsCurrent] = -1

For some reason when I execute on my DB it doesn't display the email addresses it gets from [CLIENT_CONTACTS].[CONTACT EMAIL] When I hit execute it asks for an email address and if you leave it blank it shows everything but no emails. If you enter in an email address in the desired box it puts that address for every email instead of selecting it from the database. Any ideas?

In addition to the above post using this code, again listed:
sql = "Select Password, IsCurrent, Assigned, ID,[CLENT_CONTACTS].[CONTACT EMAIL], [CLIENT NAME] From [PASSWORD], [CLIENT_CONTACTS], [CLIENT] Where Password.ID = [CLIENT_CONTACTS].[CLIENT ID] and Password.ID = CLIENT.[CLIENT ID] and Password.[Assigned] = #4/16/2004# AND Password.[IsCurrent] = -1"

And then: rs.Open sql, cn

I get an error on line 27 which is the rs.Open line that says "No value given for one or more required parameters." I assume this is talking about the cn string. CN is used here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\inetpub\database\ftp.mdb;" & _
"Jet OLEDB:System Database=\\matrix\database\dent32\dent32.mdw" , _
"Hidden", "hidden"

So whats the parameter problem as well? It works fine directly in access.

Ignore the previous posts. I got it working. Yay.

Glad that it works. And can you tell me what that problem was finally and did you solve that?

Cheers!

_________________________
- Vijay G
Strive for Perfection

I'm not entirly sure what the problem about the missing varable was but as soon as I got the select statement working right in access and tossed it in it worked fine. I guess the wrong variable was in the select statement somehow. Statement that works.

"SELECT PASSWORD.Password, PASSWORD.IsCurrent, PASSWORD.Assigned, PASSWORD.ID, CLIENT_CONTACTS.[CONTACT EMAIL], CLIENT.[CLIENT NAME] " & _
          "FROM [PASSWORD], CLIENT_CONTACTS, CLIENT " & _
              "WHERE (((PASSWORD.IsCurrent)=-1) AND ((PASSWORD.Assigned)=#4/16/2004#) AND ((PASSWORD.ID)=[CLIENT_CONTACTS].[CLIENT ID] And (PASSWORD.ID)=[CLIENT].[CLIENT ID]));"