Circumvention of GROUP BY

[fboehlandt]

Hi everyone,
I have the following (normalized) table 'mytable' containing 4 fields:

[Fund]

 [Date] [Return]
FundA 1001 Date1 Return1
FundA 1001 Date2 Return2
FundA 1001 Date3 Return3
...
FundA 1001 DateN ReturnN
FundB 1002 Date1 Return1
FundB 1002 Date2 Return2
FundB 1002 Date3 Return3
...
FundB 1002 DateN ReturnN
[I]mutatis mutandi for every fund of the database[/I]
 
For every fund thereis the same number of observations (about 200 for all relevant dates). However, not all funds will have reported in a given month (although the series are usually continuous). For example, one fund may have reported from Date1 to DateN whereas the next one only reported from Date10 to Date50. I need a general query that isolates funds that report throughout a specified time interval. As an example: all funds that reported between Date10 and Date50 but not those that are lacking observations in between. One way to do this is to use some type of GROUP BY clause:
 
[CODE] 
WHERE mytable.MM_DD_YYYY>=#[COLOR=blue][B]Startdate[/B][/COLOR]# And mytable.MM_DD_YYYY<=#[COLOR=blue][B]Enddate[/B][/COLOR]#
GROUP BY mytable.Code
HAVING Count(mytable.return)=[COLOR=blue][B]N[/B][/COLOR];

Where Startdate and Enddate are dates and N is the expected number of observations between them. Because the information contained in 'mytable' is subjected to another INNER JOIN statement (which would require a separate query), I am looking for a way to circumvene the HAVING clause (happy to provide details as to why so) and necessity of two separate queries. A VBA script to prep the table above for any start/end date and query wanting to extract complete series only would also be appreciated. In a nutshell:

- Input is startdate and endate
- objective is to extract complete series only
- via a query not using the GROUP BY clause

Can anyone help please?

 

[fboehlandt]

Okay, changed the normalized table so that there are no gaps in the data (thus entry ommitted if no corresponding return value). Surely there must be a way now?

 

[Poppa Smurf]

You need something like this. Use to BETWEEN for the date range then on a field that should have an observation set the criteria to IS NOT NULL

I am not too sure if the following is correct, this gives you the dates between the start and end dates and displays the obervation fields in that date range that are not null. Note SOME FIELD is the field that should have an observation.

WHERE (((mytable.MM_DD_YYYY) BETWEEN #Startdate# And #Enddate#) AND ((mytable.SOME FIELD) Is Not Null))
GROUP BY mytable.Code;

 

[fboehlandt]

Yes, that would yield a table with no empty entries. Alas, that is not quite what I need. Also, it can be assumed that I have a table that is normalized and contains no data gaps (i.e. only dates with associated returns). The starting and ending dates come from an Excel Userfrom, are copied into the data import string (as part of the command text) of an OLE BB query and then used to retrieve the relevant datapoints from the Access database. This works fine, but also includes funds with incomplete perfromance history. Say I would like to exclude any funds that do not report in any of the months between specified starting and ending date, what would I have to do? Also note that the initial query also includes a INNER JOIN statement. Thus, in the context of having to use GROUP BY/HAVING count() I cannot combine this in one query. Consequently, I would have to run the GROUP BY query first and the INNER JOIN second. The INNER JOIN query (command text of the OLE DB query) now contains the dates from the Userform but the previous GROUP BY query would know nothing of it (thus requiring a separate query fro every starting/ending date). I know this is a lot to ask but does anybody have an idea how to bypass this problem?