Results 1 to 6 of 6

Thread: Design question

  1. #1
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139

    Unanswered: Design question

    Hello;
    This may be a bit wordy so please forgive me. I could use a little help with a design convention. I am designing a database to store information about credit account charge disputes. Previously I had a DisputeCases table which contained all aspects of a dispute, including the customer's account number and name. This caused account numbers and names to be replicated many times. Then, I changed the format to have a separate table for just the customer information (account number, first and last name), and a separate table for the dispute case information, and linked them (one-to-many relationship - AccountNumber to DisputeCaseNumber). When I formatted the joint form (Form/Sub-Form) to combine the input of customer data as well as dispute case data on one form, the form gives me an error stating I am duplicating data in the customers table when tabing off the sub-form in order to get to the main form (if this account number had previously disputed a charge).

    I understand the why behind the error, but what I can't understand is how to overcome this. Should I go back to one huge table, which would include many duplicate account numbers (because the same person can have either multiple disputes, or can dispute many charges over time), or is there some way I can have a separate table for the customer information and dispute case information, yet still associate the dispute case number back to that customer such that a search on account number can be done to obtain previous disputes for that customer. I think I still need the agent to input the customer number in the data input form in order to associate the dispute to a particular customer. Is there some way to perhaps input the customer number but not save it unless it is unique, but yet still be availabl for a report based on the current input (I want to print a dispute case cover sheet which includes all associated data)

    I also need a way to check a date field, then check an agent field, and if the input date being currently entered into the form control equals an input date already in the database for that agent, display a message that there is data already on file for that date.

    Thanks for any thoughts or suggestions.

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by Larryg
    Should I go back to one huge table, which would include many duplicate account numbers (because the same person can have either multiple disputes, or can dispute many charges over time), or is there some way I can have a separate table for the customer information and dispute case information
    No. What you have done in terms of seperating the data is the right approach. This is called Normalisation and is the kingpin of relational database design. Read up on "database Normalization" - it's a pre-requisite for any development.

    Quote Originally Posted by Larryg
    Is there some way to perhaps input the customer number but not save it unless it is unique, but yet still be availabl for a report based on the current input.
    Take a look at my example attached. The key points are:
    - two tables (like you have)
    - a relationship between the tables (one-to-many)
    - A form and subform
    - A master/child relationship between the form/subform

    Also note:
    - AccountNumber does not need to appear in the subform. The master/child relationship ensures this is inherant (and automatically entered to the Disputes table.
    - You can use the form to create new accounts without entering anything in the subform (although it's usual to have a dedicated Account form)

    Quote Originally Posted by Larryg
    I also need a way to check a date field, then check an agent field, and if the input date being currently entered into the form control equals an input date already in the database for that agent, display a message that there is data already on file for that date.
    You could make AccountNumber and Date (both) the PK in your Disputes table. This will ensure no duplicates are possible. Another way to do it is to use the validation rule in the properties for each field.

    hth
    Chris
    Attached Files Attached Files

  3. #3
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Thank you so much Chris for the reply. I'll try to make it work. I'm new to Access and some of the things I'm trying to do seem so simple in concept yet appear to be difficult to accomplish in Access. I'm sure with experience and use it will get easier, but I'm struggling now. Today I spent hours trying to get the data from a form printed. No luck. I even copied code right out of a page that had a working print button - no luck (I did change the references). I'll be back at it tomorrow morning !

    Larry

  4. #4
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hi Chris;

    Maybe I did something wrong but I received the error message "The record could not be deleted or changed because table "tblDisputes" includes related records". This was the same thing happing to me when I tried the form/sub-form on my database. I entered a new record in your form, tabing through the 3 fields, (making sure it was not a duplicate record), then when I hit tab for the 4th time it returned the error message. Your form (and relationship) looks the same as I had mine. Your thoughts? I had to convert your file to Access 2002, could that of made any difference?

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Larry,

    Yes I can get the error message by clicking on a field in the form area and clicking Edit-Delete or by trying to change the AccountNumber. This error is to be expected. The relationship (in the relationship view) has not been set to cascade. Therefore, you can't delete (or change) and account number while you have related Disputes otherwise the Disputes would become orphaned.

    If you want all related records to be deleted when when you delete the master (Account) record, the edit the relationship (in the relationship view - double click on the relationship line) and tick the "Cascade Delete Related Records" option. As you will see you can also tick "Cascade Update Related Fields" if you wish which would allow you to change the account number and it would change in all related records (I'm not a fan of this mind!).

    Make sure when you're adding new records that you don't try to edit the account number of the current record. Note that there are two record navigators in the form/subform view. Make sure that when you want to create or move to a new account that you use the appropriate navigator.

    If you want to post your database, please do and I'll take a look.

    Chris

  6. #6
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Thanks Chris... I'll make it available a little later today.
    I'm still struggling with this printing issue. Maybe it's something simple. When I use the Command Button Wizzard to set up a report to print, I get an error of: "The database engine could not lock table "tbl_DisputeCases" because it is already in use by another person or process". No one else is in the database, and I do not have the table open in any window. Also, the Property selection (FORM) for the Record Lock is set to Edited. Any thoughts?

Posting Permissions

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