Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Question Unanswered: Help designing tables/relationships

    Hi everyone and thanks in advance for any help!!!!

    Recently I decided to help a friend set up a database for his small business but at the min I'm having some trouble with the actual table and relationship design with it...

    the database is to store customer accounts, as well as details of the various "jobs" offered, in this case pc & laptop repairs, data recovery, website design and search engine optimisation. So far I have the table for storing customer details, as well as a table for storing details on each job (each job has its own table for storing details since details are different for each job).

    now this is where I'm stuck - how do I link a customer account to a job

    my tables are so far

    tblCustomerAccounts
    CustomerID
    custFirstName
    custSurname
    custAddressLine1
    custAddressLine2
    custCityTown
    custContactNumber
    custMobileNumber
    custEmail


    tblDataRecoveryJobs
    JobID
    JobType
    Date
    Problem
    FormatPCPermission
    BackupData?
    DataToBeBackedup
    AdditionalRequests
    AdditionalHardwareSoftware
    EstimatedJobTimeDays
    TotalCost
    Quote?
    Completed?

    tblSEOJobs
    JobID
    JobType
    Date
    HoursPerMonth
    KeywordIdeas
    BusinessDescription
    IdealVisitor
    CompetitorWebsites
    TotalCost
    Quote?
    Completed?

    tblCustomerInvoice
    InvoiceID
    CustomerID
    JobID

    But surely having JobID in each table would cause problems with duplicates? And giving each table a unique primary key e.g tblRepairs has RepairJobID, and the invoice table like this:
    tblCustomerInvoice
    InvoiceID
    CustomerID
    RepairJobID
    DataRecoveryID
    WebsiteDevelopmentID

    would lead to redundant data as only one job type would be filled?

    Any help is greatly appreciated!!!
    Last edited by SCassidy2009; 10-27-09 at 15:19.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    how do I link a customer account to a job
    Add the CustomerID to the job table and create a relationship.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2009
    Posts
    4

    Linking customer to jobs

    Look for a table that has the Customer Id and Job ID in it. The rest should be easy.

Posting Permissions

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