Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2005
    Location
    Louisville, KY
    Posts
    4

    Question Unanswered: Dilemma - Separate records derived from a range value within a field

    Database = Microsoft Access 2003

    Description: I have an arts organization database that contains a field with the patron's seat range for a performance. This seat range contains the part-of-house (orchestra, balcony, grand tier, etc) along with the row and seat numbers.

    For example, if say, patron John Public bought four tickets to a particular performance, the "seat range" field would look something like "Balcony B27:B30". If he only bought one ticket, the "seat range" field would read "Balcony B27:B27"

    Problem: We're exporting information from this database into another (SQL based) database that requires individual records for EACH SEAT and individual fields for the part-of-house and row. Hence, Mr Public's information above would need to go into the new database as;

    Balcony B 27
    Balcony B 28
    Balcony B 29
    Balcony B 30

    or....if a single ticket

    Balcony B 27

    How-to: Is this even possible with a query alone? How might I do this?? I hesitate to grade my query abilities and have little (okay-zero) VB experience (for module writing).

    Thanks much for your help.

  2. #2
    Join Date
    Jan 2005
    Posts
    68
    in your new table add a field for mr.public's id# (make sure you have one in the customer's table as well). then make a relationship between the two tables linking the two customer id# fields. if your new database is SQL server then it sould create the join for you. if not the SQL is as follows:

    SELECT customers.name, seating.poh, seating.row, seating.seat
    FROM customers INNER JOIN seating ON customers.id = seating.id;

    hope this helps.

  3. #3
    Join Date
    Feb 2005
    Location
    Louisville, KY
    Posts
    4

    Red face

    Thanks Frosty1

    My problem, however, is taking the one field (i.e. "Orchestra R34:R36") and creating three records with three fields;

    ...patron id... + "Orchestra" "R" "34"
    ...patron id... + "Orchestra" "R" "35"
    ...patron id... + "Orchestra" "R" "36"

    Thanks again.

  4. #4
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    This looks like fun. I am not certain this could be done via SQL (I am not the SQL expert so don't take my word for it.) I am going to see what I come up with now just because I am very curious.
    Darasen

  5. #5
    Join Date
    Feb 2005
    Location
    Blacksburg, Virginia
    Posts
    43
    Darasen,

    I know this can be done, but it's a little too complex for me to work on while I'm on the clock.

    The way I see it, you need to use VBA and SQL code to accomplish it. (I tend to over-program, so someone else may have a simpler solution).

    The way I'd tackle it though, is have a VBA function that manipulates two ADO Connections. One can just use CurrentProject.Connection as its connection string. The other would have to tap the external database's table, so you'll have to set up the connection manually.

    That's the tough part, 'cause you don't know what engine ohbrother is using (likely MS Jet 4.0 will work fine)

    Then you simply fill the first recordset with the table ohbrother is already using (Orchestra B37:39). Parse the data into some seperate string fields, and then call SQL to insert them into the external database.

    Will look something like this:
    Dim sql As String
    sql = "INSERT INTO [NameofExternalTable] ([Seating Poh], [Seating Row], [Seat Number]) VALUES (' " & seatingpohstring & " ', ' " & seatingrowstring & " ', ' " & seatnumberstring & " ')"
    ExternalConnection.Execute sql

    (It looks confusing because you have to put ' marks outside of VB strings in SQL. Take the spaces between ' and " marks inside the string) The actual string itself should look like this when populated:

    "INSERT INTO [NameofExternalTable] ([Seating Poh], [Seating Row], [Seat Number]) VALUES ('Orchestra', 'R', '37')"

    Do this for each value in the range and you should be good to go.

    I'm really interested in how this one turns out.

  6. #6
    Join Date
    Feb 2005
    Location
    Louisville, KY
    Posts
    4
    FWIW

    Microsoft Access 2003 (11.6355.6360) SP1
    MSJet v4.0.8618

  7. #7
    Join Date
    Feb 2005
    Location
    Blacksburg, Virginia
    Posts
    43
    ohbrother, I got this to work on my machine, hopefully it will at least steer you in the right direction

    First of all, I made two databases, each with a single table (I hope I recreated your scenario accurately).

    db1 contains the table "Multiple Tickets" that stores the range data. It only has two fields, "Patron ID" and "Seat Range". I populated John Public's information (Balcony B27:30) into it. Also, I created a simple form with the wizard that was strictly based on the table, and added a button to it called Export.

    I created a second database called ExternalDb which contains a table "Single Ticket" This table has the following fields:
    Ticket ID (AutoNumber)
    Parton Name (Text)
    Part of House (Text)
    Row (Text)
    Seat (Number)

    Then I created a module and created a function in which you can pass the PatronID and SeatRange.
    Here's the code for the module:

    Code:
    Option Explicit
    
    Public Function ExportTicket(PatronID As String, SeatRange As String)
    
        'field information variables
        Dim PartOfHouse As String
        Dim SeatRow As String
        Dim FirstSeat As Integer
        Dim LastSeat As Integer
        Dim SeatNumber As Integer
        
        'ADO related variables
        Dim sql As String
        Dim ExternalConnection As New ADODB.Connection
        
        'open ADO connection to external database
        ExternalConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=P:\DbForum\Range Value\ExternalDb.mdb"
        ExternalConnection.Open
        
        'get part of house from SeatRange
        PartOfHouse = Mid$(SeatRange, 1, InStr(1, SeatRange, " ") - 1)
            
        'get seat row from SeatRange
        SeatRow = Mid$(SeatRange, InStr(1, SeatRange, " ") + 1, 1)
        
        'get first seat number from SeatRange
        FirstSeat = CInt(Mid$(SeatRange, InStr(1, SeatRange, " ") + 2, 2))
        
        'get last seat number from SeatRange
        LastSeat = CInt(Mid$(SeatRange, InStr(1, SeatRange, ":") + 1, 2))
        
        For SeatNumber = FirstSeat To LastSeat
        
            sql = "INSERT INTO [Single Ticket] ([Patron Name], [Part of House], [Row], [Seat]) " _
                & "VALUES ('" & PatronID & "', '" _
                & PartOfHouse & "', '" _
                & SeatRow & "', '" _
                & SeatNumber & "')"
            Debug.Print sql
            ExternalConnection.Execute sql
            
        Next
        
    
    End Function
    Then I went into the form that I created and got the Export button properties. In the events tab, select OnClick, click the right most button (three dots) and choose Expression builder. Double click ExportTicket in the Functions folder and the function name with its parameters should appear. Replace the parameter names with the appropriate values in the <Field List> inside your form's folder.

    This works fine for me, but there are some things to be wary about. The string parsing in the code makes a lot of assumptions. It won't work if the seat numbers are not two digits, if there is a space in the Part of house, or if the Seat Row wraps to a new letter.

    This should be enough to get you moving forward though. Let me know if it blows up!

  8. #8
    Join Date
    Jan 2005
    Posts
    68
    i put something together, but the previous code is much cleaner. good luck.
    Last edited by Frosty1; 02-10-05 at 20:23.

  9. #9
    Join Date
    Feb 2005
    Location
    Louisville, KY
    Posts
    4
    Thanks Lint Diggy, I'll certainly give this a whirl.

    There are several incidents of single seat numbers and some incidents of double-letter row numbers (AA, ZZ, etc.) but I'll see what I can work thru on those. This is a great jump-start though. Far better than I had (and could have) come up with in a much shorter time.

    All the best to you!

  10. #10
    Join Date
    Feb 2005
    Location
    Blacksburg, Virginia
    Posts
    43
    So long as you get that ADO Connection object working, the rest should go fairly smoothly.

    There will be a lot of string manipulation depending on the complexity of your seat row/range possiblilities.

    There is a good description of all the built in string utilities for VB at
    HTML Code:
    http://www.officecomputertraining.com/vbtutorial/tutpages/page19.asp
    Happy coding!

Posting Permissions

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