Hello, I am hoping someone can help me with this. I am very new to Access and kind of clueless. I have to start somewhere. I went through thread after thread to see if I can figure it out on my own, but found nothing. This is what I need to do. My database consists of 345 records (each record needs to hold at least 20 comments or notes individually). I have a tab on my form entitled Notes. On this notes tab, I have 20 fields entitled notes and dates. What happens is each time a person goes out in the field, they come back and record a note of what they have done each time they visit one of the 345 locations. So what I want to do is to have them enter this information and have it appear in chronological order by date on this tab. I know that I approached this all wrong (with all those fields), but I am open to suggestions as to how to accomplish this and with simplicity so that they will understand what is required of them. What would be the best way to have them enter these? What and how do I set it up? Any help would be greatly appreciated. Thank you!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
HOW TO ADD COMMENTS OR MESSAGES IN CHRONOLOGICAL ORDER ON A FORM TAB
- Thread starter sagehrer
- Start date
TheAceMan1
Programmer
How are ya sagehrer . . . . .
Your [blue]tables and relationships[/blue] are apparently not setup properly. You should have a [blue]Locations Table[/blue] & [blue]Notes Table[/blue], and possibly a [blue]Person Table[/blue]. Since your understanding of Relational Databases needs sharper focus (there are a number of terms that will probably go right over your head), critique the following links and I promise you'll be glad you did . . . . .
[blue]Fundamentals of Relational Database Design[/blue]
[blue]Normalizing Tables[/blue]
[blue]Table Relationships[/blue]
See Ya! . . . . . .
Your [blue]tables and relationships[/blue] are apparently not setup properly. You should have a [blue]Locations Table[/blue] & [blue]Notes Table[/blue], and possibly a [blue]Person Table[/blue]. Since your understanding of Relational Databases needs sharper focus (there are a number of terms that will probably go right over your head), critique the following links and I promise you'll be glad you did . . . . .
[blue]Fundamentals of Relational Database Design[/blue]
[blue]Normalizing Tables[/blue]
[blue]Table Relationships[/blue]
sagehrer
I suspect AceMan is absolutely correct.
It sounds like everytime you have a new entry, you need to modify your data / form.
With a Normalized database, you can have 1,000's or 10,000's comments if you wish. And it would be a small matter of using a SELECT SQL statement using ORDER BY YourDateField Desc to display the data with the most recent observation first.
An example of design may look like...
tblLocation
LocationID - primary key
LocationDesc - text
tblContact
ContactID - primary key
ContactLN - last name
ContactFN - first name
tblVisit
VisitID - prmiary key
ContactID - foreign key to Contact table
LocationID - foreign key to Location table
VisitDate - date of visit
VisitNote - memo field documenting visit
With this design, you can see...
- activities of the contact / support rep - date, location and activity
- activities of every location - which contacted visited on what date, and their notes.
Richard
I suspect AceMan is absolutely correct.
It sounds like everytime you have a new entry, you need to modify your data / form.
With a Normalized database, you can have 1,000's or 10,000's comments if you wish. And it would be a small matter of using a SELECT SQL statement using ORDER BY YourDateField Desc to display the data with the most recent observation first.
An example of design may look like...
tblLocation
LocationID - primary key
LocationDesc - text
tblContact
ContactID - primary key
ContactLN - last name
ContactFN - first name
tblVisit
VisitID - prmiary key
ContactID - foreign key to Contact table
LocationID - foreign key to Location table
VisitDate - date of visit
VisitNote - memo field documenting visit
With this design, you can see...
- activities of the contact / support rep - date, location and activity
- activities of every location - which contacted visited on what date, and their notes.
Richard
- Thread starter
- #4
Hello, Thank you both for your help. I already had my relationships set up and trying the suggestions. I will be the first to admit, alot of the information did go over my head. There has to be a simple way. I have two tables: one a contacts table and the other notes table. In the contacts I have information such as the location id, address, etc. and then in the notes table, I have the location id I have the dates and the notes. I have brought them together and created a form with 7 tabs. The first 6 are fine and working exactly how I want. The problem is my notes tab. What I previously had was 20 fields for the date and 20 fields for the comments when this was first designed. Some of the entries were made in the first field and some were made in the 20 notes field they are out of whack, which I will fix if I have to, but how do I get this tab to (1) enter the newest note on top and the previously keep moving down somehow so as to make a consistency with data entry and making easier to view the dates and notes. Now what I need to accomplish is to have the most recent note and date at the top and then go chronologically from there for each individual record. I have been reading book after book and I think I totaly confused myself. When the dates and notes get entered into each record indiviudally, I just need to have the most recent on the top and then previous on bottom and just keep going from there. I tried using a subform, but the data just goes on forever across the page because of the length of some of the notes and I got it to just show the current record which is a plus, but I need to find a way to accomplish this. I tried even just leaving the data as it is and just placing a print report command on the page, but it keeps telling me there are too many fields for that. I can print the current record Notes with a print command, but they have no order to them because of the way they have been entered. If I am not being specific please let me know. I know how it is, I know what I want, but you might not. Thanks for any help that you can provide.
TheAceMan1
Programmer
sagehrer . . . . .
If you [blue]make notes a subform[/blue] on the Tab and set the [blue]RecordSource[/blue] of the subform to a query/SQL, it will be a simple matter of sorting by date. After you enter an Date & Note a simple requery (Me.Requery) of the subform puts everything in order . . . .
See Ya! . . . . . .
If you [blue]make notes a subform[/blue] on the Tab and set the [blue]RecordSource[/blue] of the subform to a query/SQL, it will be a simple matter of sorting by date. After you enter an Date & Note a simple requery (Me.Requery) of the subform puts everything in order . . . .
TheAceMan1
Programmer
sagehrer . . . . .
Forgive me, but this is just an Idea of what I really see according to what you've posted so far!
I'm not asking or suggesting you change anything . . . . this is just food for thought . . . .
See Ya! . . . . . .
Forgive me, but this is just an Idea of what I really see according to what you've posted so far!
Now, if [blue]one person can visit 345 locations[/blue], and [blue]one location can be visited by people[/blue], this is called a [purple]Many to Many Relationship[/purple] and setup properly requires an additional table. Your setup involves a [blue]One to Many Relationship[/blue] and as such logically does not fit your needs. Because of this I can't help but see great troubles down the road.sagehrer said:
I'm not asking or suggesting you change anything . . . . this is just food for thought . . . .
- Status
Similar threads
- Locked
- Question
