Count total that occur the following week (1 Viewer)

[shacket]

tblSundays - dates that are all Sundays
tblDates - dates that occur any day of the week

I want to count the total number of dates in tblDates that occur any time during the week (Sun-Sat) of each Sunday in tblSundays.

Any ideas?

 

[seven11]

Have you tried this?

select count(*) from tblDates
where dates in (select dates from tblSundays)

 

[shacket]

That's backwards from what I need. That SQL would count all of the dates from tblDates that also exist in tblSundays. Another way to say what I need is to count how many dates occur in tblDates each week (Sun-Sat), linked up to tblSundays (indicating which week it is).

 

[Jon K]

If what you want is something like this:

Sunday ------ Saturday ---------- Week Total
6/2/02 ------ 6/8/02 ------------- 3
6/9/02 ------ 6/15/02 ------------ 0
6/16/02 ----- 6/22/02 ------------ 2

you'll need a Non-Equil-Join using the operator Between.

SELECT Sunday, Sunday+6 AS Saturday, count(AnyDay) AS [Week Total]
FROM tblSundays LEFT JOIN tblDates
ON (tblDates.AnyDay BETWEEN tblSundays.Sunday AND tblSundays.Sunday+6)
GROUP BY Sunday, Sunday+6;



[This message has been edited by Jon K (edited 06-10-2002).]

 

[shacket]

I solved it with DCount. The query takes each Sunday from tblSundays and then has this field:

NumOfDays: DCount("*", "tblDates", "[Date] Between #" & [tblSundays].[Date] & "# And #" & ([tblSundays].[Date] + 6) & "#")