473,320 Members | 2,027 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Selecting no records if condition is true

Hi - I have a numebr of rooms, which I am making available.

table: single - has single_id and single_name
rental_single has rental_id, single_id, check_in and check_out

They are linked by single_id.

I want to query so that when given two dates, it will return the
single_id that is free between two dates, which means I need to exclude
a singe_id if it has a linked entrey in the rental_single table, which
has a check_in and check_out date which iverlap with my form entries.

My query so far is:

SELECT DISTINCT single.single_id
FROM single INNER JOIN rental_single ON single.single_id =
rental_single.single_id
WHERE ((NOT ((rental_single.check_in) BETWEEN #1/1/2004# AND
#20/2/2004#))
AND
(NOT ((rental_single.check_out) BETWEEN #1/1/2004# AND #20/1/2004#)));
Trouble is, if I have an entry in the rental_single table with a check
in of #12/12/2004# and a check_out of #14/12/2004# and a checkin of
#2/1/2004# and #5/2/2004# it will still show that room linked as being
available - I want to exclude the single_id altogether, if ANY of the
entries in thje linked rental_single room fall within the two dates I am
looking for.

I'd really appreciate any pointers,

Thanks,

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 19 '05 #1
1 1337
It's not really an ASP question but a SQL one....
But

Select * from single where single_id not in
(Select single_id from rental where date......)

try that route...

--
Curt Christianson
Owner/Lead Developer, DF-Software
www.Darkfalz.com
"Mark" <an*******@devdex.com> wrote in message
news:u$**************@TK2MSFTNGP09.phx.gbl...
Hi - I have a numebr of rooms, which I am making available.

table: single - has single_id and single_name
rental_single has rental_id, single_id, check_in and check_out

They are linked by single_id.

I want to query so that when given two dates, it will return the
single_id that is free between two dates, which means I need to exclude
a singe_id if it has a linked entrey in the rental_single table, which
has a check_in and check_out date which iverlap with my form entries.

My query so far is:

SELECT DISTINCT single.single_id
FROM single INNER JOIN rental_single ON single.single_id =
rental_single.single_id
WHERE ((NOT ((rental_single.check_in) BETWEEN #1/1/2004# AND
#20/2/2004#))
AND
(NOT ((rental_single.check_out) BETWEEN #1/1/2004# AND #20/1/2004#)));
Trouble is, if I have an entry in the rental_single table with a check
in of #12/12/2004# and a check_out of #14/12/2004# and a checkin of
#2/1/2004# and #5/2/2004# it will still show that room linked as being
available - I want to exclude the single_id altogether, if ANY of the
entries in thje linked rental_single room fall within the two dates I am
looking for.

I'd really appreciate any pointers,

Thanks,

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Robert | last post by:
Hi All, I'm trying to solve this for a few days now and I just can't figure it out... I have three tables set up, I'll simplify them for this question: Table 1: HOTELS Columns: HOTEL_ID,...
3
by: Stewart | last post by:
Hi all! My (relatively small) database holds data on staff members and the projects (services) that they are assigned to. In my form frmStaff, I have a list of staff members - it is a...
1
by: Ramesh | last post by:
hi, I am selecting fields from three table for manupulating data and i want to display total number of records selected. But i am always getting -1 value, eventhough 1000 of records are selected....
3
by: M.L. Abram | last post by:
Hello all, I do not know if this question is regarding table design, queries, or programming. Below, I have given a table design using Access 2003. Fields 'Product' and 'Color' are primary keys...
2
by: ericv | last post by:
I have 3500 records in a table - each record has a unique value (KEY_ID field), but some records share the same value (in a field called POLE_ID) So, there may be 3 records that have the POLE_ID...
1
by: DonWolfi | last post by:
I am trying to get of our the database of our finance system all records where the the first field is the same and that either comply with condition a or condition b. Example: /<font...
3
by: John Fairhurst | last post by:
Hi, The following code should select the specified number of records randomly from the database <% .... query = "SELECT FROM " Set RS = Server.CreateObject("ADODB.Recordset")
1
by: RZ15 | last post by:
Hi, I have a form that opens a report. The form allows the user to pick a particular warehouse or supplier and an order to sort by. Here is the code for it: Private Sub cmdOK_Click() ...
5
by: megahurtz | last post by:
I need to put together an SQL statement and I can't think of how to make it work properly. The scenario is that I have news items in a database that have a launch time and can optionally have an...
6
jinalpatel
by: jinalpatel | last post by:
I am using following code for searching records. 'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter. 'Notes: 1. We tack " AND " on...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.