Results 1 to 8 of 8
  1. #1
    Nixx1401 is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    Feb 2010
    Location
    Barbados
    Posts
    115

    Changes to database design

    I created this database to record inmates visits and letters to friends and family. This is the process the inmates fills a form (visit form) which is sent to a department which enters the data in the database. the form contains the name of the inmate and his or her contact and invitees.



    therefore a visit form will have on the contact person in which the visit is posted to and it contains up to 3 invitees (invitees persons who can accompany the contact on day of visit)

    The database has been serving its purpose until I was asked to accomodate the department in the contact calls to make the appointment for the visit.

    I was thinking I would create another table called appointments which will contain appt time, appt date, contact name and invitee name but I am experienceing problems with the population of the above.

    Attached is a sample of the database with test data

    Any help and comments would be appreciated

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,616
    You should have one field for Gender, not two Yes/No fields. A person can only be one or the other. Make it a text field and use combobox that offers only two choices.

    Your idea for another table for appointment calls makes sense. The info would be:
    StaffID or name
    CallDate
    ApptDate

    I don't see need for contact or invitee names. Those are already in tables and can be retrieved in queries that join the tables on InmateID and the date.

    Alternatively, include this data in tblTransaction. I guess they would not apply to the Letter entries, resulting in blank fields in the records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Nixx1401 is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    Feb 2010
    Location
    Barbados
    Posts
    115
    Would the appt table have a primary field and how is this table join or the relationship to the other tables?

    The three fields you suggested should be added to the transaction table, please explain.

    I dont quite understand.

    Thanks for you initial response

  4. #4
    Nixx1401 is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    Feb 2010
    Location
    Barbados
    Posts
    115
    There is a query name tblcontactInfo query query1 this is the information I would like to generated in the format of form with text boxes to accommodate the appointment time and date but I am experiencing some problems with generating this form. Could someone please assist

  5. #5
    dblife's Avatar
    dblife is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Nov 2011
    Location
    South Shields, UK
    Posts
    104
    I have taken a look at your sample - could you please describe again exactly what you need to do - i do not understand your first message in this thread.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,616
    The call is to set up visit appointment? You can record the call in the appointment table you proposed OR if there will be only one call to establish an appointment could just save info with the visit record in Transaction table. This is not strict normalization because not all records (the letter ones) will have this data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Nixx1401 is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    Feb 2010
    Location
    Barbados
    Posts
    115
    The database is being shared between two departments therefore I dont want the department that records the appts to be able to edit the information from the transaction form.

    Basically, I wanted that when a contact calls to make an appt the data relating to that contact is accessed and the appt time and date is entered and also the ability to enter appts for individuals or lawyers etc in which a visit was not recorded.

    So I was hoping to add the appt table and utilise the contactinfo query query1 in a form and add the appt time and appt date to accomodate such.

    Any suggestions are welcome

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,616
    Just because data is saved in same table doesn't require all fields be available on a form. Same applies to a saved query. If you don't want the form to present the fields to users then don't bind any controls to the fields. Could have a form for entering appointment info and then a form for the other data.

    I really don't have anything else to offer. Have separate table or use same table, decide what best suits your situation. Build queries as appropriate. Can build a query and save it for use by multiple reports/forms or SQL statement can be built directly in the RecordSource property.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM
  2. DataBase Design.
    By cap.zadi in forum Database Design
    Replies: 3
    Last Post: 09-24-2011, 02:54 PM
  3. Help with Database Design
    By neo651 in forum Access
    Replies: 3
    Last Post: 09-11-2011, 06:33 PM
  4. Database Design
    By shutout14cf in forum Database Design
    Replies: 10
    Last Post: 12-20-2010, 11:04 AM
  5. Database design help
    By DaveyJ in forum Database Design
    Replies: 7
    Last Post: 06-09-2010, 04:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums