Table relationships

Discussion in 'Access Table Design' started by HENRY ONYEAGBAKO, Jan 16, 2004.

  1. Im currently creating a database that records my cinema visits. I have at
    present have split one of my tables tblcinema into two tables. The majority
    of information regarding cinema location, ticket price etc are held in tbl
    cinema and details regarding the screens and best seating position are held
    in another table. This was done to reflect that not all cinemas have the
    same number of screens thus preventing multiple fields to lay empty. However
    the problem I now face is what relationship should I define between the
    two tables.
     
    HENRY ONYEAGBAKO, Jan 16, 2004
    #1
    1. Advertisements

  2. HENRY ONYEAGBAKO

    Ken Snell Guest

    Step back and let's start again with your table design. Here's a suggestion:

    tblCinemas
    CinemaID (primary key)
    CinemaName
    CinemaAddress

    tblCinemaScreen
    CinemaID (composite primary key with ScreenID) (foreign key to
    tblCinemas)
    ScreenID (composite primary key with CinemaID)
    ScreenName
    BestSeat

    tblTickets
    tblTicketID (primary key)
    CinemaID (foreign key to tblCinemas)
    TicketPrice
    PriceDate

    This avoids "empty" fields (which would result from a nonnormalized table
    structure) and gives you flexibility for how many screens and even the
    effective date of the ticket prices.

    --
    Ken Snell
    <MS ACCESS MVP>

    "HENRY ONYEAGBAKO" <> wrote in message
    news:bu7dr8$g2n$...
    > Im currently creating a database that records my cinema visits. I have at
    > present have split one of my tables tblcinema into two tables. The

    majority
    > of information regarding cinema location, ticket price etc are held in tbl
    > cinema and details regarding the screens and best seating position are

    held
    > in another table. This was done to reflect that not all cinemas have the
    > same number of screens thus preventing multiple fields to lay empty.

    However
    > the problem I now face is what relationship should I define between the
    > two tables.
    >
    >
     
    Ken Snell, Jan 16, 2004
    #2
    1. Advertisements

  3. Henry,

    It seems to me that your Screens table should have a foreign key field
    to relate to the primary key field of the Cinemas table. For example
    a CinemaID or some such. That way, you will know which Cinema each
    Screen record "belongs" to.

    - Steve Schapel, Microsoft Access MVP


    On Fri, 16 Jan 2004 01:13:12 +0000 (UTC), "HENRY ONYEAGBAKO"
    <> wrote:

    >Im currently creating a database that records my cinema visits. I have at
    >present have split one of my tables tblcinema into two tables. The majority
    >of information regarding cinema location, ticket price etc are held in tbl
    >cinema and details regarding the screens and best seating position are held
    >in another table. This was done to reflect that not all cinemas have the
    >same number of screens thus preventing multiple fields to lay empty. However
    >the problem I now face is what relationship should I define between the
    >two tables.
    >
     
    Steve Schapel, Jan 16, 2004
    #3
  4. HENRY ONYEAGBAKO

    Guest

    On Friday, 16 January 2004 01:14:06 UTC, HENRY ONYEAGBAKO wrote:
    > Im currently creating a database that records my cinema visits. I have at
    > present have split one of my tables tblcinema into two tables. The majority
    > of information regarding cinema location, ticket price etc are held in tbl
    > cinema and details regarding the screens and best seating position are held
    > in another table. This was done to reflect that not all cinemas have the
    > same number of screens thus preventing multiple fields to lay empty. However
    > the problem I now face is what relationship should I define between the
    > two tables.


    Try this in your database:

    Sub TestListFilesInFolder(Optional SourceFolderName As String, Optional SkipToFolderPath As String)
    Workbooks.Add ' create a new workbook for the file list
    ' add headers
    With Range("A1")
    .Formula = "Folder contents:"
    .Font.Bold = True
    .Font.Size = 12
    End With
    'Range("A3").Formula = "File Name:"
    'Range("B3").Formula = "File Path:"
    'Range("C3").Formula = "File Size:"
    'Range("D3").Formula = "File Type:"
    'Range("E3").Formula = "Date Created:"
    'Range("F3").Formula = "Date Last Accessed:"
    'Range("G3").Formula = "Date Last Modified:"
    'Range("H3").Formula = "Attributes:"
    'Range("I3").Formula = "Short File Name:"

    With Range("A3:J3")
    .value = Array("File Name:", "File Path:", "File Size:", "File Type:", "Date Created:", "Date Last Accessed:", "Date Last Modified:", "Attributes:", "Short File Name:", "Last Accessed by:")
    .Interior.ColorIndex = 7
    .Font.Bold = True
    .Font.Size = 12
    End With

    'Range("A3:H3").Font.Bold = True
    'ListFilesInFolder "K:\Finance\AFTAB", True
    'Workbooks("Display all folder_sub_folder files_v2.xls").Worksheet("WD Start 2").Range("Select_Year").Value
    'Show_subs = Workbooks("Display all folder_sub_folder files_v2.xls").Worksheet("Sheet1").Range("Show_SubFolders").Value
    'Show_subs = Workbooks("Display all folder_sub_folder files_v2.xls").Worksheets("Sheet1").Range("Show_SubFolders").Value

    If SourceFolderName = "" Then
    SourceFolderName = BrowseForFolder
    End If

    ListFilesInFolder SourceFolderName, Workbooks("Display all folder_sub_folder files_v2.xls").Worksheets("Sheet1").Range("Show_SubFolders").value, SkipToFolderPath
    'ListFilesInFolder BrowseForFolder, True
    'ListFilesInFolder BrowseForFolder, False

    'Excel_Range.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal


    'ListFilesInFolder "C:\FolderName\", True
    ' list all files included subfolders
    End Sub

    Function IsArrayEmpty(a As Variant) As Boolean
    IsArrayEmpty = Len(Join(a, "")) = 0
    End Function

    Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean, Optional SkipToFolderPath As String)
    ' lists information about the files in SourceFolder
    ' example: ListFilesInFolder "C:\FolderName\", True
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim R As Long
    Dim x As Long, i As Long
    Dim wbNew As Workbook, wsNew As Worksheet
    Dim Files_Count As Long
    Dim Last_Author As Variant
    Dim Skip_Folder As Boolean

    Set wbNew = ActiveWorkbook
    'Set wsNew = wbNew.Sheets(1) 'set the worksheet
    'wsNew.Activate

    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    R = Range("A65536").End(xlUp).Row + 1

    'Split the skip string into constituent parts
    Dim arrSplitSkip() As String
    Dim boTestForSkip As Boolean
    Dim newSkipToFolderPath As String, SkipToFolderName As String

    If Not SkipToFolderPath = "" Then
    boTestForSkip = True
    arrSplitSkip = Split(SkipToFolderPath, "\")
    SkipToFolderName = arrSplitSkip(0)
    For i = 1 To UBound(arrSplitSkip)
    newSkipToFolderPath = newSkipToFolderPath & arrSplitSkip(i) & "\"
    Next i
    'Remove the last \
    If Len(newSkipToFolderPath) > 0 Then
    newSkipToFolderPath = Left(newSkipToFolderPath, Len(newSkipToFolderPath) - 1)
    End If
    Else
    boTestForSkip = False
    End If

    Files_Count = SourceFolder.Files.Count

    'If need to skip folder, do not iterate files in start folder
    If boTestForSkip = False Then
    For Each FileItem In SourceFolder.Files
    x = 1 'To SourceFolder.Files.Count 'for each file found, by the count (or index)
    i = x 'make the variable i = x
    If x > 60000 Then 'if there happens to be more than multipls of 60,000 files, then add a new sheet
    i = x - 60000 'set i to the right number for row placement below
    Set wsNew = wbNew.Sheets.Add(after:=Sheets(wsNew.Index))
    With Range("A3:J3")
    .value = Array("File Name:", "File Path:", "File Size:", "File Type:", "Date Created:", "Date Last Accessed:", "Date Last Modified:", "Attributes:", "Short File Name:", "Last Accessed by:")
    .Interior.ColorIndex = 7
    .Font.Bold = True
    .Font.Size = 12
    End With
    R = Range("A65536").End(xlUp).Row + 1
    End If
    ' display file properties
    'If Round(FileItem.Size / 1048576, 2) >= 100 Then
    If Round(FileItem.Size / 1048576, 2) >= 0 Then
    Cells(R, 1).Select
    Cells(R, 1).Formula = FileItem.name
    Cells(R, 2).Formula = GetDirectoryName(FileItem.Path)
    Cells(R, 3).Formula = Round(FileItem.Size / 1048576, 2) & " MB"
    Cells(R, 4).Formula = FileItem.Type
    Cells(R, 5).Formula = FileItem.DateCreated
    Cells(R, 6).Formula = FileItem.DateLastAccessed
    Cells(R, 7).Formula = FileItem.DateLastModified
    Cells(R, 8).Formula = FileItem.Attributes
    'Cells(r, 9).Formula = FileItem.ShortPath & FileItem.ShortName
    'Cells(r, 8).Parent.Hyperlinks.Add Anchor:=Cells(r, 8).Offset(0, 1), Address:=FileItem.ShortPath, TextToDisplay:=FileItem.ShortPath
    Cells(R, 8).Parent.Hyperlinks.Add Anchor:=Cells(R, 8).Offset(0, 1), Address:=FileItem.Path, TextToDisplay:=FileItem.Path
    ' use file methods (not proper in this example)
    'FileItem.Copy "C:\FolderName\Filename.txt", True
    'FileItem.Move "C:\FolderName\Filename.txt"
    'FileItem.Delete True
    If Right(FileItem.name, 3) = "xls" Then
    'Last_Author = DocProps("last author", FileItem.name, GetDirectoryName(FileItem.Path))
    Last_Author = ReadPropertyFromClosedFile(GetDirectoryName(FileItem.Path) & "\" & FileItem.name, "Author", PropertyLocationboth)

    If IsNull(Last_Author) Then Last_Author = "Null"
    'ActiveWindow.ActivateNext
    Cells(R, 10).Formula = Last_Author
    'Workbooks(FileItem.name).Activate
    'Workbooks(FileItem.name).Close SaveChanges:=False
    ElseIf Right(FileItem.name, 3) = "mdb" Then

    'If Right(FileItem.name, 3) <> "mdb" And Right(FileItem.name, 3) <> "zip" Then
    'Last_Author = DocProps("last author", FileItem.name, GetDirectoryName(FileItem.Path))
    Last_Author = ReadPropertyFromClosedFile(GetDirectoryName(FileItem.Path) & "\" & FileItem.name, "Author", PropertyLocationboth)

    If IsNull(Last_Author) Then Last_Author = "Null"
    'ActiveWindow.ActivateNext
    Cells(R, 10).Formula = Last_Author
    'Cells(r, 10).Formula = DocProps("last author", FileItem.name, GetDirectoryName(FileItem.Path))
    'ActiveWindow.ActivateNext
    'Workbooks(FileItem.name).Activate
    'Workbooks(FileItem.name).Close SaveChanges:=False
    Else
    Last_Author = ReadPropertyFromClosedFile(GetDirectoryName(FileItem.Path) & "\" & FileItem.name, "Author", PropertyLocationboth)
    If IsNull(Last_Author) Then Last_Author = "Null"
    'ActiveWindow.ActivateNext
    Cells(R, 10).Formula = Last_Author
    End If

    R = R + 1 ' next row number
    x = x + 1
    End If
    Next FileItem
    End If

    If IncludeSubfolders Then
    'Here i need to ignore folders pre element1 of my array
    For Each SubFolder In SourceFolder.SubFolders
    If boTestForSkip Then
    If Not LCase(SubFolder.name) = LCase(SkipToFolderName) Then
    GoTo Skip
    End If
    End If
    'Pass on the new skip path if required
    ListFilesInFolder SubFolder.Path, IncludeSubfolders, newSkipToFolderPath
    'If we have gotten here we have no need to skip anymore as we have surpassed the folder
    boTestForSkip = False
    newSkipToFolderPath = ""
    Skip:
    Next SubFolder
    End If
    Columns("A:J").AutoFit


    'Dim Start_Row As Range
    'Dim End_Row As Range
    'Dim Excel_Range As Range


    'Range("A3").Select
    'Set Start_Row = ActiveCell
    'Set End_Row = Range("J" & ActiveCell.End(xlDown).Row)
    'Set Excel_Range = Range(Start_Row, End_Row)

    'Excel_Range.Select

    'Excel_Range.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
    End Sub

    Function BrowseForFolder(Optional OpenAt As Variant) As Variant
    '''Code from kpuls, www.VBAExpress.com..portion of Knowledge base submission

    Dim ShellApp As Object
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0

    Set ShellApp = Nothing

    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
    If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
    If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
    GoTo Invalid
    End Select
    Exit Function

    Invalid:


    'ToggleStuff True
    End Function
     
    , Jul 3, 2013
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. JerryS

    Recreate Table Relationships

    JerryS, Jul 17, 2003, in forum: Access Table Design
    Replies:
    2
    Views:
    109
    JerryS
    Jul 19, 2003
  2. Jo

    Table relationships problem

    Jo, Jul 23, 2003, in forum: Access Table Design
    Replies:
    0
    Views:
    89
  3. Heather

    Table relationships

    Heather, Aug 14, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    67
    Tim Ferguson
    Aug 14, 2003
  4. David Ward

    Access table relationships . from dave

    David Ward, Sep 14, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    134
    Tim Ferguson
    Sep 14, 2003
  5. Larry

    Relationships Created by Lookup Wizard in a Table

    Larry, Oct 15, 2003, in forum: Access Table Design
    Replies:
    2
    Views:
    77
    Larry
    Oct 15, 2003
  6. David

    Table Relationships

    David, Oct 30, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    72
    Steve Schapel
    Oct 31, 2003
  7. Luis

    Disappearing table relationships

    Luis, Nov 3, 2003, in forum: Access Table Design
    Replies:
    4
    Views:
    138
    John Vinson
    Nov 4, 2003
  8. David

    Where to define table relationships?

    David, Nov 6, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    79
    Gary Miller
    Nov 6, 2003
Loading...