Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    1

    Unanswered: Need help design a simple complaint database

    Hello

    I am a newbie to the world of database. I have been entrusted to handle the complaints of my company for which i wanted to make a simple ms access (2007) database in which i can add data, recall data using query, know whats pending etc...

    I have designed 4 tables -
    1. complaint_id which has the following fields complaint # (primary key autonumber), date (auto entry), time (auto entry), received by (text) and complaint by (look up list having options customer, bank, internal dept)
    2. customer which has transaction # (primary key, number), complainant name (text), contact # (number), email (text)
    3. complaint_details which has id(dummy primary key, number), complaint details (memo), department (look up list), contact person (text), remarks (memo), status (look up list - solved, pending)
    4. bank_complaints which has transaction # (primary key, number), institution name (text), institution contact (text),

    I am having trouble in creating relationship between the tables. I don't understand how to create one to many relationship since each field of each table is unique to one complaint. I am also having trouble in creating sub forms since a one to many relationship is required for doing this.

    The work flow is simple; First form to appear is complaint_id and depending upon the data in complaint by, a sub form is to appear (if the choice is customer then customer form is to open along with complaint_details form below the complaint_ID form. If bank is chosen then bank_complaints is to open along with complaint_details.

    Can anyone help me out with this. Really appreciate any help!!!!!

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    well first get a textbook; easily found at Amazon or any big box book store.

    you defer your focus on making relationships, in terms of the database design. If you have a clear understanding of the cross referencing field between tables - that is key....because when you make a subform into a main form - the wizard will set up that 1-Many table relationship for you automatically.

  3. #3
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Casim View Post
    Hello

    I am a newbie to the world of database. I have been entrusted to handle the complaints of my company for which i wanted to make a simple ms access (2007) database in which i can add data, recall data using query, know whats pending etc...

    I have designed 4 tables -
    1. complaint_id which has the following fields complaint # (primary key autonumber), date (auto entry), time (auto entry), received by (text) and complaint by (look up list having options customer, bank, internal dept)
    2. customer which has transaction # (primary key, number), complainant name (text), contact # (number), email (text)
    3. complaint_details which has id(dummy primary key, number), complaint details (memo), department (look up list), contact person (text), remarks (memo), status (look up list - solved, pending)
    4. bank_complaints which has transaction # (primary key, number), institution name (text), institution contact (text),

    I am having trouble in creating relationship between the tables. I don't understand how to create one to many relationship since each field of each table is unique to one complaint. I am also having trouble in creating sub forms since a one to many relationship is required for doing this.

    The work flow is simple; First form to appear is complaint_id and depending upon the data in complaint by, a sub form is to appear (if the choice is customer then customer form is to open along with complaint_details form below the complaint_ID form. If bank is chosen then bank_complaints is to open along with complaint_details.

    Can anyone help me out with this. Really appreciate any help!!!!!
    Once you get your tables designed properly, the forms normally are easier to create.



    See if this helps: Data Model
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  4. #4
    Join Date
    May 2002
    Posts
    157

    Complaints Database

    Everyone will approach this differently, and I am certainly not saying that my approach is the best way of doing this.

    When I have dealt with complainants and their lawyers and my organisation's lawyers they have always wanted to know the detail as it was at the time of the incident, not as it currently stands. That is if the complainant lived at a certain address and has since moved then they want to see our records at the time when the complaint was made and showing the address at the time of the incident or if the complainant has since had a name change the Lawyers want to know what the complainant's name was at the time of the incident etc. Because of this my complaints database actually keeps a lot of information rather than looking up employee details as it currently is recorded in the database. (This is completely different from the way a database should normally be written ie with as little duplication of information and only keeping say for example the clientID in the complaints table.)

    You may want to consider the above when writing your database, and how it is to be used in the future.

    Where I can though I will create tables to allow for combo box ('pick lists'). The attached .jpg highlights where I have used pick lists in my complaints database (see green dots).

    You will notice that the client ID field allows for entry of the Client ID Number from the [ClientDetails] table. But it also allows for entry of a default number which is used for all other Persons who are not employees and want to make a complaint. In my organisation we tend to get complaints from people off the street - that is non client/staff members. That is why this is a combo box.

    Most of the work of completing the client info fields actually occurs on the Form. In my Complaints Form I allow the user to pick the client ID from the [ClientDetails] table and then I get it to auto fill the name, address, suburb, postcode, state, phone number, mobile number from the [ClientDetails] table. This then becomes permanently saved in the Complaints Table for the reasons I have previously stated.

    I still have some combo 'pick lists' on the Complaints Table, just incase some silly person wants to add details via the table instead of the form.

    Where I want only certain information entered then I supply a combo 'pick list' which refers to other tables for the information.

    I hope the above helps you in thinking out how you actually want to store your data.


    KD
    Attached Thumbnails Attached Thumbnails Complaints Database.bmp  

  5. #5
    Join Date
    May 2002
    Posts
    157

    I keep on getting a drawing failed message so will try it again

    I keep on getting a drawing failed message so will try attaching it again
    Attached Thumbnails Attached Thumbnails Complaints Database.JPG  

Posting Permissions

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