DB Design

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nancy - 01 Feb 2004 01:26 GMT

Access 2000
I want to create a DB, but I need help.
This will be used as a customer recall log.

The fields will be:
Recall No.
Unit No.
Customer ID No.
Last Name
First Name
Purchase Date
Reason
1st Letter Request
1st Letter Sent
2nd Letter Request
2nd Letter Sent

The 1st 7 fields will be filled out first.
When I request a letter from the secretary to the customer
the first time, I'll put the date in the "1st Letter
Request" field. When I send a letter, I'll put the date
in the "1st Letter Sent" field. If the customer does not
call me in 7 days after the 1st Letter Sent date, I want
to be alerted some how.

Is there a way the DB can be set up so when I open
the "Customer Recall Form" (when I create the form), a
list appears with the customers who haven't answered the
recall 7 days after the 1st Letter Sent date?

Then when I receive the alert, I then have to put the date
in the "2nd Letter Request" field and request a 2nd letter.

How should this be designed and set up?

ANY help is GREATLY appreciated.

Reply to this Message

John Vinson - 01 Feb 2004 05:34 GMT

>Access 2000
>I want to create a DB, but I need help.
[quoted text clipped - 12 lines]
>2nd Letter Request
>2nd Letter Sent

Whenever I see two fieldnames beginning with "1" and "2" I cringe.
You're embedding a one to many relationship within each record.

Will you NEVER have a *third* letter? Will you *always* have a second
letter (I gather you won't)?

I'd be inclined to pull these out into a second table, RecallLetters,
with fields:

Recall No. <link to the table above>
Letter No. <sequential, 1, 2, maybe 3>
RequestDate
SentDate

You'ld add one, or two, *records* to this table. It would be very easy
to find Recalls with only a Letter No. 1 with a date earlier than
(say) DateAdd("d", -14, Date()) to find those undealt with in 14 days.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message

Eechhutti R.Rajasekaran - 01 Feb 2004 06:08 GMT

> Access 2000
> I want to create a DB, but I need help.
[quoted text clipped - 32 lines]
>
> ANY help is GREATLY appreciated.

Add one more field Customer Reply to enter the date of customer's reply.
Generate a query with all the fields, giving the criteria under "1st Letter
Sent" field to be Date()-7 and "Customer Reply" field to be Null. When you
run the query, you will get the details of the customers to whom 1st letter
has been sent 7 days back, but have not called on.

Create a macro to run this query and name it as autoexec. In such a case,
when you open the file, automatically you will get the particulars of such
customers you have to contact.

Eechhutti R.Rajasekaran
rsrraja@hotmail

Reply to this Message