Simple SQL?

[Guest_imported]

I am a beginner at SQL and Access and am having a problem viewing data from my database.

I am using an .asp file on my website to view information from a Access 2000 database. The database has two tables.

Table 1 (details) has a primary key called user.
Table 2 (notes) has a primary key called noteid.

The relationship is set with table 1 field 'user' being linked to 'user' in table 2.

I can view information fine on the website if I use:

sqltemp="select * from details where USER='"
sqltemp=sqltemp & myname & "'"
' myname is a variable gained from a text box on webpage

but if I try to include any details from Table 2 (notes) then I get an SQL error stating that ‘The specified field 'USER' could refer to more than one table listed in the FROM clause of your SQL statement’.

sqltemp="select * from details,notes where USER='"
sqltemp=sqltemp & myname & "'"
' myname is a variable gained from a text box on webpage

Any ideas on where I am going wrong?

 

[ghubbell]

Hi Mark,
You'll have to prefix the "user" with the table name, 'cause you now have two "user" fields in your query. Maybe like:

sqltemp="select details.* from details,notes where details.USER='"
sqltemp=sqltemp & myname & "'"

Gord
ghubbell@total.net