[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

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
0
alanbergin
Asked:
alanbergin
  • 3
  • 2
  • 2
1 Solution
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now