Need help with design pls

[TotallyConfused]

I have an old unnormalized Access db. The main table is a flat table with
all the fields being used. I would like to revamp this db. I am having
trouble how to set up the date fields. there are 9 date fields 3 Sent, 3 Due
and 3 Rcv number 1 through 3. Can someone help me guide me how to go about
this? Mail will go out and need to be track when it is due and when it is
received. So for instance mail will go out to Mr. Access - date will be
entered into 1st Dt Sent. 1st Dt Due will automatically be filled in by the
date entered into the 1st Dt Sent. A date will be entered manually in the
1st Dt Rcv. Mail can and will be sent 3 x until something is received. Will
I need to keep this setup or is there a better way to do this? Is there a
sample db where I can see a better way of setting this up? Thank you for any
information you can provide.

 

[KARL DEWEY]

I think all you need is the sent and received date fields. A query can
calculate when mail is due based on last sent and you can count how many time
you sent in a sub-query.

You can use a union query to realign your data.

 

[John... Visio MVP]

I have an old unnormalized Access db. The main table is a flat table with
all the fields being used. I would like to revamp this db. I am having
trouble how to set up the date fields. there are 9 date fields 3 Sent, 3
Due
and 3 Rcv number 1 through 3. Can someone help me guide me how to go
about
this? Mail will go out and need to be track when it is due and when it is
received. So for instance mail will go out to Mr. Access - date will be
entered into 1st Dt Sent. 1st Dt Due will automatically be filled in by
the
date entered into the 1st Dt Sent. A date will be entered manually in the
1st Dt Rcv. Mail can and will be sent 3 x until something is received.
Will
I need to keep this setup or is there a better way to do this? Is there a
sample db where I can see a better way of setting this up? Thank you for
any
information you can provide.

I have done a couple of correspondence tracking systems and the key is to
have a correspondence file with a record for each letter.

Can you expand on what you mean by Due Date and do the Dt Rcv and Dt Sent
indicate when the mail was recived and a response/request was sent out?

John... Visio MVP

 

[TotallyConfused]

Yes the Dt Sent is the Date mail was sent. Due date is the date we ask for a
response (we give them 7days to respond). the Rcv dt is the date we receive
correspondence back. Table fields are:

1st Dt Sent
1st Due Dt
1st Dt Rcv
2nd Dt Sent
2nd Due Dt
2nd Dt Rcv
3rd Dt Sent
3rd Due Dt
3rd Dt Rcv

As I explained earlier these field are included in a flat table of about
another 20+ fields. Would I have to create a separate table with only Date
Sent, Date Due and Date Rcv fields???? thank you for any help you can
provide.

 

[tina]

so you have a piece of correspondence (letter, bill, whatever), which may be
sent many times. that's a one-to-many relationship. suggest the following
tables, based only on your post, as

tblMailItems
ItemID (primary key)
<other fields that describe the mail item, but *not* when it was sent, due,
or a reply received.>

tblItemDates
ItemDateID (primary key)
ItemID (foreign key from tblMainItems)
ItemAction (sent, received, any other actions that may apply)
ItemDate (note, don't use the word "Date" by itself as a fieldname, as it's
a Reserved word in Access)

you don't need to record a due date, unless that 7-day time frame can be
arbitrarily changed. even then, i might store the time frame as a number of
days, rather than a due date.

so if you send out a mail item twice, and then get a reply, tblItemDates
will have *three* records related to that mail item: first date sent,
second date sent, date received.

recommend you read up/more on relational design principles, so you'll
understand how and why to set up your tables in a normalized structure. for
more information, see http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Fred]

One thought (which is sort of just subset of Tina's answer) is that you are
really databasing 2, maybe 3 types of entities, and so the the word
"correspondence" is so broad that it's confusing the issue. I think that the
entities that your are databases are actually:


- Documents
- Transmittals of documents

And possibly:

- Obligations ("due" etc.) regarding documents

If so, you might want to design, name and link tables accordingly.