dBase query to return MAX date

[dnevels]

I have some software that uses dBase for its database and I have virtually no experience with dBase. I am attempting to construct a report using fields from 3 tables (Customer, Service & History).

In all of the tables the ACCOUNT field is the same. The 'Customer' and the 'Service' table only have one one record for each Customer. The 'History' table has multiple records for each Customer.

I need to write a query so that only the record with the MAX date in 'History.BILLTHRU' is returned for each Customer. The code below returns all of the records for each Customer in the History table:

SELECT Customer.ACCOUNT, 
       Customer.FIRSTNAME, 
       (more fields...), 
       History.ACCOUNT, 
       History.BILLTHRU, 
       Service.ACCOUNT, 
       Service.OFFERCODE

FROM "C:\Customer.dbf" Customer

INNER JOIN "C:\History.dbf" History 
ON (Customer.ACCOUNT = History.ACCOUNT)

INNER JOIN "C:\Service.dbf" Service 
ON (Customer.ACCOUNT = Service.ACCOUNT) 

WHERE Customer.STATUS = "A" 
ORDER BY Customer.LAST_BUS_NAME

I got a suggestion from elsewhere to use a subquery like this:

INNER JOIN (SELECT ACCOUNT, MAX(BILLTHRU) AS BILLTHRU
            FROM "C:\History.dbf"
            GROUP BY ACCOUNT) History
ON (Customer.ACCOUNT = History.ACCOUNT)

But that code gives me a syntax error. Would someone be so kind as to assist me in solving this?

 

[LyndonOHRC]

What version of dbase are you using?

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[dnevels]

Oh Sorry! Version 4

 

[LyndonOHRC]

Sorry I'm only fluent in II, III and III+. But from what I know of SQL, try:

INNER JOIN (SELECT ACCOUNT, MAX(BILLTHRU) AS BILLTHRU
            FROM "C:\History.dbf" [COLOR=#EF2929]History[/color]
            GROUP BY ACCOUNT) 
ON (Customer.ACCOUNT = History.ACCOUNT)

Good Luck

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[dnevels]

Thanks for responding, but it still has a syntax error.