Table relationships

  • Thread starter Thread starter HENRY ONYEAGBAKO
  • Start date Start date
H

HENRY ONYEAGBAKO

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.
 
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.
 
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
 
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.
Click to expand...

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
 
Back
Top