Changes to database design

[Nixx1401]

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

[June7]

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.

[Nixx1401]

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

[Nixx1401]

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

[dblife]

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.

[June7]

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.

[Nixx1401]

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

[June7]

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.