Subquery problem

[Günter Brandstätter]

Hi all,

I have a problem resolving a sub query. Heres what I've got:
database Fairground
A table 'STANDS' with a stand number and a descrition
A table 'Stands allocated' with the standnumber, the year and the client.
both tables are in relation via standnumber.
A combobox which should show me the stands available.

What I am trying to figure out is a query which shows me all free stands for
a particular year. (Free stand is when no record exists in the 'Stands
allocated' table for the stand selected and the particular year.
What I am doing now in Code is to fill a recordset with
all the stands and then browse this recordset one by one to put a query on
each stand in the table 'stands allocated'. When the recordcount of the
query is 1 then I know that the stand is already in use. With this I am able
to fill my combobox.

What I want to know: Isn't there a SQL instruction for this issue. I am
thinking since two days about this but I can't see a solution. I tried
subqueries but my SQL knowledge isn't that good to be able to resolve this
problem on my own.

Could anyone help me please???

thanks in advance
Günter

 

[Allen Browne]

This example uses a subquery to select all stands that were not allocated in
2003. Hopefully you can add the ClientID bit from there.

SELECT StandID FROM Stands WHERE NOT EXISTS
( SELECT StandID FROM [Stands allocated]
WHERE (([Stands allocated].StandID = Stands.StandID)
AND ([Stands allocated].AllocationDate Between #1/1/2003# And
#12/31/2003#)) )

 

[Günter Brandstätter]

Thank you so much Allen, it took me three days now to find anything an you
did that in five minutes only.

Works great
I should start to learn in depth SQL now.
Günter

-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com

This example uses a subquery to select all stands that were not allocated in
2003. Hopefully you can add the ClientID bit from there.

SELECT StandID FROM Stands WHERE NOT EXISTS
( SELECT StandID FROM [Stands allocated]
WHERE (([Stands allocated].StandID = Stands.StandID)
AND ([Stands allocated].AllocationDate Between #1/1/2003# And
#12/31/2003#)) )

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a problem resolving a sub query. Heres what I've got:
database Fairground
A table 'STANDS' with a stand number and a descrition
A table 'Stands allocated' with the standnumber, the year and the client.
both tables are in relation via standnumber.
A combobox which should show me the stands available.

What I am trying to figure out is a query which shows me all free stands for
a particular year. (Free stand is when no record exists in the 'Stands
allocated' table for the stand selected and the particular year.
What I am doing now in Code is to fill a recordset with
all the stands and then browse this recordset one by one to put a query on
each stand in the table 'stands allocated'. When the recordcount of the
query is 1 then I know that the stand is already in use. With this I am

able
to fill my combobox.

What I want to know: Isn't there a SQL instruction for this issue. I am
thinking since two days about this but I can't see a solution. I tried
subqueries but my SQL knowledge isn't that good to be able to resolve this
problem on my own.