Getting Inner Join to work in a VBA-based query to MS SQL

Hi, I am having trouble getting SQL-data by using VBA.

Everything Works fine until I use the "inner Join"-sql command.

Basicly I want to exctract the newest updates from a employee table. The table would look something like this:

EmployeeNo	ChangeNo	EmploymentStatus	FromDate
1	10	Working	01.01.2014
2	11	Working	01.02.2014
2	12	Sick	01.03.2014
1	13	Quit	01.03.2014

The ChangeNo is a PK, and will increase for each New row in the table.

I want the Query to understand that the ChangeNo 12 and 13 is the largest, and then display EmployeeNo, ChangeNo, EmploymentStatus and FromDate for the row With ChangeNo 12 and 13.
To do this, I have tried to tweek an excelent macro by Carl.

Unfortunately, I am struggeling with the "inner join"-bit of my macro.

Please Login or Register  to view this content.

Where did I go wrong?

Best regards
Habang

Hi, Kyle123- My older brother is a Leeds United fan :-)

- I want the query to find the highest ChangeNo for each employee. In my example, I only included 2 employees, but in the real table, it would be about 400.

mc84excel
l - Thank you for your "foul" code ;-) It might be the solution, but I can't see how I can get the Max ChangeNo for every employe without importing it to Excel first.


I might of course use som sort of loop to go through all ChangeNo's, but the best solution would be if I could get the Innerjoin to work...

Still scratching my head, and I am woundering if OPENROWSET could be used to make this work.

Best regards
Habang.

Found it!

Somtimes it is so simple...

Problem was a forgotten Space after the SS at the end of this line.

wrong:' & "Inner join(select EmployeeNo, MAX(ChangeNo) ChangeNo from [--MyDatabase--].[--MySchema--].[--Table--] group by EmployeeNo) SS" _
right: ' & "Inner join(select EmployeeNo, MAX(ChangeNo) ChangeNo from [--MyDatabase--].[--MySchema--].[--Table--] group by EmployeeNo) SS " _

mc84excel - I'll look on the Join-issue on your post.
Kyle123 - I once ended up in a pub with som Irish Rugby-fans before an International between England and Ireland. It wasn't a happy sunday morning...

Best regards
Habang

Ah, always the little things! I use http://www.dpriver.com/pp/sqlformat.htm to convert to code. You can copy the sql from SSMS into the top box and select VB as the output and voila:

PHP Code: 

SELECT fld1, fld2,fld3
FROM G
Inner join j tbl2 on tbl2.key = g.key
where
tbl2.fld3 = 5 


Becomes:

PHP Code: 

varname1 = ""
varname1 = varname1 & "SELECT fld1, fld2,fld3 " & vbCrLf
varname1 = varname1 & "FROM G " & vbCrLf
varname1 = varname1 & "Inner join j tbl2 on tbl2.key = g.key " & vbCrLf
varname1 = varname1 & "where " & vbCrLf
varname1 = varname1 & "tbl2.fld3 = 5" 


Nifty eh?

Haha I'll bet, lived with a couple of Irish lads ar Uni and they will support anyone against England!!

Kyle123 - Very nifty :-) I tested the website, and it worked perfectly