R
Ronald
Hi All.
strSQLNoOfTimesOrdered = "SELECT [CustID], Count([CustID]) AS
[NoOfTimesOrdered] " & _
"FROM [Orders] " & _
"WHERE (([AffID]=" & lngAffID & ") AND
(Year([OrderDate])=2008)) " & _
"GROUP BY [CustID] " & _
"HAVING ([CustID] IN (SELECT DISTINCT [CustID]
FROM [Orders] WHERE (([AffID]=" & lngAffID & ") AND ([OrderDate] BETWEEN
#1/1/2008# AND #1/7/2008#))));"
Set qdf = .CreateQueryDef("qryNoOfTimesOrdered", strSQLNoOfTimesOrdered)
Set qdf = Nothing
.QueryDefs.Refresh
.Execute "SELECT [NoOfTimesOrdered], Count([NoOfTimesOrdered]) AS
[NoOfTimes] " & _
"INTO [tbl" & strAffName & "] " & _
"FROM [qryNoOfTimesOrdered] " & _
"GROUP BY [NoOfTimesOrdered];"
In strSQLNoOfTimesOrdered I group customers and count how many times they
have ordered over the year 2008 (only customers that have ordered in the
first week of january are counted).
Then in the Execute I group and count the number of times ordered so the
result will be the number of customers that ordered once, number of customers
that have ordered 2 times and so on. This works fine for 1 affiliate.
But now, to get a global view, I want it done for all affiliates. You should
think that the first query would look like this:
strSQLNoOfTimesOrdered = "SELECT [AffID], [CustID], Count([CustID]) AS
[NoOfTimesOrdered] " & _
"FROM [Orders] " & _
"WHERE (Year([OrderDate])=2008) " & _
"GROUP BY [AffID], [CustID] " & _
"HAVING ([CustID] IN (SELECT DISTINCT [CustID]
FROM [Orders] WHERE ([OrderDate] BETWEEN #1/1/2008# AND #1/7/2008#)));"
Additional grouping on AffID, but in the HAVING part there also should be a
grouping on AffID (CustID's may not be mixed). Of course this can be done by
a sepatate query like:
SELECT DISTINCT [AffID], [CustID] FROM [Orders] WHERE ([OrderDate] BETWEEN
#1/1/2008# AND #1/7/2008#)
but then the real question remains: how can I link AffID in the main query
part to the query in the HAVING so the affeliates are processed separately?
The Execute query will stay as it is.
Thanks in advance for your advice.
Ronald.
strSQLNoOfTimesOrdered = "SELECT [CustID], Count([CustID]) AS
[NoOfTimesOrdered] " & _
"FROM [Orders] " & _
"WHERE (([AffID]=" & lngAffID & ") AND
(Year([OrderDate])=2008)) " & _
"GROUP BY [CustID] " & _
"HAVING ([CustID] IN (SELECT DISTINCT [CustID]
FROM [Orders] WHERE (([AffID]=" & lngAffID & ") AND ([OrderDate] BETWEEN
#1/1/2008# AND #1/7/2008#))));"
Set qdf = .CreateQueryDef("qryNoOfTimesOrdered", strSQLNoOfTimesOrdered)
Set qdf = Nothing
.QueryDefs.Refresh
.Execute "SELECT [NoOfTimesOrdered], Count([NoOfTimesOrdered]) AS
[NoOfTimes] " & _
"INTO [tbl" & strAffName & "] " & _
"FROM [qryNoOfTimesOrdered] " & _
"GROUP BY [NoOfTimesOrdered];"
In strSQLNoOfTimesOrdered I group customers and count how many times they
have ordered over the year 2008 (only customers that have ordered in the
first week of january are counted).
Then in the Execute I group and count the number of times ordered so the
result will be the number of customers that ordered once, number of customers
that have ordered 2 times and so on. This works fine for 1 affiliate.
But now, to get a global view, I want it done for all affiliates. You should
think that the first query would look like this:
strSQLNoOfTimesOrdered = "SELECT [AffID], [CustID], Count([CustID]) AS
[NoOfTimesOrdered] " & _
"FROM [Orders] " & _
"WHERE (Year([OrderDate])=2008) " & _
"GROUP BY [AffID], [CustID] " & _
"HAVING ([CustID] IN (SELECT DISTINCT [CustID]
FROM [Orders] WHERE ([OrderDate] BETWEEN #1/1/2008# AND #1/7/2008#)));"
Additional grouping on AffID, but in the HAVING part there also should be a
grouping on AffID (CustID's may not be mixed). Of course this can be done by
a sepatate query like:
SELECT DISTINCT [AffID], [CustID] FROM [Orders] WHERE ([OrderDate] BETWEEN
#1/1/2008# AND #1/7/2008#)
but then the real question remains: how can I link AffID in the main query
part to the query in the HAVING so the affeliates are processed separately?
The Execute query will stay as it is.
Thanks in advance for your advice.
Ronald.