By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

Help with Joins/Query

P: 19
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.
Jul 23 '07 #1
Share this Question
Share on Google+
6 Replies


JKing
Expert 100+
P: 1,206
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tProspects INNER JOIN tContactslog ON tContactslog.CLECNumber = tProspects.CLECNumber
  3. WHERE CallResult <> 'not interested'
  4.  
Jul 23 '07 #2

Expert
P: 97
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tProspects.CLECNumber, CompanyName, calldate,callresult
  2. FROM tProspects INNER JOIN tContactslog ON tProspects.CLECNumber = tContactslog.CLECNumber
  3. 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.
Jul 23 '07 #3

P: 19
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.
Jul 23 '07 #4

Expert
P: 97
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.
Jul 23 '07 #5

P: 19
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)
Jul 23 '07 #6

Expert
P: 97
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.
Jul 23 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.