Using Distinct im my SQL String

[boes]

I have a table called Testdata from database1 with following columns : itemid, item, state (this can be OK or Not OK) and employeeID. I make an innerjoin between employeeID from this table and employeeID from an other table called Employee from database 2. From that table I want to see the employee_name. What I want to get is every item that is OK. Every Item can be more then once in the table so when an item is 2 times in the table and is 2 times OK I get it 2 times in my datagrid. So I want to use the SELECT DISTINCT method but then I get an error. My question is : Can I always use DISTINCT or isn't it possible to use this when I'm also using INNERJOIN? I'm asking this because in an other case I can use DISTINCT but then I don't use an INNERJOIN.

Or can't I use it because I'm working with 2 tables out of 2 different databases?

Can someone give me an answer on this, please?

 

PS at the moment the first database is in Acces. The second is already copied to SQLserver.

[Nerseus]

You can use DISTINCT pretty much anywhere, including inner and outer joins and you should be able to do it across databases. Can you post your SQL so we can take a look - much easier to debug it if I can see it.

 

Also, where is the query "at"? In access with a link table to SQL Server, or is there a remote server in SQL Server that points to Access (not even sure if this is possible)?

 

-nerseus

[boes]

Ok here is my SQL,

 

strSQL = "SELECT DISTINCT PLCTest.*, employee.id, employee.name, employee_1.id, employee_1.name " & _

"FROM employee AS employee_1 INNER JOIN (employee INNER JOIN PLCTest ON " & _

"employee.id = PLCTest.Verantwoordelijke) ON employee_1.id = PLCTest.Tester " & _

"WHERE ((PLCTest.Projectnr = '" & strProjectNr & "') AND " & _

"(PLCTEST.Status = 'OK') AND " & _

"(PLCTEST.Boardnr = " & GarantyBoardnr & ")) " & _

"ORDER BY PLCTest.IO"

 

When I take out DISTINCT it works, with DISTINCT in my string it doesn't.

 

PLCTest is a Acces table and employee is a table on SQL-server

 

Boes

[quwiltw]

EDITED to remove a bad guess:(

BTW, what does the error say?