Subquery problem

  • Thread starter Thread starter Günter Brandstätter
  • Start date Start date
G

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
 
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#)) )
 
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


Allen Browne said:
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.

Günter Brandstätter said:
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
Click to expand...
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.
Click to expand...
Click to expand...
 
Back
Top