Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question Unanswered: help with subquerying

    I'm new to dBForums (and relatively inexperienced with access programming) so please bear with me. I have a question and I'm hoping someone can help me out. I've got two queries, one which queries a table and the other which queries the first query. I'd like to embed the queries as SQL in the VBA that goes along with one of my forms. The trouble is I can't seem to get the queries setup as a query-subquery and am therefore struggling to get the two queries as one SQL statement. The two queries I have are as follows:

    qryShowAllInFirstRefNumGroup:

    SELECT tblSeat.Seat, tblMain.ID_Main
    FROM tblSeat RIGHT JOIN tblMain ON tblSeat.Seat = tblMain.Seat
    WHERE (((tblMain.RefNum)="205CBV609"));

    qryAllSeatsUnusedInFirstRefNumGroup:

    SELECT Min(tblSeat.ID_Seat)
    FROM qryShowAllInFirstRefNumGroup RIGHT JOIN tblSeat ON
    qryShowAllInFirstRefNumGroup.Seat=tblSeat.ID_Seat
    WHERE (((qryShowAllInFirstRefNumGroup.Seat) Is Null));

    I'd like to be able to pass the RefNum from a textbox on the form rather than have it hard coded as above (205CBV609). I'd also like to ultimately run a While ... Wend statement to perform an update query for all seats unused below a certain number for a given refnum with the lowest seat number from that same refnum above a certain number. That way I can take the person sitting in the lowest numbered seat above 13 and place them in the lowest empty seat below 14 or something like that. The only thing getting in my way right now is my stumbling getting these two SQL statements setup as one sql statement. Anyhow... I'm feeling desperate and would be grateful for any help! Thanks!
    Last edited by ArielZusya; 01-09-08 at 18:54.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can use expressions to refer to your form's text box (like forms!formname!textboxname) in your first query and you're away
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Thanks for your response. forgive my ignorance... but please tell me more. I know how to drop the value of my text box in to one expression or the other... like:

    Code:
    Dim stMySQL1 As String
    
    stMySQL1 = "SELECT tblSeat.Seat, tblMain.ID_Main " _
         & "FROM tblSeat RIGHT JOIN tblMain ON tblSeat.Seat = tblMain.Seat " _
         & "WHERE (((tblMain.RefNum)='" & Me.txtRefNum & "'));"
    but I'm curious how to combine the two sql statements (where the second is a query of the first) using the method you described. I wish it were as simple as:

    Code:
    Dim stMySQL1, stMySQL2 As String
    
    stMySQL1 = "SELECT tblSeat.Seat, tblMain.ID_Main " _
         & "FROM tblSeat RIGHT JOIN tblMain ON tblSeat.Seat = tblMain.Seat " _
         & "WHERE (((tblMain.RefNum)='" & Me.txtRefNum & "'));"
    
    stMySQL2 = "SELECT Min(tblSeat.ID_Seat) " _
         & "FROM " & stMySQL1 & " RIGHT JOIN tblSeat ON " _
         & stMySQL1 & ".Seat=tblSeat.ID_Seat " _
         & "WHERE (((" & stMySQL1 & ".Seat) Is Null));"
    But that doesn't work and returns all sorts of errors. With other subquerying I've done I was able to do the WHERE IN (SELECT tblBlahBlah.BlahBlah FROM tblBlahBlah WHERE Blahblahblah);" but I can't figure out how to do that here since I'm actually querying a query. Anyhow... your help is greatly appreciated. Thanks!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's not actually a sub query - it is a derived table. You need to enclose it in parentheses and give it an alias.

    I'm not sure what your query is doing either - what does it tell you?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A bit like:
    Code:
    SELECT    *
    FROM    
            (SELECT    *
            FROM    table_a) AS aliased_derived_table

  6. #6
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Wow... thanks for the quick response. So... my first query looks at tblMain and tblSeat and finds all the records in tblSeat which correspond to a record in tblMain which have RefNums equal to the passed RefNum (in my original post it was "206CBV609" and in my second post it was the value of Me.txtRefNum). The second query looks at that first query and finds the lowest seat number not used in that first query's results. In Case it's useful here's the datastructure:

    tblMain (holds the info about people attending a particular event)
    .ID_Main, AutoNumber, PrimaryKey
    .FirstName, text, First name of the person
    .LastName, text, Last Name of the person
    .Seat, Number, Linked to the list of seats in tblSeat
    .RefNum, text, the Reference number for a particular event

    tblSeat (Holds the seat numbers where people can sit)
    .ID_Seat, AutoNumber, PrimaryKey
    .Seat, Number, the actual seat numbers

    Ultimately, what I'd like to figure out is which seats in a given range (say seats < 14) are empty and then figure out which of those is the lowest and then figure out which seats above that range (seats > 13) are occupied and then take the person sitting in the lowest seat in that second range and move them into the lowest empty in that first range. The difficulty is that there are a number of events (ie different ref numbers) and so I can't just look at the unused seats in tblSeat I need to look at the list of seats used in tblMain for a given ref num and then compare that the the list of seats in tblSeat and see what tblSeat has that tblMain for a given refnum doesn't.

    I'd like to loop that until all the seats in that lower range are full. I think I can figure out the update queries (though I am still struggling a bit with the loop... would this be a when-wend statement?) I was thinking I should keep trying to figure this... what did you call it... derived table (I haven't learned this much this fast in a very long time... thank you for helping me... it sincerely does mean a tremendous amount to me!) issue out before I jump into trying to figure out the other stuff I'm struggling with. Hopefully that explained it... sometimes what I have clear in my head doesn't come out as clear when I go to try to describe it. Let me know if I need to take another stab at explaining all of this. Thanks so much for your help!

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - just to make sure I understand the natural English setup and problem.

    All the available seats are in tblSeat.
    tblMain holds information on bookings of these seats. If there is no entry in tblMain for a seat for a particular refnum then that seat is free for that event.

    You want to find the lowest free seat numbers in particular ranges. The intention is then to move people from higher numbered seats into these lower numbers.

    Correct so far?

    Do you need to consider groups of seats (e.g. if I book for myself and my family of four then you cannot move one of use - we must be moved to the first group of four consecutive seats)?

  8. #8
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    sounds like we're on the same page so far. To answer your question about groups of seats for this project, fortunately, no. Here's a possible real-world scenario of the project:

    There are 115 people brought in initially for RefNum 06CVB609 and 112 brought in for 05CBR226. Each group is assigned to seats for their refnum. They are seated sequentially in Seats 1-115 and 1-112 by the order their names appeared on a list (which is random). It is important to keep seats 1-13 filled at all times. If the first person removed from group 06CVB609 was removed from seat 7, then the person in seat 14 from 06CVB609 needs to be moved into seat 7 which leaves seat 14 empty. If the next person to be removed from 06CVB609 was in seat 2 then the person in seat 15 needs to be moved into seat 2. However, I need to keep the seating seperate by refnum... ie I don't want the fact that there is still a person in seat 2 and 7 from 05CBR226 when the two from 06CVB609 are removed to impact the process... I want to keep the processes seperate.

    The reason I have the seat numbers in tblSeat was just because I knew every refnum event would require a list of seat numbers... I assumed that was the way to do it but to be perfectly honest, I don't know enough to be confident so if you don't think that was the way to do it, I'm always eager to learn. Thanks so much for your help!

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok.
    I would dump the ID column from tblSeat. Make Seat the primary key. There are a few advocates for surrogate keys on this forum but I think even they would have a tough time justifying a surrogate number for a natural number key!
    Other than that everything is ok. If you will never ever add or remove any seats and do not record any information about each individual seat then you don't even need the seat table but probably best to keep it for now.

    Are your seat numbers always absolutely consecutive (every number between 1 and 300 for example, with no gaps) and if so will they always, always be? If so your SQL is much easier.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Are your seat numbers always absolutely consecutive (every number between 1 and 300 for example, with no gaps) and if so will they always, always be? If so your SQL is much easier.
    Actually, that is nonsense.

    First unnoccupied seat in range
    Code:
    SELECT    MIN(seat_no) AS min_seat
    FROM    tblSeat
    WHERE    seat_no BETWEEN 1 AND 13
        AND NOT EXISTS    (SELECT    NULL
                FROM    tblMain
                WHERE    refno = 'something'
                    AND tblSeat.seat_no = tblMain.seat_no)
    First occupied seat in a range
    Code:
    SELECT    MIN(seat_no) AS min_seat
    FROM    tblSeat
    WHERE    seat_no BETWEEN 14 AND 300
        AND EXISTS    (SELECT    NULL
                FROM    tblMain
                WHERE    refno = 'something'
                    AND tblSeat.seat_no = tblMain.seat_no)
    I wasn't too fussy about the coumn names - you might have to adjust them.

  11. #11
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    I was about to tell you that they are gapless... and then I saw your second post. Thank you so much. That's so cool... I'm about to go try it... I'll report back!

  12. #12
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Very cool... that worked perfectly. Ok... so... I have another question about using the SQLs in a loop but would you say it's better to post that question in a new thread or post it here? I'd like to be a good cybercitizen. *GRIN* Let me know... and thanks again for your help. I'm feeling like this is turning out to be a great day!

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this one is fixed - you are happy.

    new issue = new thread!

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •