Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

dBase query to return MAX date

Status
Not open for further replies.

dnevels

Technical User
Aug 1, 2014
3
US
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:

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?
 
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
 
Sorry I'm only fluent in II, III and III+. But from what I know of SQL, try:
Code:
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
 
Thanks for responding, but it still has a syntax error.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top