Help creating tables and relationships (1 Viewer)

tb12

New member
Local time
Today, 09:53
Joined
Nov 13, 2016
Messages
2
Hey, I am kinda new to access and I am having trouble identifying the tables and creating relationships from based off these tables. It keeps coming up
"no unique index found " when trying to join the chauffeur booked to the booking table. Maybe I don't need one of these tables but I am not sure. I am trying to create the database so that when appointments come in I can fill out a form for customer bookings and that will automatically fill the tables out. any help is much appreciated.
 

Attachments

  • IMG_1143[1].jpg
    IMG_1143[1].jpg
    100.9 KB · Views: 155
  • IMG_1144[1].jpg
    IMG_1144[1].jpg
    97.4 KB · Views: 142

Simon_MT

Registered User.
Local time
Today, 16:53
Joined
Feb 26, 2007
Messages
2,177
Perhaps you should put the Vehicle and Chauffeur into Bookings and then put all the vehicle information (Make) into Vehicles

Simon
 

plog

Banishment Pending
Local time
Today, 11:53
Joined
May 11, 2011
Messages
11,611
I'm looking at just your relationship screenshot and this is what I see wrong:

1. Bad field/table names--spaces. You shouldn't use spaces in field names--nor any other non alpha-numeric characters. Instead of [Date of Inspection] you should use [InspectionDate], (also [Vehicles Inspections]->[VehicleInspections], [First Name]->[FirstName], [Card Number]-> [CardNumber], etc). It just makes writing query and code easier later on.

2. Bad field names--reserved words. You shouldn't name anything (fields, tables, queries, reports, etc) with reserved words (https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe). This are system specific words that access uses and can screw up code/queries later on. That means [Date], [Time], [Year] shouldn't be field names. Instead you should prefix them for what Date/Time/Year they represent--kind of like how you have an [Expiry Date] field--you prefixed it with what that date represented.

3. Seperate fields for Date/Time. Booking has a Date field and a Time field, there is no need for that. The datatype of both those fields is probably a Date/Time--that means it can hold both of those and that's how you should do it. Instead of 2 fields to hold that data, you should simply have one.

4. [Make of Car] in wrong table. That field is an represents an attribute of the Vehicle itself, not of the rental. It should be in the Vehicles table.

5. [Booking Reference] used as foreign key, but its not the primary key of its table. [Vehicles Rented].[Booking ID] links to [Booking].[Booking Reference], but the [Date] and [Time] fields of [Booking] are the composite key of [Booking]. Instead you should make [Booking Reference] the primary key of [Booking].
 

tb12

New member
Local time
Today, 09:53
Joined
Nov 13, 2016
Messages
2
okay thanks guys for your help its much appreciated... any idea if i missed any tables that are needed
 

Users who are viewing this thread

Top Bottom