[RESOLVED] Calculate total number of tickets

Hi,

I wrote this code based off a school assignment I had last year which, was in Oracle. However, I can't figure out how to calculate the total number of tickets regardless of whether a ticket is added or subtracted.

vb Code:

Dim person_table As person_type
Dim staff_table As staff_type
Dim customer_table As customer_type
Dim address_table As address_type
Dim resavation_table As resavation_type
Dim station_table As station_type
Dim ticket_table(2) As ticket_type
Dim ticket() As Variant
 
Private Type person_type
personID As Integer
firstName As String
lastName As String
dob As Date
email As String
phone As String
End Type
 
Private Type staff_type
PTID As person_type
staffID As Long
password As String
End Type
 
Private Type customer_type
PTID As person_type
gender As String
title As String
End Type
 
Private Type address_type
addressID As Integer
street As String
suburb As String
postcode As String
cState As String
addressType As String
personREF As person_type
End Type
 
Private Type resavation_type
resavationID As Integer
customerID As String
resavataionDate  As Date
resavationStatus As String
carriageID As Integer
serviceID As Integer
addressType As String
dateofService As Date
customerREF As customer_type
End Type
 
Private Type station_type
stationID As Integer
stationName As String
ResavationRef As resavation_type
End Type
 
Private Type ticket_type
ticketID As Integer
travellerName As String
resavationID As Integer
stationStart As Integer
stationEnd As Integer
discountID As Integer
carriageID As Integer
seatID As Integer
carriageID1 As Integer
serviceID As Integer
dateofService As Date
stationRef As station_type
End Type
 
Private Sub Form_Load()
Dim tickets As String
person_table.personID = 1
person_table.firstName = "Bob"
person_table.lastName = "Smith"
person_table.dob = 12 - April - 1971
person_table.email = "bobsmith@optus.com"
person_table.phone = "86754345"
MsgBox (person_table.firstName & ", " & person_table.lastName & ", " & person_table.dob & ", " & person_table.email & ", " & person_table.phone)
staff_table.PTID.firstName = "Thomas"
staff_table.PTID.lastName = "Travis"
staff_table.PTID.dob = 22 - Feb - 1982
staff_table.PTID.phone = "22278889"
staff_table.staffID = 78656
staff_table.password = "tfrsewrd"
address_table.addressID = 1
address_table.street = "Wilson St"
address_table.suburb = "Prospect"
address_table.postcode = "5082"
address_table.cState = "SA"
address_table.addressType = "Unit"
address_table.personREF.personID = 1
resavation_table.resavationID = 1
resavation_table.customerID = 1
resavation_table.resavataionDate = 1 - Jan - 2000
resavation_table.resavationStatus = "T"
resavation_table.carriageID = 965
resavation_table.serviceID = 276
resavation_table.dateofService = 27 - July - 2011
resavation_table.customerREF.PTID.personID = 1
station_table.stationID = 1
station_table.stationName = "Adelaide"
station_table.ResavationRef.resavationID = 1
 
ticket = Array("0", "1", "2")
For i = LBound(ticket) To UBound(ticket)
ticket_table(ticket(0)).travellerName = "Bob Smith"
ticket_table(ticket(0)).resavationID = 297
ticket_table(ticket(0)).stationStart = 1
ticket_table(ticket(0)).stationEnd = 3
ticket_table(ticket(0)).discountID = 5
ticket_table(ticket(0)).carriageID = 27
ticket_table(ticket(0)).seatID = 1
ticket_table(ticket(0)).carriageID1 = 7
ticket_table(ticket(0)).dateofService = 5 - Dec - 2000
ticket_table(ticket(0)).stationRef.stationID = 1
ticket_table(0).ticketID = 1
ticket_table(ticket(1)).ticketID = 2
ticket_table(ticket(2)).ticketID = 3
tickets = (ticket_table(i).ticketID)
Next i
MsgBox ("Total number of tickets = " + tickets)
End Sub

This is the first time I have tried to use types in Visual Basic too.

Thanks,


