Access query to return items free between two user input dates

[angel75]

Hi, I am setting up loans database with a table holding all the dates that
items are going in/out. I want to be able to ask user for the dates and
search the table for an item which is free between the two dates.

eg phone 2 is booked on 1/11, so when I enter 1/11 as start and end date I
want it to bring back phone 1/3/4. It is excluding the one entry where phone
2 is booked on that day, although it brings back all other entries for phone
2, and its also bringing back phone 1/3/4 future bookings, which is correct.

I am using Not Between StartDate and EndDate. What else do I need to do to
ensure if its checked phone 1 and its booked to exclude that phone
altogether? I am not good with SQL so want to keep it simple if possible.


SchedId ResourceId StartDate EndDate User Booked CheckedOut Completed
1 PHONE1 15-Jan-08 15-Jan-08 Sarah
yes
2 PHONE1 10-Sep-08 12-Sep-08 Vicki
yes
5 PHONE2 13-Oct-08 14-Oct-08 Fred
yes
4 PHONE2 17-Oct-08 17-Oct-08 John yes yes
no
3 PHONE1 20-Oct-08 20-Oct-08 Steve yes
no
15 PHONE2 01-Nov-08 16-Nov-08 Jack yes
no
11 PHONE2 19-Nov-08 25-Nov-08 Steve yes
no
7 PHONE1 22-Nov-08 22-Nov-08 John yes
no
17 PHONE1 01-Dec-08 01-Dec-08 Jan yes
no

Any help greatly appreciated!

 

[Ken Sheridan]

The following query is a perhaps a little more complex than you were hoping
for, but as Einstein said, things can be made as simple as possible, but no
more so. The query is in fact an adaptation of one I wrote some years ago
for handling room allocations, but in essence your situation is the same:

PARAMETERS [Enter Start Date:] DATETIME, [Enter End Date:] DATETIME;
SELECT Resources.ResourceID, C1.CalDate
FROM Resources, Calendar AS C1
WHERE C1.CalDate BETWEEN [Enter Start Date:] AND [Enter End Date:]
AND NOT EXISTS
(SELECT *
FROM Loans, Calendar AS C2
WHERE C2.CalDate >= StartDate
AND (C2.CalDate <= EndDate OR EndDate IS NULL)
AND C2.CalDate = C1.CalDate
AND Loans.ResourceID = Resources.ResourceID);

I've called your current table Loans in this example. You'll see that this
query includes two additional tables. Firstly a table Resources, which is
simply a list of all resources (phone1, phone2 etc in your example) with a
primary key ResourceID referenced by the Loans table. Irrespective of your
current problem you need this table to be able to enforce referential
integrity as the Loans table on its own otherwise contains redundancy and is
open to inconsistent data being entered.

Secondly the query includes a table Calendar with a column calDate of
date/time data type. This is no more than a table of all dates over a period
of time, e.g. 10 years, and is very easily created by serially filling a
column in Excel with dates and then importing the worksheet into Access as a
table. Once imported make the calDate column the primary key. What this
table does is provide the missing data, i.e. the individual dates, which are
not present in the current table.

One thing which the query does, which probably isn't actually necessary in
your case is cater for open ended bookings. In the original allocation
system it was possible for a room to be allocated from a start date, but with
no end date, i.e. it was considered allocated for all time after the start
date until and end date was entered. You don't need to amend the query,
however, as it will work fine when both start and end dates are always
present in any row.

One final point: its always a good idea with date/time parameters in
queries to declare them as I've done, as otherwise a date parameter entered
in short date format could be interpreted by Access as an arithmetical
expression rather than a date, and give the wrong results.

Ken Sheridan
Stafford, England

 

[John Spencer]

The simplest way I can think of is the following. Test it and see if it
gives your the desired results.

Parameters [Enter Begin Date] DateTime, [Enter End Date] DateTime;
SELECT Y.*
FROM YourTable AS Y
WHERE Not(Y.StartDate < = [Enter End Date]
And Y.EndDate >= [Enter End Date])


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

I realized AFTER I posted that the solution I proposed will only find
resources if they have been used at least once.

I think you could use the basic query as a subquery in a where clause
SELECT Y.*
FROM YourTable as Y
WHERE Y.ResourceID NOT IN
(
SELECT Y2.ResourceID
FROM YourTable AS Y2
WHERE Y2.StartDate < = [Enter End Date]
And Y2.EndDate >= [Enter End Date])

Or build an unmatched query using your table and a saved query that is
the where clause above.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The simplest way I can think of is the following. Test it and see if it
gives your the desired results.

Parameters [Enter Begin Date] DateTime, [Enter End Date] DateTime;
SELECT Y.*
FROM YourTable AS Y
WHERE Not(Y.StartDate < = [Enter End Date]
And Y.EndDate >= [Enter End Date])


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi, I am setting up loans database with a table holding all the dates
that items are going in/out. I want to be able to ask user for the
dates and search the table for an item which is free between the two
dates.

eg phone 2 is booked on 1/11, so when I enter 1/11 as start and end
date I want it to bring back phone 1/3/4. It is excluding the one
entry where phone 2 is booked on that day, although it brings back all
other entries for phone 2, and its also bringing back phone 1/3/4
future bookings, which is correct.

I am using Not Between StartDate and EndDate. What else do I need to
do to ensure if its checked phone 1 and its booked to exclude that
phone altogether? I am not good with SQL so want to keep it simple if
possible.


SchedId ResourceId StartDate EndDate User Booked
CheckedOut Completed
1 PHONE1 15-Jan-08 15-Jan-08
Sarah yes
2 PHONE1 10-Sep-08 12-Sep-08
Vicki yes
5 PHONE2 13-Oct-08 14-Oct-08
Fred yes
4 PHONE2 17-Oct-08 17-Oct-08 John yes
yes no
3 PHONE1 20-Oct-08 20-Oct-08 Steve
yes no
15 PHONE2 01-Nov-08 16-Nov-08 Jack
yes no
11 PHONE2 19-Nov-08 25-Nov-08 Steve
yes no
7 PHONE1 22-Nov-08 22-Nov-08 John
yes no
17 PHONE1 01-Dec-08 01-Dec-08 Jan
yes no

Any help greatly appreciated!