access_test

Public Sub CreateQueries()
    Dim db As Database
    Set db = CurrentDb

    On Error Resume Next

    ' Create qry_Company
    db.Execute "CREATE VIEW qry_Company AS " & _
        "SELECT Company_Name, Address FROM COMPANY;"

    ' Create qry_total_guest
    db.Execute "CREATE VIEW qry_total_guest AS " & _
        "SELECT COUNT(*) AS TotalGuests FROM GUEST;"

    ' Create qry_EarlyPlatinum
    db.Execute "CREATE VIEW qry_EarlyPlatinum AS " & _
        "SELECT c.Company_Name, " & _
        "g.First_Name & ' ' & g.Last_Name AS Guest_Name, " & _
        "g.Package_ID, p.Early_Bird, p.Final_Cost " & _
        "FROM (COMPANY c INNER JOIN GUEST g ON c.Company_ID = g.Company_ID) " & _
        "INNER JOIN PAYMENT p ON g.Guest_ID = p.Guest_ID " & _
        "WHERE p.Early_Bird = True AND g.Package_ID LIKE 'PTM*';"

    ' Create qry_international
    db.Execute "CREATE VIEW qry_international AS " & _
        "SELECT g.First_Name & ' ' & g.Last_Name AS Guest_Name, " & _
        "g.Package_ID, p.Early_Bird AS Status, " & _
        "g.Date_Of_Reservation, p.Final_Cost " & _
        "FROM GUEST g INNER JOIN PAYMENT p ON g.Guest_ID = p.Guest_ID " & _
        "WHERE g.Telephone LIKE '(+*';"

    Set db = Nothing
    MsgBox "Queries created successfully!", vbInformation
End Sub