Nightwalker

What exactly "total number of tickets" means? Isn't UBound(ticket) return the "total number of tickets"?

You should declare tickets as integer not as string and replace the line

vb Code:

tickets = (ticket_table(i).ticketID)

with

vb Code:

tickets += (ticket_table(i).ticketID)

The total number of tickets are suppose to represent the number sold.

I tried putting type ticket_type into an array but I didn't have much success. That is what those two lines,

vb Code:

Dim ticket_table(2) As ticket_type
Dim ticket() As Variant

are suppose to do.

If I change "tickets" datatype from string to integer I receive a typemismatch error on this line,

vb Code:

MsgBox ("Total number of tickets = " + tickets)

Quote:

---

Originally Posted by Nightwalker83

The total number of tickets are suppose to represent the number sold.

---

Declare a module level variable to keep track of sold tickets.

Quote:

---

Originally Posted by Nightwalker83

If I change "tickets" datatype from string to integer I receive a typemismatch error on this line,

vb Code:

MsgBox ("Total number of tickets = " + tickets)

---

I don't talk about tickets data type but about the variable declared at the beginning of Form_Load

Yeah, I used this

vb Code:

MsgBox ("Total number of tickets = " + CStr(tickets))

instead of casting tickets to string. That is casting to integer like you said.

The type mismatch is likely caused by you using the arithmetic plus (+) instead of the string operator concatenate (&). Just replace + with &.

Code:

---

    Dim person_table As person_type
    Dim staff_table As staff_type
    Dim customer_table As customer_type
    Dim address_table As address_type
    Dim resavation_table As resavation_type
    Dim station_table As station_type
    Dim ticket_table(2) As ticket_type
    Dim ticket() As Variant
   
    Private Type person_type
        personID As Integer
        firstName As String
        lastName As String
        dob As Date
        email As String
        phone As String
    End Type
   
    Private Type staff_type
        PTID As person_type
        staffID As Long
        password As String
    End Type
   
    Private Type customer_type
        PTID As person_type
        gender As String
        title As String
    End Type
   
    Private Type address_type
        addressID As Integer
        street As String
        suburb As String
        postcode As String
        cState As String
        addressType As String
        personREF As person_type
    End Type
   
    Private Type resavation_type
        resavationID As Integer
        customerID As String
        resavataionDate  As Date
        resavationStatus As String
        carriageID As Integer
        serviceID As Integer
        addressType As String
        dateofService As Date
        customerREF As customer_type
    End Type
   
    Private Type station_type
        stationID As Integer
        stationName As String
        ResavationRef As resavation_type
    End Type
   
    Private Type ticket_type
        ticketID As Integer
        travellerName As String
        resavationID As Integer
        stationStart As Integer
        stationEnd As Integer
        discountID As Integer
        carriageID As Integer
        seatID As Integer
        carriageID1 As Integer
        serviceID As Integer
        dateofService As Date
        stationRef As station_type
    End Type
   
    Private Tickets() As ticket_type
   
    Private Sub Form_Load()
   
    With person_table
        .personID = 1
        .firstName = "Bob"
        .lastName = "Smith"
        .dob = 12 - April - 1971
        .email = "bobsmith@optus.com"
        .phone = "86754345"
   
        MsgBox (.firstName & ", " & .lastName & ", " & .dob & ", " & .email & ", " & .phone)
    End With
   
    With staff_table
        .PTID.firstName = "Thomas"
        .PTID.lastName = "Travis"
        .PTID.dob = 22 - Feb - 1982
        .PTID.phone = "22278889"
        .staffID = 78656
        .password = "tfrsewrd"
    End With
   
    With address_table
        .addressID = 1
        .street = "Wilson St"
        .suburb = "Prospect"
        .postcode = "5082"
        .cState = "SA"
        .addressType = "Unit"
        .personREF.personID = 1
    End With
   
    With resavation_table
        .resavationID = 1
        .customerID = 1
        .resavataionDate = 1 - Jan - 2000
        .resavationStatus = "T"
        .carriageID = 965
        .serviceID = 276
        .dateofService = 27 - July - 2011
        .customerREF.PTID.personID = 1
    End With
       
    With station_table
        .stationID = 1
        .stationName = "Adelaide"
        .ResavationRef.resavationID = 1
    End With
   
    ReDim Preserve Tickets(3)
   
      With Tickets(1)
            .ticketID = 1
            .travellerName = "Bob Smith"
            .resavationID = 297
            .stationStart = 1
            .stationEnd = 3
            .discountID = 5
            .carriageID = 27
            .seatID = 1
            .carriageID1 = 7
            .dateofService = 5 - Dec - 2000
            .stationRef.stationID = 1
        End With
       
        With Tickets(2)
            .ticketID = 2
            'other data here
        End With
       
        With Tickets(3)
            .ticketID = 3
            'other data here
        End With
       
   
    MsgBox ("Total number of tickets = " & UBound(Tickets))
   
    End Sub

