Trouble getting query to select records based on datediff

[lawtonl]

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

 

[vbaInet]

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

 

[lawtonl]

Ok, I use a subquery to get results like:

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

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.

 

[vbaInet]

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

 

[lawtonl]

Apologies, I'm not explaining myself very well.

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

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

when summed will end up like:

[FONT=Calibri][SIZE=3]Campaign name                                    product_estrevenue                Date Difference[/SIZE][/FONT]
[FONT=Verdana][SIZE=3]StoreSafe Campaign              £5,759.00                   24[/SIZE][/FONT]
[B][FONT=Verdana][SIZE=3]Manchester Breakfast           £5,759.00                     2[/SIZE][/FONT][/B]

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_salesstagename
 
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_FilteredCampaign.campaignid) ON dbo_FilteredList.listid=dbo_FilteredCampaignItem.entityid) ON dbo_FilteredListMember.listid=dbo_FilteredList.listid) ON Suspects.Lid=dbo_FilteredListMember.entityid) ON Prospects.LID=Suspects.Lid) ON dbo_FilteredOpportunityProduct.opportunityid=Prospects.OPID
 
GROUP BY 
 
dbo_FilteredCampaign.name, 
Prospects.[PotentialCustomer], 
Prospects.OPID, 
dbo_FilteredCampaign.actualstart, 
dbo_FilteredOpportunityProduct.product_estrevenue, 
Prospects.[CreatedOn], 
dbo_FilteredOpportunityProduct.product_salesstagename
 
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];

 

[vbaInet]

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

 

[lawtonl]

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

 

[Brianwarnock]

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