 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / February 2008Free Form Text Entry
Even though I used to work with databases years ago, I have never worked with Access 2003. Presently, I need to create a very simple application that is similar to one that a credit card company uses, as an example. If you have ever called your credit card company, after you give them your card number, they bring up a page that has all of your account information and a page for comments. The "comments" function is what I need. Here is a crude and grossly simplified representation: =================================== Name: John Smith Credit Card Number: 2342-2343-2343-2342 --------------------------------------- 02-08-2008 Mr. Smith called to complain about us charging him an annual fee. We agreed to waive it for this year. 02-08-2008 Mrs. Smith called to say that Mr. Smith has not been taking his medication and that we should ignore his call. 02-09-2008 Mrs. Smith called again to say "never mind". =================================== The field structure would look like this: =================================== Name: <first name> <last name> Credit Card Number: <card number> --------------------------------------- <date_001> <comments_001> <date_002> <comments_002> <date_003> <comments_003> =================================== Some records may have comments up to 99 comments (comments_099), while most would only have a few comments (say, to comments_05). In the old days, you had to create fields for the maximum number of entries, but I understand that Access can add these fields dynamically. If that is true, how? Second question: What field type is <comments_###> such that free form text multi-line text can be entered? Thanks for the help. > Even though I used to work with databases years ago, I have never > worked with Access 2003. Presently, I need to create a very simple [quoted text clipped - 41 lines] > I understand that Access can add these fields dynamically. If that is > true, how? The proper way is not a "field per comment" it is to use a separate related table and then have a "record per comment". > Second question: What field type is <comments_###> such that free form > text multi-line text can be entered? Text if you need 255 characters or less. Otherwise memo.  SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com You have for the most part laid things out correctly, except for: > <date_001> <comments_001> > > <date_002> <comments_002> > > <date_003> <comments_003> Think of "MANY" applications you used in which repeating data is to appear over and over. What we do is use the relational ability of the database to *repeat* that data. So, in fact, we get two tables: tblCustoemr fields: id (autonumber primay key) firstname, lastname, credit car etc. You have: Name: <first name> <last name> Credit Card Number: <card number> you then need a table with comments for each incident: tblComments, it will have: cust_id, IDate, Comments > Some records may have comments up to 99 comments (comments_099), while > most would only have a few comments (say, to comments_05). In the old > days, you had to create fields for the maximum number of entries, but I > understand that Access can add these fields dynamically. If that is true, > how? *exclclent* question. Yes, you use a another table, and *relate* this table back. That way, if you have 2 reocrds...you don't wind up with 99 blank fields. It also makes reporting on this data VERY easy since you can build a reprot that will show ONLY the cust name + the *last* incident date (if you used 99 fields, this becomes next to impossbile to biulda reprot to figre out which of hte 99 fields is emplory etc. Furhte, you acually would have 300 felds asusming a max of 100 coments. > Second question: What field type is <comments_###> such that free form > text multi-line text can be entered? It is a called a memo field, and even hitting the "enter" key can be set to create a new line in teh text.... So, we would create our main table (user name, credit card etc). In practice, you'd have some means to search/find this customer. upon finding this record, you would present the user with a form with the main contact information, and then a continues sub-form would display the "repeating" informant you have: 02-08-2008 Mr. Smith called to complain about us charging him an annual fee. We agreed to waive it for this year. 02-08-2008 Mrs. Smith called to say that Mr. Smith has not been taking his medication and that we should ignore his call. 02-09-2008 Mrs. Smith called again to say "never mind". The above would thus be 3 new records. I give some screen shots of "repeating" data forms here: http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm if you want to see some samples of applications, then check out the northwind sample that ships with ms-access (in a2003...go help->sample databases->northwind sample database). As a another suggestion, as a general rule you find it easier if you avoid spaces in field names (they are allowed, but I recommend you avoid this practice). SignatureAlbert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com > You have for the most part laid things out correctly, except for: > [quoted text clipped - 71 lines] > spaces in field names (they are allowed, but I recommend you avoid this > practice). Albert, you and Rick have answered my questions. Thanks. The problem is, I'm going to have to dig into a book or two. I don't know enough about Access yet to know how to relate the comments table to the main application. But, you have given me some insight to hopefully know where to look. The goods news is it appears that Access will do what I want it to do. Thanks! >But, you have given me some insight to hopefully know where >to look. Here are some other places: Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/accessjunkie/resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html A free tutorial written by Crystal (MS Access MVP): http://allenbrowne.com/casu-22.html MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] >> But, you have given me some insight to hopefully know where >> to look. [quoted text clipped - 14 lines] > -- > John W. Vinson [MVP] Thanks for the links, John. Can you point me to one that relates one table to another, as I have been told this application needs? I'm reading tutorials and an Access 2000 book, but I'm not keyed in on it yet. I know the solution is simple, but there is a lot to comprehend at first blush. >Thanks for the links, John. Can you point me to one that relates one >table to another, as I have been told this application needs? I'm pretty sure ALL of them would, at least somewhere; try Crystal's tutorial. In a thumbnail - every table should have a "Primary Key", a field or (sometimes) two or three or more fields, which uniquely identifies the record. If there is a table related one-to-many to this table, it would have a field of the same datatype as a "Foreign Key". For example, if you have a table of Schools and a table of Teachers, and every school has zero, one, or more teachers, and every teacher teaches at one and only one school, you might have tables like: Schools SchoolID <Autonumber, Primary Key> SchoolName Address City State Zip <other info about the school itself> Teachers TeacherID <Text, unique school-administration supplied ID> LastName FirstName <other biographical data> SchoolID <Long Integer> You would define a one to many relationship in the Relationships window by adding these two tables, and dragging the SchoolID from Schools to the SchoolID in Teachers. When you add a new teacher, you would need to specify a valid SchoolID (Access forms give a variety of tools to do so painlessly, you would never need to look up or type the autonumber, although that is certainly one possible way). The relationship would ensure that you could not assign a teacher to Hogwarts Acadamy, unless of course that esteemed institution did exist in your table of schools. SignatureJohn W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.