how can i improve my SQL query in MS Access VBA

Question

how can improve my query so that only those records should be selected those are not selected yet in Pasenger_Detail table.

Private Sub BR_ID_LostFocus()
   Dim s As String

   s = "Select seat_no.seat_no" & _
       " FROM Seat_No" & _
       " Where seat_no.seat_no <=  " & _
       " (select br_info.Seats_Reserved from br_info where "  & _
       " Br_info.br_id=forms!pasenger_detail!br_id);"

  Me.Seat_No.RowSource = s
  Me.Seat_No.Requery
End Sub

can you submit table definitions?

4dmonster
– 4dmonster

2013-09-23 08:25:10 +00:00
Commented Sep 23, 2013 at 8:25 — 4dmonster
– 4dmonster, Commented Sep 23, 2013 at 8:25
i added pic of relationship might be it help u.

Rizwan Safdar
– Rizwan Safdar

2013-09-23 16:08:45 +00:00
Commented Sep 23, 2013 at 16:08 — Rizwan Safdar
– Rizwan Safdar, Commented Sep 23, 2013 at 16:08

Linger · Accepted Answer · 2014-02-07 15:09:51Z

2

You can use Not In in your WHERE clause like below. If it does not find the seat in the sub query then it will list seat_no in the results.

SELECT Seat_No.seat_no 
FROM Seat_No 
WHERE Seat_No.seat_no <= 
(
  SELECT br_info.Seats_Reserved 
  FROM br_info 
  WHERE br_info.br_id = forms!pasenger_detail!br_id
) 
AND Seat_No.seat_no NOT IN 
(
  SELECT pasenger_detail.seat_no 
  FROM pasenger_detail
)

edited Feb 7, 2014 at 15:09

answered Sep 23, 2013 at 12:37

Linger

15.1k23 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Rizwan Safdar Over a year ago

boss i did it earlier and was working. but problem with this is shows those seat nos which are not being selected yet. but it show all fields entries from seat _no table, but i need only till those which meet my maximum seat_no criteria.

Rizwan Safdar Over a year ago

in short i need both thing, first which is already working in this query also one you suggested.

Linger Over a year ago

You lost me at hello. Your English is difficult for me to follow. Can you explain what is being stored in what table and how they relate to each other? We obviously need more information.

Rizwan Safdar Over a year ago

i tried to improve it in this way but it does not work. "Select seat_no.seat_no" & _ " FROM Seat_No" & _ " Where seat_no.seat_no <= (select br_info.Seats_Reserved from br_info where Br_info.br_id=forms!pasenger_detail!br_id) NOT IN (select pasenger_detail.seat_no);"

Linger Over a year ago

That would not work. It is not a proper SQL statement. Maybe the following is what you are after:

SELECT Seat_No.seat_no FROM Seat_No WHERE Seat_No.seat_no <= (SELECT br_info.Seats_Reserved FROM br_info WHERE br_info.br_id = forms!pasenger_detail!br_id) AND Seat_No.seat_no NOT IN (SELECT pasenger_detail.seat_no FROM pasenger_detail)

|

Collectives™ on Stack Overflow

how can i improve my SQL query in MS Access VBA

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related