for loop in a query / iterate over a date range

I have a query I need help with.. the explanation might be a bit long but I'd say it's reasonably simple to do!

I have a MSAccess .mdb file containing a table 'Enquiries', which contains two columns: 'ResidentDate' and 'ExResidentDate'.

I want to create a query that when passed a date range will loop through the range and for each date go through each entry in the table and count whether the test date falls between a client's ResidentDate and ExResidentDate.

Some pseudocode, as that's a bit of a long-winded sentence:

foreach date in range
{
   count = 0;
   foreach client in Enquiries
   {
      if(date >= client.ResidentDate AND date < client.ExResidentDate)
      {
         count++;
      }
   }
   [...pass date and count to output table...]
}

The output should look like this:

+------------+-----------------------------+
| Date        | # of Clients on that date |
+------------+-----------------------------+
| 01-Jan-04 | 76                                |
| 02-Jan-04 | 79                                |
| 03-Jan-04 | 78                                |
etc..

Any questions/clarifications, just ask!

Thanks!
Alan
alanberginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

alanberginAuthor Commented:
'client' basically means line. The Enquiries table looks like:

+------------------+-----------------+-------------------+------------------+
| Organisation    | ResidentDate  | ExResidentDate  | ContactDetails |
+------------------+-----------------+-------------------+------------------+
| Joe Bloggs Co.  | 04-Feb-04     | 04-Mar-04         | 52 New St.      |
| Sam Smith Co. | 15-Jan-04      | 28-Apr-04         | 51 Old St.        |
etc..
0
leeskelton83Commented:
Try :

SELECT Organization, Resident date, ExResidentDate, Contact Details , IIf([enter test date] >=ResidentDate And [enter test date]<=ExResidentDate,"Yes", "No) as BetweenValue
FROM tbl1

The Between Value field you will create with an IIf statment. It will return yes if the value is between and no otherwise.
0
shanesuebsahakarnCommented:
I rather think that you will need a second table containing all the dates that you are interested in for this to work. Access cannot fill in "blank spaces" in a dataset, so you can't say, for example "create a dataset containing all dates between these ranges" unless all of those dates already exist as records. Once you have a table with the dates that you want, it becomes easy:

SELECT MyDate, (SELECT Count(*) FROM Enquiries WHERE ResidentDate<=Date AND ExResidentDate>=MyDate) FROM MyDateTable
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

alanberginAuthor Commented:
shanesuebsahakarn,

I'm trying your idea, and have created a date table (just two sample dates for now), but whenever I try and run the query I get asked for paramters for ResidentDate and ExResidentDate. I've tried Enquiries.ResidentDate but it just asks me for a parameter for that. What do I do?
0
leeskelton83Commented:
Post the SQL.
0
alanberginAuthor Commented:
Oops, 'ResidentDate' is in fact 'Resident Date'. However when I run this:

SELECT MyDate, (SELECT Count(*) FROM Enquiries WHERE 'Resident Date'<=MyDate AND ExResidentDate>=MyDate)
FROM DateTable;

I get "Data type mismatch in criteria expression".
0
shanesuebsahakarnCommented:
Try:

SELECT MyDate, (SELECT Count(*) FROM Enquiries WHERE [Resident Date]<=MyDate AND ExResidentDate>=MyDate)
FROM DateTable;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.