Results 1 to 6 of 6

Thread: MS access

  1. #1
    Registered User
    Join Date
    May 2005
    Posts
    11
    Thanks
    0
    Thanked
    0 times in 0 posts

    MS access

    I am trying to create a database but keep on screwing up the tables!

    Its basically a car rental database with bookings and the usual rental things. But the extra thing is, I want to be able to have extra details for the cars like when they where last serviced and what was done. I also want to be able to print of invoices and damage reports, now i know this can be done with mail merge etc. But the issue is getting these tables set up.

    So far I have

    Customer - customer id, surname, first, address, contact etc

    car - car id, registration, Manufacturer, Model, mileage.

    booking - booking id , car id , customer id, date of collection

    rental - car id , customer id, date collected, date to be returned, returned?

    Now im not really sure what do to with the servicing details or things like that .....

    Anyone offer any advice how to clean this up a little?

    Thanks alot for any help given, will be appreciated

  2. #2
    One Great Ordeal!
    Join Date
    Feb 2005
    Posts
    1,764
    Thanks
    201
    Thanked
    56 times in 49 posts

    Re: MS access

    I'd strongly suggest you layout the tables in a more understandable format:

    For example:

    Code:
    Table name = Customer Info
    Entity Name            | Entity Type |              Null
    Customer ID              NUMBER                       NOT NULL
    First Name               TEXT                       NOT NULL
    Last Name               TEXT                       NOT NULL
    Address Line 1          TEXT                       NOT NULL
    Address Line 2          TEXT                       NOT NULL
    Address Line 3          TEXT                       NOT NULL
    Address Line 4          TEXT                       NULL
    Postcode                 TEXT                       NOT NULL
    Phone number           TEXT                       NULL
    You then need to think about your relationships. Will one customer be able to have more then one car?
    If so then you need a link entity:

    Code:
    customer table ---> customer_cars <------ car details
    customerid          cust_car              carid
                        *customerid
                        *carid
    Underline denotes a primary key. * denotes a foriegn key. Not Null means it must contain data.

    There are wizards which can put together a basic report which you can then tweak after to make it more suitable for purpose. Also you might want to make use of a switchboard and forms to ease input
    Last edited by digit; 29-01-2009 at 08:25 PM.

  3. #3
    Registered User
    Join Date
    May 2005
    Posts
    11
    Thanks
    0
    Thanked
    0 times in 0 posts

    Re: MS access

    Hi,

    thanks alot for your reply.

    If I wanted to have even more in depth details on the rental car would it be wise for me to create a new table for that.

    EG -

    Table name - Car Detail

    CAR DETAIL ID
    car id
    last service
    location

    I am more bothered about how to get the servicing and damage reports in the tables.

  4. #4
    One Great Ordeal!
    Join Date
    Feb 2005
    Posts
    1,764
    Thanks
    201
    Thanked
    56 times in 49 posts

    Re: MS access

    Details about the rental would indeed go in a new table, you don't want to have data about a car duplicated!

    A report would be an extraction of data from multiple tables? Would be something you click a button to generate / update?

    Lets say you want a report on what cars have been rented?

    For example a report on car rentals could select from the rental table the rental id, customer id and car id.
    From the customer table the data matching the customer id eg first name and last name.
    From the car table the data macthing the car id for example what make and engine it has.

    I can't remember the correct sql syntax, but you need not worry about that as access has a nice graphical front end.

    Your report would then be:

    Rental Report

    Rental ID: 1
    Name: Joe Blogg
    Car: Peugeot 206 1.2
    _________________________
    Rental ID: 2
    Name: Joe Green
    Car: Ford Focus 1.8
    _________________________
    Rental ID: 3
    Name: Bob
    Car: Fiat Punto 1.4

    For each rental ID you have a further breakdown with info from the other tables. Unless i'm missing something?
    Last edited by digit; 29-01-2009 at 08:48 PM.

  5. #5
    Registered+
    Join Date
    Jan 2009
    Posts
    18
    Thanks
    0
    Thanked
    0 times in 0 posts

    Lightbulb Re: MS access

    If you need any software to be done
    this is best place to find web master and software coders

    RentaCoder

  6. #6
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: MS access

    I find it useful to start with the tables for things that will be unique

    1) Customers: Each customer will only have one record
    2) Cars: Each car will only have one record.
    3) Booking / Rental record: Combine these two tables because a booking is simply a rental that hasn't happened yet. You can add a simple status flag to set whether it's a booking, a rental in progress, or complete.

    The secondary tables contain the more detailed information, such as service history, damage reports etc. They'll reference back to the car table and possibly the customer and booking tables, depending on the features wanted.

    In terms of invoicing, are you designing this system to also serve as basic accountancy software? Or are you feeding the data back into a system such as Sage 50? If it's the former, it's a case of writing a report that prints off all completed rentals, that haven't got an invoiced flag ticked and then making sure the print routine flags them as invoice sent (or maybe first invoice sent, and have a secondary function that allows re-invoice). When writing the forms for things like this, try to consider what the user may need to do, will they need to print out another copy? Re-run a batch if the printer jams? etc.


    The most important thing to remember is to keep seperate tables for things that only need to be recorded once, from tables that list the day to day activty. I generally tag the tables with a shorthand code to indicate tables that are recorded once as "ref" and the day to day ones as "tran" (short for transaction). Helps keep things clearer IMO.

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. urgent help with c++ function access
    By haelly in forum Welcome to HEXUS!
    Replies: 1
    Last Post: 17-12-2007, 05:11 PM
  2. Internet access through wireless, file access through LAN
    By Impeller in forum Networking and Broadband
    Replies: 2
    Last Post: 15-11-2007, 09:08 PM
  3. Belkin F5D6130 802.11b Wireless Access Point
    By Howard in forum Reader Reviews
    Replies: 4
    Last Post: 10-12-2004, 11:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •