I'm trying to measure ROI of marketing campaigns. I've gained access to our Dynamics CRM system via odbc as the reporting feature in Dynamics CRM is balls.

Essentially there are 4 tables:

Campaigns
Leads
Opportunities
Opportunity Products

In a nutshell when a lead has shown interest in a product it gets converted to an opportunity and opportunity products are added and detail the sales pipeline.

My quandry is that often campaigns dates can overlap and the next campaign can contain the same leads as the previous one. To allocate an opportunity to a campaign I've decided the best solution is to use the Datediff function to work out when an opportunity was created and which campaign it is closest to. However I can't seem to get Access to ignore the oldest campaign as shown below.



Campaign Name Potential Customer Sales Pipeline Date Difference
Campaign 2 Opportunity 1 £5,759.00 2
Campaign 1 Opportunity 1 £5,759.00 24


I want to end up with something like this:


Campaign Name Potential Customer Sales Pipeline Date Difference
Campaign 2 Opportunity 1 £5,759.00 2
Campaign 3 Opportunity 3 £7,989.00 5


I've tried using the Min function but haven't had any luck.

Any suggestions would be great.

tia

Not clear on how your desirable result is achieved. Could you show some sample records.

Ok, I use a subquery to get results like:


Campaign name Potential Customer actualstart product_estrevenue Created On Date Difference
StoreSafe Campaign 9 Starts Ltd 17-Nov-09 £3,367.00 11-Dec-09 24
Manchester Breakfast 9 Starts Ltd 09-Dec-09 £1,000.00 11-Dec-09 2
Manchester Breakfast 9 Starts Ltd 09-Dec-09 £1,392.00 11-Dec-09 2
Manchester Breakfast 9 Starts Ltd 09-Dec-09 £3,367.00 11-Dec-09 2
StoreSafe Campaign 9 Starts Ltd 17-Nov-09 £0.00 11-Dec-09 24
StoreSafe Campaign 9 Starts Ltd 17-Nov-09 £1,000.00 11-Dec-09 24
StoreSafe Campaign 9 Starts Ltd 17-Nov-09 £1,392.00 11-Dec-09 24
Manchester Breakfast 9 Starts Ltd 09-Dec-09 £0.00 11-Dec-09 2
StoreSafe Campaign Kirkby Lonsdale Society 17-Nov-09 £0.00 24-Nov-09 7
StoreSafe Campaign Kirkby Lonsdale Society 17-Nov-09 £14.00 24-Nov-09 7
Microsoft Campaign ABC 05-Oct-09 £610.00 13-Oct-09 8
Microsoft Campaign ABC 05-Oct-09 £1,250.00 13-Oct-09 8
Issue 4 - Prospect Version ABSOLUTE ROOFERS 28-Jan-10 £290.00 01-Feb-10 4
UC Expo Invite Advertiserbay 10-Feb-10 £24.00 15-Feb-10 5
Issue 4 - Prospect Version Advertiserbay 28-Jan-10 £24.00 15-Feb-10 18
Business Invitations Amici Procurement Solutions 17-Dec-09 £3,210.00 21-Dec-09 4
London Breakfast 8th Oct AnaCap Financial Partners 08-Oct-09 £5,000.00 15-Oct-09 7
Business Invitations Aperium Solutions 17-Dec-09 £671.00 22-Dec-09 5


From those results I have another query that sums on the product_estrevenue field.

Where there are records involved in multiple campaigns I only want it to select the result with lowest Date Difference value.

In the end I want:

Campaign name £Revenue

I just don't want duplicated currency values in the finaly query.

If you want to get rid of duplicates, go to the sql view and include either a DISTINCTROW predicate or a DISTINCT predicate.

SELECT DISTINCTROW... etc

SELECT DISTINCT... etc

See which one works for you. Here's a link that may help:
http://office.microsoft.com/en-us/access/HP010322051033.aspx

Apologies, I'm not explaining myself very well.

The results are not totally duplicates, the differ in a few ways:


ampaign name Potential Customer actualstart product_estrevenue Created On Date Difference
StoreSafe Campaign 9 Starts Ltd 17-Nov-09 £3,367.00 11-Dec-09 24
Manchester Breakfast 9 Starts Ltd 09-Dec-09 £1,000.00 11-Dec-09 2
Manchester Breakfast 9 Starts Ltd 09-Dec-09 £1,392.00 11-Dec-09 2
Manchester Breakfast 9 Starts Ltd 09-Dec-09 £3,367.00 11-Dec-09 2
StoreSafe Campaign 9 Starts Ltd 17-Nov-09 £0.00 11-Dec-09 24
StoreSafe Campaign 9 Starts Ltd 17-Nov-09 £1,000.00 11-Dec-09 24
StoreSafe Campaign 9 Starts Ltd 17-Nov-09 £1,392.00 11-Dec-09 24
Manchester Breakfast 9 Starts Ltd 09-Dec-09 £0.00 11-Dec-09 2


when summed will end up like:


Campaign name product_estrevenue Date Difference
StoreSafe Campaign £5,759.00 24
Manchester Breakfast £5,759.00 2


I want my query to select the enboldened row based on the fact that the date difference field is the smallest result out of the 2.

Here's my sql for the original query, I may well be missing something basic.


SELECT

dbo_FilteredCampaign.name,
Prospects.[PotentialCustomer],
Prospects.OPID,
dbo_FilteredCampaign.actualstart,
dbo_FilteredOpportunityProduct.product_estrevenue,
Prospects.[Created On],
DateDiff("d",[dbo_filteredcampaign]![actualstart],[
Prospects]![Createdon]) AS [Date Difference],
dbo_FilteredOpportunityProduct.product_salesstagen ame

FROM

dbo_FilteredOpportunityProduct INNER JOIN (Prospects INNER JOIN (Suspects INNER JOIN (dbo_FilteredListMember INNER JOIN (dbo_FilteredList INNER JOIN (dbo_FilteredCampaignItem INNER JOIN dbo_FilteredCampaign ON dbo_FilteredCampaignItem.campaignid=dbo_FilteredCa mpaign.campaignid) ON dbo_FilteredList.listid=dbo_FilteredCampaignItem.e ntityid) ON dbo_FilteredListMember.listid=dbo_FilteredList.lis tid) ON Suspects.Lid=dbo_FilteredListMember.entityid) ON Prospects.LID=Suspects.Lid) ON dbo_FilteredOpportunityProduct.opportunityid=Prosp ects.OPID

GROUP BY

dbo_FilteredCampaign.name,
Prospects.[PotentialCustomer],
Prospects.OPID,
dbo_FilteredCampaign.actualstart,
dbo_FilteredOpportunityProduct.product_estrevenue,
Prospects.[CreatedOn],
dbo_FilteredOpportunityProduct.product_salesstagen ame

HAVING

(((Prospects.[Created On]) Between DateAdd("d",28,dbo_Filteredcampaign!actualstart) And dbo_Filteredcampaign!actualstart) And ((DateDiff("d",dbo_filteredcampaign!actualstart,Prospects![Created on]))>=0))

ORDER BY Prospects.[Potential Customer];

Got you now :)

Use the DMin function to return only the min value. A criteria to this effect:

[Date Difference] = DMin("[Date Difference]", "Name_Of_Table", "[Campaign Name] = '" & [Campaign Name] & "'")

Don't use the aliases obviously. You know what the correct field names are ;)

Thanks vbaInet.

I tried what you suggested last night and it ground the query to a complete halt. So I've amended it this morning so that the sub query makes a table and the this query runs from the table.

When I run this though it still brings up 2 campaigns as shown below:


Campaign name product_estrevenue DminField
StoreSafe Campaign £5,759.00 24
Manchester Breakfast £5,759.00 2



I'm not sure why it's not doing what I want it to do!

Here's the sql on the query that runs from the made table from the sub query:


SELECT CampaignReports.name, Sum(CampaignReports.product_estrevenue) AS SumOfproduct_estrevenue, [DateDifference]=DMin("[campaignreports]![DateDifference]","CampaignReports","[Name] = '" & [name] & "'") AS Expr1
FROM CampaignReports
GROUP BY CampaignReports.name, [DateDifference]=DMin("[campaignreports]![DateDifference]","CampaignReports","[Name] = '" & [name] & "'")
ORDER BY [DateDifference]=DMin("[campaignreports]![DateDifference]","CampaignReports","[Name] = '" & [name] & "'");




Your help is greatly appreciated!

tia

I got confused as I read the thread as the requirements seemed to change slightly as post 1 seemed to want multiple answers 1per campaign name but later only one answer ignoring campaign name.

If wanting to find the data with the Min value( or max) in a particular field run a query with only Min of that field selected (or you can Group on another field to get Min within group)
Then a second query joins that query back to the data on field innerjoin MinofField( and also join on grouped field if that was selected) and extract the rest of the data.

Brian