Access 2002 - report record source on open event

[Justin Koivisto]

I am trying to create a report that displays a name of an advertising
source and count of the number of times it was hit between certain date
ranges. The data is split between two different databases: this access
db, and a remote MySQL server. The MySQL tables are linked in the access db.

What I have done so far is created an On Open event for a report that
does the following:

* prompt user for the date range
* get which sources were hit during this period
* for each hit source in the time period...
* count times hit from this database
* count times hit from remote database through the linked tables

Inside the looping of each hit source, I set variables to hold the
source_name and the count of hits that source got. These are the pieces
of data that I want displayed in the report as a row.

Is there a way that I can create some kind of data structure to hold
each "row" which I can then set as the reports record source? Could
somebody give me a little direction on how to do what I am looking for?

Any help is *greatly* appreciated! TIA

Below is the On Open method to help with clarification:

Private Sub Report_Open(Cancel As Integer)
Dim rslt As ADODB.Recordset, rslt2 As ADODB.Recordset
Dim sqlstr As String
Dim sdate As String, edate As String
Dim source_name As String, source_id As String
Dim count As String, numSources As Integer

'prompt user for the date range
sdate = Format$(CDate(InputBox("Enter the Start Date")),
"General Date")
edate = Format$(CDate(InputBox("Enter the End Date")),
"General Date")

'get which sources were hit during this period
sqlstr = "SELECT DISTINCT (contacts.how_found) AS source_id, "
+"sources.name AS source_name FROM sources RIGHT"
+" JOIN contacts ON sources.id = contacts.how_found"
+" WHERE (((contacts.submitted) Between #" + sdate
+ "# And #" + edate + "#));"
Set rslt = New ADODB.Recordset
Set rslt.ActiveConnection = CurrentProject.Connection
rslt.Open (sqlstr)
numSources = 0
Do Until rslt.EOF
'for each hit source in the time period...
source_name = rslt.Fields("source_name").Value
source_id = rslt.Fields("source_id").Value
numSources = numSources + 1

'count times hit from this database
sqlstr = "SELECT Count(contacts.id) AS numReqs FROM contacts "
+"LEFT JOIN sources ON contacts.how_found = "
+"sources.id WHERE (((contacts.submitted) Between #"
+ sdate + "# And #" + edate
+ "#) AND ((sources.id)=" + source_id + "));"
Set rslt2 = New ADODB.Recordset
Set rslt2.ActiveConnection = CurrentProject.Connection
rslt2.Open (sqlstr)
count = rslt2.Fields("numReqs").Value
rslt2.Close

'count times hit from remote database through the linked tables
sqlstr = "SELECT Count(itcvb_contacts.id) AS numReqs FROM "
+"itcvb_contacts LEFT JOIN itcvb_sources ON "
+"itcvb_contacts.how_found = itcvb_sources.id WHERE "
+"(((itcvb_contacts.submitted) Between #" + sdate
+"# And #" + edate + "#) AND ((itcvb_sources.id)="
+ source_id + "));"
rslt2.Open (sqlstr)
count = count + rslt2.Fields("numReqs").Value
rslt2.Close

Set rslt2 = Nothing
rslt.MoveNext

'I now know the number of hits for this source
'need a way to make a row in the report to display the
' source_name and count
'can I create a data structure to use as the report's
' data Record Source ??

Loop
rslt.Close
Set rslt = Nothing
End Sub
--
Justin Koivisto - sp**@koivi.com

[Justin Koivisto]

Heh.. managed to work out a query for this (sometimes I feel really stupid):

SELECT
DISTINCT sources.name,
(
SELECT
COUNT(contacts.id)
FROM contacts
WHERE
contacts.how_found=sources.id
AND contacts.submitted Between $sdate And $edate
) + (
SELECT
COUNT(it_contacts.id)
FROM it_contacts
WHERE
it_contacts.how_found=sources.id
AND it_contacts.submitted Between $sdate And $edate
) AS hits,
$sdate AS sdate,
$edate AS edate
FROM sources
RIGHT JOIN contacts
ON sources.id = contacts.how_found
WHERE
contacts.submitted Between $sdate And $edate;

--
Justin Koivisto - sp**@koivi.com