ProNetGroup
MIS
Please Help!
I am running the latest version of Microsoft Visual FoxPro, and am having difficulties creating a SQL statement. I am trying to create a report that lists all of our contacts in the database, and includes the last call to that person, if one exists. Basically, I have a table of contact information, client information (company/address info), and calls. (Calls is a table that contains a log of who called this person and what they talked about, etc.) So right now, this is what I have:
SELECT calls.contactid, calls.isclient, calls.initials, calls.date, calls.memo, calls.clientid, clients.clientid, clients.address, clients.address2, clients.city, clients.state, clients.zip, contacts.id, contacts.lastname, contacts.firstname, contacts.jobtitle, contacts.workphone, contacts.workext, contacts.work2, contacts.cellphone, contacts.cell2, contacts.faxnumber, contacts.email, contacts.branch, contacts.division FROM calls, clients, contacts WHERE contacts.id = calls.contactid AND calls.clientid = clients.clientid GROUP BY calls.contact
This SQL works just fine, but the only thing is that the only contacts that I grab are the ones that actually have a call entered, when what we want is all contacts in the database, showing the last call entered if they do have calls. It occurred to me that because some contacts don't have calls, that it might produce some kind of error and not be possible at all, but I am just not sure. Does anyone have any suggestions for me???
Thanks so much for your help!!!
Eubanks
I am running the latest version of Microsoft Visual FoxPro, and am having difficulties creating a SQL statement. I am trying to create a report that lists all of our contacts in the database, and includes the last call to that person, if one exists. Basically, I have a table of contact information, client information (company/address info), and calls. (Calls is a table that contains a log of who called this person and what they talked about, etc.) So right now, this is what I have:
SELECT calls.contactid, calls.isclient, calls.initials, calls.date, calls.memo, calls.clientid, clients.clientid, clients.address, clients.address2, clients.city, clients.state, clients.zip, contacts.id, contacts.lastname, contacts.firstname, contacts.jobtitle, contacts.workphone, contacts.workext, contacts.work2, contacts.cellphone, contacts.cell2, contacts.faxnumber, contacts.email, contacts.branch, contacts.division FROM calls, clients, contacts WHERE contacts.id = calls.contactid AND calls.clientid = clients.clientid GROUP BY calls.contact
This SQL works just fine, but the only thing is that the only contacts that I grab are the ones that actually have a call entered, when what we want is all contacts in the database, showing the last call entered if they do have calls. It occurred to me that because some contacts don't have calls, that it might produce some kind of error and not be possible at all, but I am just not sure. Does anyone have any suggestions for me???
Thanks so much for your help!!!
Eubanks