Foreach loop in Access

[Echilon]

I have two tables, [Property] and [Booking]. What I need to do is select every ID in [Property], then move to [Booking] and select every booking which matches [Property].[ID]. I've been trying for hours but I just can't get it to work. This is what I have so far:

SELECT Property.[Property ID], Booking.[Visit Commencement Date]
FROM Property, Booking
WHERE Property.[Property ID] =
(SELECT DISTINCT Property.[Property ID] FROM Property);

But I can't work out how to select every row. I'm using MS Access 2007 to run the query.

 

[Golom]

First of all, your SQL needs some modification

SELECT Property.[Property ID], Booking.[Visit Commencement Date]
FROM Property, Booking
WHERE Property.[Property ID] [red]IN[/red] 
(SELECT DISTINCT Property.[Property ID] FROM Property);

When you use "=", SQL expects that you will specify a single value but your sub-query will return a list of values.

Second, you are using a cartesian join (i.e. FROM Property, Booking) and that causes every record in Property to be matched with every record in Booking.

Third, since you display only two fields here, it is unclear if there is a field (or fields) in the two tables that can be used to match records. I assume there must be but that is not apparent from your SQL.

Fourth, the construct

WHERE Property.[Property ID] [red]IN[/red] 
(SELECT DISTINCT Property.[Property ID] FROM Property);

Just says "... select records where Property.[Property ID] is one of the values for the same field in the same table ..." ... and by definition, of course it is.

 

[lespaul]

And I think you can achieve it with a join:

SELECT P.[Property ID], B.[Visit Commencement Date]
FROM Property As P
INNER JOIN Booking As B ON P.[Property ID] = B.[Property ID]

You may need this (it's hard to tell from your description):

SELECT P.[Property ID], B.[Visit Commencement Date]
FROM Property As P
LEFT OUTER JOIN JOIN Booking As B ON P.[Property ID] = B.[Property ID] 
WHERE B.[Property ID] is null

Anyway check out Understanding SQL Joins

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Echilon]

After a bit of tweaking, I ended up with:

SELECT Property.[Property ID],
Booking.[Visit Commencement Date]
FROM [Property] JOIN Booking
ON Property.[Property ID] = Booking.[Property ID]
WHERE Property.[Property ID] IN
(SELECT DISTINCT Property.[Property ID]
FROM Property);

Which works perfectly. Thanks for the help.

 

[PHV]

What happens if you remove your WHERE clause ?