 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / February 2004Date selected search of memo fields
One of my marketers had previously used a dbase which had what sounds to be unlimited memo fields for notes and/or comments. Apparently it automatically generated an ID corresponding with the ClientID. I would like to set up a contact/memo entry form where relevant comments regarding client care can be entered and that I would then be able to run a report where I could set the parameters by saying --- All comments from 1/1/04 to 2/1/04 etc and all comments containinng any dates contained there-in I'm not sure I understand what she is describing although if it works it would be a neat trick! "=?Utf-8?B?UmVuZQ==?=" <anonymous@discussions.microsoft.com> wrote in news:ED10CC98-F395-4C9D-A474-BA6BEDF92624@microsoft.com: > I would like to set up a contact/memo entry form where relevant > comments regarding client care can be entered and that I would then be > able to run a report where I could set the parameters by saying --- > All comments from 1/1/04 to 2/1/04 etc and all comments containinng > any dates contained there-in. The point about a memo is that it is, from the db engine's point of view, an unstructured bucket of ascii characters. You will not reasonably be able to query on the kind of thing you mention here. In any case, you don't need to. Since you have a relational database, use it relationally! Simply create a new table to hold the Comments. It would look like this: Comments ======== ContactID Long Integer, 1-M relationship with Contacts.ContactID DateCreated DateTime, DefaultValue = Date() Content Memo, could be a big Text field if you prefer Constraint pk Primary Key on (ContactID, CommentDate) Then it is easy to create a query on the Comments table, with a criterion like this: WHERE ContactID = 10993 AND DateCreated >= #2004-01-01# AND DateCreated < #2004-02-01# Hope that helps Tim F Ok, at least what I was asking made sense When I create the form, do I set it up as a subform from my main client form? My experience is very limited, but this seems to be the way to connect as is in the past when I have set up a unconnected though related form it tells me that I can save until a related record was made in the client table As you can tell I'm a newbie, what do you suggest for a resource where I see an example (visual learner etc) etc ----- Tim Ferguson wrote: ---- "=?Utf-8?B?UmVuZQ==?=" <anonymous@discussions.microsoft.com> wrote i news:ED10CC98-F395-4C9D-A474-BA6BEDF92624@microsoft.com: > I would like to set up a contact/memo entry form where relevan > comments regarding client care can be entered and that I would then b > able to run a report where I could set the parameters by saying --- > All comments from 1/1/04 to 2/1/04 etc and all comments containinn > any dates contained there-in. The point about a memo is that it is, from the db engine's point of view, an unstructured bucket of ascii characters. You will not reasonably be able to query on the kind of thing you mention here. In any case, you don't need to. Since you have a relational database, use it relationally! Simply create a new table to hold the Comments. It would look like this Comment ======= ContactID Long Integer, 1-M relationship with Contacts.ContactI DateCreated DateTime, DefaultValue = Date( Content Memo, could be a big Text field if you prefe Constraint pk Primary Key on (ContactID, CommentDate Then it is easy to create a query on the Comments table, with a criterion like this WHERE ContactID = 1099 AND DateCreated >= #2004-01-01 AND DateCreated < #2004-02-01 Hope that help Tim "=?Utf-8?B?UmVuZSc=?=" <anonymous@discussions.microsoft.com> wrote in news:89A231C4-2462-478B-AB1C-4BAA211A4497@microsoft.com: > When I create the form, do I set it up as a subform from my main > client form? You can do pretty much what best fits your methods of working (or your users', if you are doing this for someone else). For example, you might start off writing the comments and then want to choose which client to attach it to. Something like a combo box could do well for that. Alternatively, you may want to navigate to the client record first, and then have a command button to "Add Comment", which pops up a new dialog with the relevant fields already filled in. You could have a client record, with a comments subform; although I must admit to a personal dislike for subforms. The first point is to think through what seems natural to the way the work is done, not to the way the data are laid out in tables. If you see what I mean. > As you can tell I'm a newbie, what do you suggest for a resource where > I see an example (visual learner etc) etc. As far as I can remember, Northwind uses all these approaches in different places. For general ways of collecting data, look at Windows programs generally (dialogs, popup windows, etc); for database info gathering look at the way that other systems work, by peering over the counter at point- of-sale systems, personnel and management in work or in the post office, and so on. Make value judgements -- this one looks cheesy and contrived, while this one is easy and intuitive -- and work out why one is good (for you!) and the other is bad. Read books and website on UI design and layout: there are loads of these. Most of the techniques are reasonably easy to put into practice, or at least to get help to, but remember that Access is so flexible that it is as easy to build a crappy, difficult database as it is to make a good one. The difference is in the imagination and thoughtfulness of the designer!! Well, you did ask... <smile> All the best Tim F Thanks, I got several books the other night So that I can look it up, give me a hint on what it is that I am looking to do. The technical term etc You got me in a bad way with your comment about imagination and thoughtfulness ! :) "=?Utf-8?B?UmVuw6k=?=" <anonymous@discussions.microsoft.com> wrote in news:8D028526-8A77-49EB-AA6B-CAB4322669A6@microsoft.com: > So that I can look it up, give me a hint on what it is that I am > looking to do. The technical term etc. The overall heading is Systems Analysis -- this covers the whole process of investigating User Requirements and Data Flow and Business Processes, on to designing the Logical Model and finally the Physical Implementation. These are big terms that each have a separate department to manage them when you are designing a system to run ICI; but on the other hand even for smaller jobs they are phases your thinking should still go through. > You got me in a bad way with your comment about imagination and > thoughtfulness ! :) Oh dear: it was meant to be a bit cheering and optimistic! What I was trying to get at was that as long as you can have a clear idea about what you and your users need to achieve on screen, then the technical, programming stuff is relatively easy. And you can always get help with it. B wishes Tim F You guys are great by the way, This is a piece that I'm looking forward to tackling :) |
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.