If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help with design pls
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. |
#2
|
|||
|
|||
Need help with design pls
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. "TotallyConfused" wrote: 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. |
#3
|
|||
|
|||
Need help with design pls
"TotallyConfused" wrote in
message ... 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 |
#4
|
|||
|
|||
Need help with design pls
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 a 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. "John... Visio MVP" wrote: "TotallyConfused" wrote in message ... 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 |
#5
|
|||
|
|||
Need help with design pls
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 "TotallyConfused" wrote in message ... 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. |
#6
|
|||
|
|||
Need help with design pls
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. |
Thread Tools | |
Display Modes | |
|
|