Anyway, I'd change this:
fm_cost.fmc_terminal = f.fmc_vendor
with this:
fm_cost.fmc_terminal = f.fmc_terminal

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Sorry typo - I do have it correct. Still not getting results.

Any other help?

I have also just tried without success:

select fmc_terminal, fmc_vendor, fmc_prodlnk, fmc_date, fmc_time, fmc_cost
FROM fm_cost
WHERE fm_cost.fmc_date =
           (Select max(fmc_date) from fm_cost as f
             where fm_cost.fmc_terminal = f.fmc_terminal and
               fm_cost.fmc_vendor = f.fmc_vendor
              and fm_cost.fmc_prodlnk = f.fmc_prodlnk
              AND fm_cost.fmc_date = f.fmc_date
              AND fm_cost.fmc_time = f.fmc_time
       GROUP by f.fmc_terminal, f.fmc_vendor, f.fmc_prodlnk)
 

What about this ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

PHV:

This query gives me every record in the table - the join is not correct somewhere.

I can make this work using a temp table as follows:

SELECT fm_cost.fmc_terminal, fm_terminal.fmt_owner, fm_cost.fmc_vendor,
p_altname.pn_name, Max(fm_cost.fmc_date) as max_date, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
FROM fm_cost,fm_terminal,inv_header,p_altname
WHERE fm_cost.fmc_terminal = fm_terminal.fmt_code
AND fm_cost.fmc_prodlnk = inv_header.ivh_link
AND fm_cost.fmc_vendor = p_altname.pn_alt
GROUP BY fm_cost.fmc_terminal, fm_terminal.fmt_owner,
fm_cost.fmc_vendor, p_altname.pn_name, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
INTO TEMP t100;

select t100.fmc_terminal, t100.fmt_owner,
t100.fmc_vendor,t100.pn_name,t100.max_date,t100.fmc_prodlnk,
t100.ivh_product,t100.ivh_desc, fm_cost.fmc_time, fm_cost.fmc_cost
from t100,fm_cost
where t100.fmc_terminal = fm_cost.fmc_terminal
and t100.fmc_vendor = fm_cost.fmc_vendor
and t100.fmc_prodlnk = fm_cost.fmc_prodlnk
and t100.max_date = fm_cost.fmc_date
and t100.max_date >= (TODAY-31)
order by t100.fmc_terminal, t100.fmc_vendor, t100.fmc_prodlnk

However, I need to be able to run this and send results to Excel. I tried creating a stored procedure that inserts the records into the temp table first and then telling MSQuery to execute the stored procedure. It will not allow the creation of the temp table from the ODBC connection in MSQuery.

CREATE PROCEDURE fm_cost_maxdate ()
SELECT fm_cost.fmc_terminal, fm_terminal.fmt_owner, fm_cost.fmc_vendor,
p_altname.pn_name, Max(fm_cost.fmc_date) as max_date, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
FROM fm_cost,fm_terminal,inv_header,p_altname
WHERE fm_cost.fmc_terminal = fm_terminal.fmt_code
AND fm_cost.fmc_prodlnk = inv_header.ivh_link
AND fm_cost.fmc_vendor = p_altname.pn_alt
GROUP BY fm_cost.fmc_terminal, fm_terminal.fmt_owner,
fm_cost.fmc_vendor, p_altname.pn_name, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
INTO TEMP t100;
END PROCEDURE

EXECUTE PROCEDURE fm_cost_maxdate ();
select t100.fmc_terminal, t100.fmt_owner,
t100.fmc_vendor,t100.pn_name,t100.max_date,t100.fmc_prodlnk,
t100.ivh_product,t100.ivh_desc, fm_cost.fmc_time, fm_cost.fmc_cost
from t100,fm_cost
where t100.fmc_terminal = fm_cost.fmc_terminal
and t100.fmc_vendor = fm_cost.fmc_vendor
and t100.fmc_prodlnk = fm_cost.fmc_prodlnk
and t100.max_date = fm_cost.fmc_date
and t100.max_date >= (TODAY-31)
order by t100.fmc_terminal, t100.fmc_vendor, t100.fmc_prodlnk

This also works fine. However, when I try to run this from the windows client I get the message that it cannot add the table t100.

This really should not be this hard to accomplish.

This query actually does work. The problem was performance on the actual table. Table has over 260,000 records. I re-indexed the table, Updated Statistics on the table and then added the date filter to get only records in the last 10 days. This still takes about 5 minutes to run, but it was running for over an hour and dumping garbage results. So much better.

Thanks for the input

select f1.fmc_terminal, f1.fmc_vendor, f1.fmc_prodlnk,
f1.fmc_date, f1.fmc_time, f1.fmc_cost
FROM fm_cost as f1
WHERE f1.fmc_date =
(Select max(f2.fmc_date) from fm_cost as f2
where f2.fmc_terminal = f1.fmc_terminal
and f2.fmc_vendor = f1.fmc_vendor
and f2.fmc_prodlnk = f1.fmc_prodlnk
and f2.fmc_date >= (TODAY-10)
GROUP by f2.fmc_terminal, f2.fmc_vendor, f2.fmc_prodlnk)