Hi,

I am working on a marketing database. I have two tables that are in use called tblListInfo and tblResults.

In tblListInfo the fields I am using are DropDate, ListName and Marketcode.
In tblResults the fields I am using are JoinDate and Marketcode.

I am attempting to write a query that the end result will display the number of records within the 1st week of the promotions drop date. For every combination where the ListName value AND the DropDate value are unique, I want to count the number of records in tblResults that have JoinDate BETWEEN DropDate AND DropDate + 6.

I tried writing the code for this in pieces starting with:

SELECT DISTINCT tblListInfo.[ListName], tblListInfo.[DropDate]
FROM tblResults
INNER JOIN tblListInfo
ON tblResults.[Marketcode] = tblListInfo.[Marketcode]

This gave me my first ideal part of the output I was seeking. I listing of all the possible marketing lists used with duplicate names occuring only when there were different drop dates. for example:

Listname Drop Date
List - 1 1/1/08
List - 1 1/15/08
List - 2 1/1/08
List - 2 2/5/08

From there I wanted to modify the process to include a third field that included the # of records in tblResults that have the JoinDate field BETWEEN tblListInfo.[Drop Date] AND (tblListInfo.[Drop Date]+6). The ideal output would be like this:

Listname Drop Date Week 1
List - 1 1/1/08 15
List - 1 1/15/08 8
List - 2 1/1/08 32
List - 2 2/5/08 12

My attempt at getting to this result is below:

SELECT DISTINCT tblListInfo.[drop date], tblListInfo.[ListName], count(tblResults.[join date]) AS [Week 1]
FROM tblResults
INNER JOIN tblListInfo
ON tblResults.[MarketCode] = tblListInfo.[MarketCode]
WHERE tblResults.[Join Date] BETWEEN tblListInfo.[drop date] AND (tblListInfo.[drop date]+6)

I receive an error at this point saying: "You tried to execute a query that does not include the specified expression 'ListName' as part of an aggregate function."

Can anyone offer suggestions/corrections to my approach and logic?

My attempt at getting to this result is below:

SELECT DISTINCT tblListInfo.[drop date], tblListInfo.[ListName], count(tblResults.[join date]) AS [Week 1]
FROM tblResults
INNER JOIN tblListInfo
ON tblResults.[MarketCode] = tblListInfo.[MarketCode]
WHERE tblResults.[Join Date] BETWEEN tblListInfo.[drop date] AND (tblListInfo.[drop date]+6)

I receive an error at this point saying: "You tried to execute a query that does not include the specified expression 'ListName' as part of an aggregate function."

Can anyone offer suggestions/corrections to my approach and logic?the answer to this can be found here:
http://www.access-programmers.co.uk/forums/showthread.php?t=135763

The same rules apply to INNER JOINS as well.

You can do this with nested queries. The first query selects the distinct records from tblListInfo (Q1). The second query joins that query with your results table and returns the results that meet the date criterion you mention (Q2). The third query does the counting:

SELECT Q2.DropDate, Q2.ListName, count(Q2.[joindate]) AS [Week 1]
FROM (SELECT Q1.DropDate, Q1.ListName, Q1.MarketCode, tblResults.JoinDate
FROM (SELECT DISTINCT tblListInfo.DropDate, tblListInfo.ListName, tblListInfo.MarketCode FROM tblListInfo) AS Q1 INNER JOIN tblResults ON Q1.MarketCode=tblResults.MarketCode
WHERE (((tblResults.JoinDate) Between [dropdate] And DateAdd("d",6,[dropdate])))) Q2
GROUP BY Q2.DropDate, Q2.ListName