Counting occurences of dates over a whole table

[Ben_Zurich]

I have a problem how to count how many dates are corresponding with a
particular date (within +/- n days) over a WHOLE Table:

Here's the setup:

Table A
Column 1: has sequential Dates from e.g. 1/1/1900 to 1/1/2010
Columne 2: I want to count in this column 2 how many dates are in Table
B that are wihtin the brackets of the date of column 1 +/- n days.


Table B
about 1000 lines and 60 columns, each cell contains a particular date.

dates in no particular order.


Example Table A:

Col1 Col2
....
12/10/2006 Count how many dates there are in Table B
that are greater than 12/8/2006 and smaller than
12/12/2006
....


Has anybody an idea how this could be done ???

Also, it would be great if the "brackets" (i.e. +/- n days of the date
in Col1) could be defined as a parameter.


Any help would be highly appreciated.

 

[John Vinson]

I have a problem how to count how many dates are corresponding with a
particular date (within +/- n days) over a WHOLE Table:

Here's the setup:

Table A
Column 1: has sequential Dates from e.g. 1/1/1900 to 1/1/2010
Columne 2: I want to count in this column 2 how many dates are in Table
B that are wihtin the brackets of the date of column 1 +/- n days.

ok... but this count should almost certainly NOT be stored in any
table. It's redundant; if you're counting data that's already in a
table, *just count it as needed*. I'd only violate this rule if you
can be ASSURED that TableB is static and will never have any changed
or added dates.

Table B
about 1000 lines and 60 columns, each cell contains a particular date.
dates in no particular order.

Ow. This isn't a table. This is a spreadsheet! What's the meaning of
the columns? Is there any primary key?

Example Table A:

Col1 Col2
...
12/10/2006 Count how many dates there are in Table B
that are greater than 12/8/2006 and smaller than
12/12/2006
...


Has anybody an idea how this could be done ???

Also, it would be great if the "brackets" (i.e. +/- n days of the date
in Col1) could be defined as a parameter.

Don't know if this could be done in one pass - you'll probably get the
"Query Too Complex" error - but it may be worth a try. Create a UNION
query based on TableB. Assuming your fields are named Date1, Date2,
Date3, ..., Date60, it would involve going into the SQL window and
editing

SELECT Date1 AS TheDate FROM TableB WHERE Date1 IS NOT NULL
UNION ALL
SELECT Date2 FROM TableB WHERE Date2 IS NOT NULL
UNION ALL
<etc etc>
UNION ALL
SELECT Date60 FROM TableB WHERE Date60 IS NOT NULL;

You can leave off the WHERE Daten IS NOT NULL from each line if you
can be sure that all "cells" are in fact filled with dates.

This will turn your wide-flat... mess... into a tall-thin, 60000 row
recordset. Let's call it uniAllDates.

Then create a Query

SELECT TableA.Column1, Count(*)
FROM TableA INNER JOIN uniAllDates
WHERE uniAllDates.TheDate >= TableA.Column1 - [Enter N:]
AND uniAllDates.TheDate <= TableA.Column1 + [Enter N:]
GROUP BY TableA.Column1;

Don't expect this query to run lightning-quick... you might want to go
make a cup of tea or coffee while it's running!

John W. Vinson[MVP]

 

[Alessandro Baraldi]

Ben_Zurich ha scritto:

I have a problem how to count how many dates are corresponding with a
particular date (within +/- n days) over a WHOLE Table:

Here's the setup:

Table A
Column 1: has sequential Dates from e.g. 1/1/1900 to 1/1/2010
Columne 2: I want to count in this column 2 how many dates are in Table
B that are wihtin the brackets of the date of column 1 +/- n days.


Table B
about 1000 lines and 60 columns, each cell contains a particular date.

dates in no particular order.


Example Table A:

Col1 Col2
...
12/10/2006 Count how many dates there are in Table B
that are greater than 12/8/2006 and smaller than
12/12/2006
...


Has anybody an idea how this could be done ???

Also, it would be great if the "brackets" (i.e. +/- n days of the date
in Col1) could be defined as a parameter.


Any help would be highly appreciated.

You can Try with SubSelect....!

SELECT fldDate,
(SELECT COUNT(fldDate) FROM T1
WHERE fldDate Between fldDate-nDays AND fldDate+nDays)
FROM T1

If you define nDays as a parameter(like this [nDays:] you can open a
Query by code
passing your parameter:

Function GetRS(DaysNumber As integer) as DAO.Recordset
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set qdf = CurrentDb.QueryDefs("QryName")
qdf.Parameters![nDays:] = DaysNumber
Set rs = qdf.OpenRecordset
If rs.EOF And rs.BOF Then
Msgbox "No records present"
Exit Function
End IF
set GetRS=rs
rs.close
set rs=nothing
set qdf=nothing
End Function

I'm not sure about your question, my English is not so good,but may be
right...

@Alex

 

[Ben_Zurich]

folks, thanks a lot for your thought and ideas - I will try it !

actually, maybe I could also generate Table B - the date "spreadsheet"
- in a way so that I will have one long list of dates in the first
place instead of a matrix?

It is created from a list of dates (usually up to 1000 unique dates) to
each of which a maximum of 60 dates is added by a fixed algorithm.

Is it possible to generate this information as a long list in the first
place, appending those 60 new dates (from each date) into a new table ?


So I could save the first step you mentioned.

 

[John Vinson]

folks, thanks a lot for your thought and ideas - I will try it !

actually, maybe I could also generate Table B - the date "spreadsheet"
- in a way so that I will have one long list of dates in the first
place instead of a matrix?

It is created from a list of dates (usually up to 1000 unique dates) to
each of which a maximum of 60 dates is added by a fixed algorithm.

Is it possible to generate this information as a long list in the first
place, appending those 60 new dates (from each date) into a new table ?

Certainly. You don't say how the algorithm works, but it could
certainly be adapted to create one long table. If you have a "source"
date and a series of generated dates, then I'd suggest a two-field
table with those dates; just write one record per generated date.

John W. Vinson[MVP]