Help with SQL statement 1

[woodrg]

I don't want to be guilty of duplicate posting, so if this is the incorrect forum for this question, please redirect me. I'm using Access 2002 and i'm building a form with data from linked SQL Server tables.

I'm trying to refine a query and has two tables with a parent/child type relationship. I want all of the master(parent) records, but only the most recent details(child) records. I've tried a couple of SQL statements that work fine in SQL Query Analyzer, but to do the exact same statements in the SQL View of my Access query builder returns a syntax error with only a portion of a word highlighted, as if it's possibly not really a syntax error, but reacting to something else.

Below is an example. Please Help!

SELECT termination_benefits_master.*, termination_benefits_details.*
FROM termination_benefits_master
INNER JOIN 
   termination_benefits_details ON termination_benefits_master.tbm_id = termination_benefits_details.tbm_id_fk
   AND tbd_id IN (SELECT MAX (termination_benefits_details.tbd_id)
   FROM termination_benefits_details 
   GROUP BY tbm_id_fk)
ORDER BY termination_benefits_master.primary_ssn

p.s. is there a tgml tag for tab?


Becky,
Ft. Rucker

 

[ByteMyzer]

How about:

SELECT termination_benefits_master.*, termination_benefits_details_1.*
FROM termination_benefits_master
INNER JOIN termination_benefits_details AS termination_benefits_details_1 
ON termination_benefits_master.tbm_id = termination_benefits_details_1.tbm_id_fk
WHERE termination_benefits_master.tbd_id=
   (SELECT MAX (termination_benefits_details_2.tbd_id)
   FROM termination_benefits_details AS termination_benefits_details_2
   WHERE termination_benefits_details_2.tbm_id_fk=termination_benefits_master.tbd_id;)
ORDER BY termination_benefits_master.primary_ssn;

 

[woodrg]

That did it! you got a few of the table/field names mixed up, but i understood the concept and was able to straighten it out.

Thanks so much for your help, it is greatly appreciated!

(sorry i'm just getting this, we were in the path of "Ivan")

Becky,
Ft. Rucker