Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78

    Unanswered: Help with SQL in Access 97

    I am trying to find the number of seats available on flights and then I want to display them in a listbox in access. At the moment I am displaying the seat availabity in a set of labels down the side of the list, not very elegant! What I want to do is display it along with the all the flight details i.e.

    FltNo From At Time To At Time Seats Available
    6754 LHR 0900 BHX 1000 32
    6756 BHX 1030 LHR 1130 22

    The tables I have are

    Bookings--------BookingDetails-------|
    |------------Flights

    I find the seats available by counting the bookings for the flight id and subtracting that from aircraft capacity using SQL. Can I build a sql query to do the lot in one and display the results for each flight on each row of my listbox?
    the light is on, someone is home, but they dont know they are. HELLO!

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Help with SQL in Access 97

    Originally posted by dapman2002
    I am trying to find the number of seats available on flights and then I want to display them in a listbox in access. At the moment I am displaying the seat availabity in a set of labels down the side of the list, not very elegant! What I want to do is display it along with the all the flight details i.e.

    FltNo From At Time To At Time Seats Available
    6754 LHR 0900 BHX 1000 32
    6756 BHX 1030 LHR 1130 22

    The tables I have are

    Bookings--------BookingDetails-------|
    |------------Flights

    I find the seats available by counting the bookings for the flight id and subtracting that from aircraft capacity using SQL. Can I build a sql query to do the lot in one and display the results for each flight on each row of my listbox?

    If you can create the query that contains the data that you want to display then you can either assign an SQL statement directly or use the query as the RowSource for the Listbox. Just set the RowSource Property to "Table/Query", the ColumnCount to the number of fields that you want to display, and the ColumWidth properties to the respective widths. If you want headings in the box set your Column Heads property to "Yes". The SQL statement can be built from the properties page of the listbox also. Click the build(...) button next to the RowSource property and the Query Design Grid will open where you can create what you want.
    Just close the query and save the RowSource but not the Query.

    Hope this helps.

    Gregg

  3. #3
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    Thanks for taking the time to reply, I know all that stuff its just the SQL I am strugling with. So any ideas??
    the light is on, someone is home, but they dont know they are. HELLO!

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by dapman2002
    Thanks for taking the time to reply, I know all that stuff its just the SQL I am strugling with. So any ideas??
    Sorry, I assumed too little. Could you provide a list of the fields in the tables and how the tables are linked? I could guess but would stand a better chance of success if I had them.

    Gregg

  5. #5
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    well the query is quite complex....

    the bones are; I use a hand written SQL call that calls upon other quries that are stored in access



    I have attached an .txt file with the details


    dap
    Attached Files Attached Files
    the light is on, someone is home, but they dont know they are. HELLO!

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by dapman2002
    well the query is quite complex....

    the bones are; I use a hand written SQL call that calls upon other quries that are stored in access



    I have attached an .txt file with the details


    dap
    I looked at the procedure that you are using. It's always a little tough to follow when it's not yours and you don't see the database. However, it seems that the creation of a temporary table might be a help. Use the SQL to populate the table initially and then add to a field in each row the inforation that you want using a couple of recordsets. This would be the available seats if I'm correct. You could then use the temp table to populate the listbox.

    I sometimes create a temporary table template and other times create it from scratch in code.

    Just a suggestion. If I'm off base let me know.

    Gregg

  7. #7
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    The temp table seems like a good idea. Do you know if it is quick and low on resources? I was hoping to just do it in SQL, im sure it must be possible but its beyond me at my level of knowledge. Thanks for your time on this.
    the light is on, someone is home, but they dont know they are. HELLO!

  8. #8
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by dapman2002
    The temp table seems like a good idea. Do you know if it is quick and low on resources? I was hoping to just do it in SQL, im sure it must be possible but its beyond me at my level of knowledge. Thanks for your time on this.
    Some of the forum members could probably recognize more quickly than I, the SQL you would need to accomplish your task.

    I do a bit of everything and usually pick what seems most practical. I'll look at it at home and see what I can do. Maybe, in the mean time, someone else will chime in with your solution. If you have any more info that you could perhaps Email, it would help.

    Gregg

  9. #9
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Help with SQL in Access 97

    Originally posted by dapman2002
    I am trying to find the number of seats available on flights and then I want to display them in a listbox in access. At the moment I am displaying the seat availabity in a set of labels down the side of the list, not very elegant! What I want to do is display it along with the all the flight details i.e.

    FltNo From At Time To At Time Seats Available
    6754 LHR 0900 BHX 1000 32
    6756 BHX 1030 LHR 1130 22

    The tables I have are

    Bookings--------BookingDetails-------|
    |------------Flights

    I find the seats available by counting the bookings for the flight id and subtracting that from aircraft capacity using SQL. Can I build a sql query to do the lot in one and display the results for each flight on each row of my listbox?
    I have had a look at your SQL you posted. You can probably do this in 2 queries. The first query does the counting, and the second query, based on the first, will add the extra columns. So you would start by calculating the seat availability in the first query by flight number. The second query would then add in the flight details (linked by the flight number).

    For display purposes a data sheet on a subform might be more flexible then a list box, but that is just a question of aesthetics!

Posting Permissions

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