---

Assuming that the code you posted was intended to be illustrative, it should look more like the above. i.e. I have shown how to use an array of type 'ticket_type'. Of course, you wouldn't populate that array as I have illustrated. Rather, you would use a procedure or a function called 'create_ticket' or 'issue_ticket' and add each element there...

Thanks Colin! That looks like what I want to do although, there is still the problem of if a ticket hasn't been bought (set) the code would still return the size of the defined array rather than the count of the objects in the array.

For example:

Say ticket 1 had been bought and also ticket 3 but ticket 2 was left unsold at the moment

vb Code:

MsgBox ("Total number of tickets = " & UBound(Tickets))

the message box would display 3 instead of 2 because that what the size of the array was defined as rather than the number of objects actually in the array which is 2.

Night

I'm jumping in for the ride. Like you, I have not worked
with Types before either.

Let me focus on 2 segments of your code .. which I
excerpted from Colin's post.

Declarations

Code:

---

Dim station_table As station_type
Dim ticket_table(2) As ticket_type
Dim ticket() As Variant
'
Private Type station_type
    stationID As Integer
    stationName As String
    ResavationRef As resavation_type
End Type

---

Sub ... from Colin

Code:

---

Private Sub Form_Load()
    With station_table
        .stationID = 1
        .stationName = "Adelaide"
        .ResavationRef.resavationID = 1
    End With
    ReDim Preserve Tickets(3)
    With Tickets(1)
        .ticketID = 1
        .travellerName = "Bob Smith"
        .resavationID = 297
        .stationStart = 1
        .stationEnd = 3
        .discountID = 5
        .carriageID = 27
        ' ..... other data here
    End With     
    With Tickets(2)
        .ticketID = 2
        ' ..... other data here
    End With       
    With Tickets(3)
        .ticketID = 3
        ' ..... other data here
    End With     
    MsgBox ("Total number of tickets = " & UBound(Tickets))
End Sub

---

A few observations .. mainly questions as I'm new at this too

1. why is ticket_table(2) dim'd as an array? .. the other Types aren't.
2. why isn't there a Private Type ticket_table declaration?
3. where do Tickets(1), ..(2), ..(3) come from? .. not declared anywhere
4. to deal with your "unsold ticket 2" issue, you might need a loop

EDIT:

I thought I'd give your OP another look-see ...

Sub ... excerpted from your OP

Code:

---

Private Sub Form_Load()
    Dim tickets As String
    ticket = Array("0", "1", "2")
    For i = LBound(ticket) To UBound(ticket)
        ticket_table(ticket(0)).travellerName = "Bob Smith"
        ticket_table(ticket(0)).resavationID = 297
        ticket_table(ticket(0)).stationStart = 1
        ticket_table(ticket(0)).stationEnd = 3
        ticket_table(ticket(0)).discountID = 5
        ticket_table(ticket(0)).carriageID = 27
        ticket_table(ticket(0)).seatID = 1
        ticket_table(ticket(0)).carriageID1 = 7
        ticket_table(ticket(0)).dateofService = 5 - Dec - 2000
        ticket_table(ticket(0)).stationRef.stationID = 1
        ticket_table(0).ticketID = 1
        ticket_table(ticket(1)).ticketID = 2
        ticket_table(ticket(2)).ticketID = 3
        tickets = (ticket_table(i).ticketID)
    Next i
    MsgBox ("Total number of tickets = " + tickets)
End Sub

---

Most of my earlier questions remain.

Spoo

OK, my misunderstanding, there. I see from your earlier code that you will only populate traveller_name, reservation_id, etc once a ticket is sold, correct?

If so, you will need to loop through the tickets array identifying for yourself where that is the case and determine the total sold by via code. Obviously, if your data types were physically held in a database, you would do this via a query.

Quote:

---

Originally Posted by Spoo

Night


A few observations .. mainly questions as I'm new at this too

1. why is ticket_table(2) dim'd as an array? .. the other Types aren't.
2. why isn't there a Private Type ticket_table declaration?
3. where do Tickets(1), ..(2), ..(3) come from? .. not declared anywhere
4. to deal with your "unsold ticket 2" issue, you might need a loop

Spoo

---

Spoo, seems our posts crossed! To answer your quesions:

1- that code is not needed any more. I mistakenly forgot to delete that line and the one below, i.e.

Dim ticket_table(2) As ticket_type
Dim ticket() As Variant

2 The ticket_type 'table' is a data structure that holds information about a ticket. It is just a definition, nothing more. In code, we declare an array (in my example 'tickets', declared further down) that will hold a collection of tickets; i.e. each element of the array 'ticket' is of the type 'ticket_type'

3 They are declared (in my code example) in Form_Load. i.e. ReDim Preserve Tickets(3)

4 Yes, he'll need a loop as per his design

Cheers!

Colin

OK, thanks.

Yes, in the interim I took a look at Type statement in MSDN,
and now sort of get it .. they are "more detailed" kinds of variable names,
grouped per definition, and may also be arrays. Cool.

Spoo

Yeah, kinda like how a database table looks, in many ways. In fact you could read a record-set into an array (or collection, if memory serves) of a given data-type and then close the database if that were your desire...

Spoo,

The array is meant to hold the data for the ticket_type although, I can't figure out how to put ticket_type into an array. The lines of code I posted in post #3 can be removed from the code since they only have to do with finding the total number of tickets and not the types themselves.

Colin,

Yes, I know they need to go in a loop but I don't which object to put in?

Nightwalker,

Continuing with the database analogy, you could avoid using a loop if you have a 'Booking' data type that cross-references tickets to Customers.

i.e.

Code:

---

Private Type Booking
    CustomerID as Long
    TicketID as Long
End Type

---

Then you could use an array of Bookings, the Ubound of which would be tickets sold...

Night

Quote:

---

The array is meant to hold the data for the ticket_type although, I can't figure out how to put ticket_type into an array.

---

Maybe something like this ..

Declarations

Code:

---

Private Type ticket_table
    travellerName As String    ' "Bob Smith"
    resavationID As Integer    ' 297
    stationStart As Integer    ' 1
    stationEnd As Integer      ' 3
    discountID As Integer      ' 5
    carriageID As Integer      ' 27
    seatID As Integer          ' 1
    carriageID1 As Integer    ' 7
    dateofService As String    ' 5 - Dec - 2000
    stationRef As String      ' 1
End Type

---

Sub

Code:

---

' declare an array of user-defined Type
Dim Tickets(100) As ticket_table
' populate (1)
Tickets(1).travellerName =  "Bob Smith"
Tickets(1).resavationID = 297
Tickets(1).stationStart = 1
Tickets(1).stationEnd = 3
    < .. more .. >
' populate (2)
    < .. none .. >
' populate (3)
Tickets(3).travellerName =  "Nob Smith"
Tickets(3).resavationID = 299
Tickets(3).stationStart = 1
Tickets(3).stationEnd = 3
    < .. more .. >
' count tickets issued
nn = 0
For ii = 1 to 100
    If Tickets(ii).travellerName <> Empty Then
        nn = nn + 1
    End If
Next ii

---

Kind of crudely crafted, but I think it conveys the concept.

EDIT:

The key here the first line in the Sub .. declaring the array Tickets
as an array of a user-defined Type, namely, ticket_table. That then
facilitates counting via the loop.

Spoo

Yup, Spoo, that will do it. But having empty array elements causes unnecessary redundancy, hence the suggestion to have a bookings structure instead as per my earlier post...

Well, the image in the first post here shows all the tables that are in the whole system (although, not all of them were required for the assignment originally).

@ Colin,

See as a ticket would normally only be on a one to one basis I guess I would populate the booking type fields with the data that is already in the required fields from the other two types?

My example of a booking structure was only an illustration. If your data contains other inforamtion that relates one-to-one to a given booking then it should also be added to the booking type and removed from any other data-type


i.e.

Private Type Booking
Customer_Id as long
TicketID as Long
BookingDate as Long
CarriageID as Long/String/whatever
SeatNo as Long/String/whatever
Paid as Boolean
etc.
etc.
End Type

i.e. a Booking 'entry' only contains the information about that single relationship between a customer and the ticket he has purchased; no more, no less.

Forgive me if I have not bothered to check whether these are all valid as per your design, I'm just trying to equip you with the necessary knowledge to complete your task...

What? That stuff has nothing to do with I am trying to do. Besides I already have most of that in Ticket type. As far as I can tell all I need is say 1 person holds 1 ticket and calculate the total. I think it is assumed that the ticket has already been purchased. Although, I can't remember since it based off an assignment I did last year.

This is what the query would be if it were an Oracle database:

Code:

---

create or replace type body resavation_type is
  member function num_of_tickets return
  number is N number;
  begin
      select count(T.TicketID) into N from table_ticket T where
          deref(T.resavationID) = self;
      return N;
  end num_of_tickets;
end;
/

---

My question is instead of using a database to hold the information, could I use a variable and calculate the total from that, if so, how?

Doesn't Post #16 answer that ?

In Spoo's example you would, for instance, populate the 'Tickets' variable from your Tickets Table and run through a loop counting the entries where the ReservationID matches whatever you're looking for

Code:

---

For ii = 1 to 100
    If Tickets(ii).ReservationID = strSelf Then
        nn = nn + 1
    End If
Next ii
MsgBox "Number of Tickets with ReservationID = " & strSelf & " is " & CStr(nn)

---

@ Doogle,

Yes, that is exactly what I want! I forgot to test the code and mark this thread "Resolved" as now it has been.

Sorry, thought you were trying to design a system too. Hence my suggestion...

I mean, seriously, your database design seems very odd! What is a ticket? According to your design it looks like it's the availability of a seat on a given train from a Start Station to a Destination Station. But how can you say that a ticket is unsold for a Start-Station to End-Station journey when a customer may not want to start and end at your pre-defined points? Only the SOLD tickets would have start and end points, surely!!!! In other words, you cannot infer unsold tickets from that design. This is because a given seat can yield several tickets...

Never mind, you seem happy now anyway...

EDIT:

I just looked in detail at the database design in your link above. The ticket table in the database looks like it was intended to work exactly as I describe above. Because of that, you have no requirement that I can see to create data of type ticket_type where the traveller is not known and, therefore, the count of tickets sold should be the upper bound of the tickets array.

In other words, your database table called ticket is ONLY intended to store SOLD tickets, it seems. My suggestion for a Booking data type arose from an understanding that you needed to link data about available tickets to customers...

Well, I might have missed a table or two off that diagram I posted! I will ask my lecturer on Monday and see if he still has the solution to the assignment so I can find out whether there was a booking type or not.

No probs. The solution posted by Spoo will work if you intend to populate your ticket array with both sold and unsold tickets, although un-sold tickets is, as I say, a bit of a misnomer since a seat can be sold several times from the start to finish of a train's journey. At least, in real-life that is the case ;-)