Designing Database

[Del_Piero_3]

Hi Guys,

I need some ideas on designing a database.

I send about 300 letters nationally to all senior staffs in NHS to make sure their clinics/staffs are following the correct procedures regarding patient info etc. This review has to be done on annual basis. Every year I would send them a letter and in return I would expect a reply. When I get a reply their answer is recorded for them for that year and ticked off. If they haven’t replied then after each month I have to re-send the letters until a confirmation is received.

I need to design a database to track this and keep a record of those that have replied and flag those that are yet still to reply in the current year. Some sort of reminders etc.

Can someone please just give me a brief example of how the structure and the relationship should look like. I would really appreciate your help. Thank you

Kind Regards

 

[AlienRay]

I would recommend three tables. Your first table lists all of your contact information, and will be used to generate your form letters. The second table is a listing of all letters that have been sent out, and will only need to include two fields: the ID of the contact, and the date the letter was generated. The third table is identical to the second, but instead records when replies were received.

Create a query that shows the maximum date for each contact in both table 2 and 3. This will show you the last time a letter was sent out, and the last time a reply was received. A third query will compare these two tables, to find any contacts that do not have a reply entry later than their most recent letter entry. This query should also look for contacts that have never had a letter sent.

Create a report based on this third query, which is essentially a form letter populated with the information in your contact table.

Each month, after you run this report, run an Update query on your second table, to populate each company that appears on the report, and the current date.