I loves my problems...

Night_Zero · July 19th, 2004, 02:34 PM

Alright, first lets start with the code I have.

<%Option Explicit%>
<HTML>
<HEAD>
<TITLE>Testing</TITLE>
</HEAD>
<BODY>

<%
 dim cn, sql, rs, sql2
 cn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\inetpub\database\ftp.mdb"
 set rs = server.createobject("adodb.recordset")
 sql = "SELECT Password.Assigned, Password.ID, Password.Password " & _
 "FROM [Password] " & _
 "WHERE Password.Assigned = #4/16/2004# AND Password.IsCurrent = -1"

 response.write("[u]Assigned [u]ID [u]Password") & " " & " "

 rs.open sql, cn

 if not rs.eof then
 do while not rs.eof
 response.write rs("Assigned") & " " & _
 rs("ID") & " " & _
 rs("Password") & " "
 rs.movenext
 loop
 end if
 rs.close
 set rs = nothing

%>
</BODY>
</HTML>

Now lets move onto the problem. This works perfect it displays exactly how it should. Here's the deal though. This is from the password table. I need to use in this case the "ID" field and match it against two other tables however (Here's the problem) the fields are called "CLIENT ID" in those two tables. My two problems are this, how can I use a join which is what I assume I need if the two ID names don't match? Also, can I get info from these fields in the CLIENT and CLIENT CONTACTS tables as all the data I need is in fields with multiple words seperated by spaces? Hope you can understand this and get back to me soon. Thanks for all the hlep guys.

bluckcuck · July 19th, 2004, 02:45 PM

You can join the tables as long as there's some logical data relationship. It doesn't matter if the columns have the same name.

For example:

Code:

select table1.name, table2.userID, table3.password
from table1
  inner join table2 on (table1.ID = table2.userID and table2.status = 'A')
  inner join table3 on (table1.ID = table3.employeeID)
where ...

Notice that I included columns from the joined tables in the select.

Bruce Luckcuck
Director, Applications & Support Services
Wiley Publishing, Inc.

Night_Zero · July 19th, 2004, 02:55 PM

Yes but the tables are "CLIENT" and "CLIENT CONTACTS" and the fields are "CLIENT ID" "CLIENT EMAIL" etc. Notice the spaces. Does that make a difference or do you just ignore them?

Jonax · July 19th, 2004, 03:08 PM

Code:

sql = "SELECT Password.Assigned, Password.ID, Password.Password " & _
          "FROM [Password] P INNER JOIN [CLIENT CONTACTS] CC ON P.ID = CC.[CLIENT ID] " & _
          "WHERE Password.Assigned = #4/16/2004# AND Password.IsCurrent = -1"

bluckcuck · July 19th, 2004, 03:12 PM

For any table or column that have spaces in them, enclose them in braces.

Like this:

select [client contacts].[client email] from [client contacts] ...

I might also strongly suggest that if possible you rename your tables to eliminate the spaces from the table names and columns. If that's not possible, then the above should work.

Bruce Luckcuck
Director, Applications & Support Services
Wiley Publishing, Inc.

Night_Zero · July 19th, 2004, 05:00 PM

Yeah, life would be much easier if I had access to the database but alas, I don't. I'll try your suggestions tomorrow since work day is over. Woot!

happygv · July 19th, 2004, 11:46 PM

Hi Night_Zero,

Quote:

quote:My network admin says they can't change either of the names

As you said in your other post, I don't think your life would be easier in changing that. But I would suggest you remember that any reserved keywords or table/column names with space in it, should be WRAPPED around with [] to get that work. This is the secret, if you can't control changing their names to avoid spaces in it or follow the standard object-naming coventions.

Also, better request your network admin or the person who designed the database take a look at the following links to know about the fact of faster ways to developing professional database application, which can make others work too easier.
* Organize your database better...
* Implement standard naming conventions...
* Get the job done faster!

LESZYNSKI (HUNGARIAN) NAMING CONVENTION

Naming Conventions for Microsoft Access under the head Naming Database Objects

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection