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:
I got a suggestion from elsewhere to use a subquery like this:
But that code gives me a syntax error. Would someone be so kind as to assist me in solving this?
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:
Code:
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:
Code:
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?