Recordset with SQL strings

[Michael Conroy]

I need some help on the best way to get some numbers totaled for a particular
date range. I want to total the [CapitalGains] field for whatever date range
is selected on the form, a [Start] and [End] date. When I include the
transaction date in the Totals query, the totals are grouped by the date and
I get three rows of numbers for each [EmployeeID], when I really want one row
of all three months totaled.

So I went crazy with recordsets: rstData is created from an sql string that
gets me the records for that date range and other criteria. Then I am trying
to total those fields by referencing the recordset, like SELECT
Sum(rstData!CapGains) AS TotalCG FROM rstData and putting that in rstTotals.
Then I am using an outer join on the rstTotals and the Employees tax
information so I can get their work and residence tax states. So I guess I
have three questions.

1) Can I reference one recordset in an SQL string in order to make a second
recordset?

2) Is there a sub query that would allow me to total numbers for a specified
date range?

3) Is there a better way to do what I am trying to do?

As always any help would be greatly appreciated. Thank you.

 

[Ken Snell]

You can do this in a single query (if I'm understanding your question
correctly).

SELECT Sum(CapGains) AS Total, EmployeeID
FROM YourTable
WHERE DateField Between
#StartDateValue# And #EndDateValue#
GROUP BY EmployeeID;

As for question 1, the answer is no. You can open a second recordset from a
first recordset by using the .Filter property of a recordset, and then
opening the second recordset as a filtered subset of the first recordset by
using the .OpenRecordset method of the first recordset.