Access SQL error...

[cusimar9]

I'm trying to do something incredibly simple but I'm having no luck

I have 2 tables, an item table and a category table. They are connected by the field 'CategoryNo'. There are 'Description' fields in both tables.

I'm trying to do an SQL query and retrieve all the fields, but it won't let me specify the Description field in the Category table.

The error I get is "Item cannot be found in the collection corresponding to the requested name or ordinal. "

Here is my code:

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db1.mdb")
Set rsDatabase = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT Item.Description, Category.Description FROM Item LEFT JOIN Category ON Item.CategoryNo = Category.CategoryNo;"

rsDatabase.Open strSQL, adoCon

Do While not rsDatabase.EOF
Response.Write (rsDatabase("Category.Description")) ** Causes error
rsDatabase.MoveNext
Loop

[minority]

pure intrest whats the error?

What does a left join do again its totally escaped me is it joins two tables together and shows only one of columns for similar entries?

I think description is a reserved word (not sure) put [] around it

what is the reason for using a left join if you are just printing out all of the descriptions at the start?

[buntine]

It seems the query is working correctly. There is no need to prefix the item with he name of the table that houses it. Once it is in the RecordSet object, the tables name is trivial.

rsDatabase("Description")

Regards.

[cusimar9]

I've fixed it.

I just used the following in the Select statement:

"SELECT Item.Description AS Item_Description, Category.Description AS Category_Description..."

Which then allows me to retrieve both values without errors