Help with Joins/Query

[Nestor01]

Hello all!
I've searched the forum for I am sure my answer lies within somewhere, but I was unable to apply what I found to solve my question.

I have two tables:

tProspects
-id (autonumber)
-CLECNumber
-CompanyName

tContactslog
-id (autonumber)
-CLECNumber
-callDate
-CallResult.

I cannot figure out a join issue. . The tProspects is a complete customer list. The tcontactslog has each type of contact made to the customer with the CLECListNumber as the id in common. When a customer is contacted, contact log record is created. There are multiple contact records for a Customer though not all Customers have been contacted. I need to do two things:

1) return a table (query result) which shows me all of the prospects in tprospects that do not have “not interested” as a call result in tContacts
2) after filtering out the ones that have "not interested" in CallResult, return a result that which includes the most recent (ONLY) callresult and Calldate from tcontacts, if any there is anything.

I am using MS Access 2007.

Thanks.

[JKing]

Hi, what do you consider as the most recent? Do you want to return any calls within the last 5 days? last day? Or just the single record with the most recent date?

To filter your results your query would be something like this:

SELECT *
FROM tProspects INNER JOIN tContactslog ON tContactslog.CLECNumber = tProspects.CLECNumber
WHERE CallResult <> 'not interested'
 

[kepston]

Hello all!
I've searched the forum for I am sure my answer lies within somewhere, but I was unable to apply what I found to solve my question.

I have two tables:

tProspects
-id (autonumber)
-CLECNumber
-CompanyName

tContactslog
-id (autonumber)
-CLECNumber
-callDate
-CallResult.

I cannot figure out a join issue. . The tProspects is a complete customer list. The tcontactslog has each type of contact made to the customer with the CLECListNumber as the id in common. When a customer is contacted, contact log record is created. There are multiple contact records for a Customer though not all Customers have been contacted. I need to do two things:

1) return a table (query result) which shows me all of the prospects in tprospects that do not have “not interested” as a call result in tContacts
2) after filtering out the ones that have "not interested" in CallResult, return a result that which includes the most recent (ONLY) callresult and Calldate from tcontacts, if any there is anything.

I am using MS Access 2007.

Thanks.

If I am understanding you correctly, try

SELECT tProspects.CLECNumber, CompanyName, calldate,callresult
FROM tProspects INNER JOIN tContactslog ON tProspects.CLECNumber = tContactslog.CLECNumber
WHERE callresult <>"not interested” AND calldate>=[Date From];

The [Date From] will prompt for a user input and then show all records from that date where an interest was shown.

Welcome to The Scripts.

[Nestor01]

Great!

I'm halfway home!

This worked to exclude those customers that have expressed "not interested from my list. (I added the "Ordered Service" as an additional criterion)

SELECT *
FROM tCLECProspects AS t1
WHERE (((Exists (select * from tContactLog where (callresult= 'Not Interested' AND tContactLog.CLECListNumber= t1.CLECListNumber) or callresult= 'Ordered Service' AND tContactLog.CLECListNumber= t1.CLECListNumber ))=False));

Now, I am trying to return the contents of the "CallResult" record, if exists of course as not all customers will have been contacted on any day. But, I need only the latest ONLY CallResultfield/record (from tContactLog) for each customer that has been previously contacted and has not been finalized as "not interested" or "ordered service" (e.g. still an active prospect) ).

These results populate a continuous form so our folks can see a list of prospects to contact and see the result LAST contact made with that contact.

[kepston]

Great!

I'm halfway home!

This worked to exclude those customers that have expressed "not interested from my list. (I added the "Ordered Service" as an additional criterion)

SELECT *
FROM tCLECProspects AS t1
WHERE (((Exists (select * from tContactLog where (callresult= 'Not Interested' AND tContactLog.CLECListNumber= t1.CLECListNumber) or callresult= 'Ordered Service' AND tContactLog.CLECListNumber= t1.CLECListNumber ))=False));

Now, I am trying to return the contents of the "CallResult" record, if exists of course as not all customers will have been contacted on any day. But, I need only the latest ONLY CallResultfield/record (from tContactLog) for each customer that has been previously contacted and has not been finalized as "not interested" or "ordered service" (e.g. still an active prospect) ).

These results populate a continuous form so our folks can see a list of prospects to contact and see the result LAST contact made with that contact.

You will need to use the Max() aggregate function on your date field, in the SELECT clause.
You may have to split into two queries.

[Nestor01]

You will need to use the Max() aggregate function on your date field, in the SELECT clause.
You may have to split into two queries.

I was thinking that. I tried Max() or Top 1 (using a sort on date) a couple of different ways, but ended up with the latest for the entire table and not the last/latest for each distinct number found (e.g. last of multiple contacts to each customer in table)

[kepston]

I was thinking that. I tried Max() or Top 1 (using a sort on date) a couple of different ways, but ended up with the latest for the entire table and not the last/latest for each distinct number found (e.g. last of multiple contacts to each customer in table)

You will have to select specific fields e.g. customer and Max(calldate) and GROUP BY customer.
That should get you closer.