Date selected search of memo fields

[Rene]

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!

 

[Tim Ferguson]

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

 

[Rene']

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: ----

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

 

[Tim Ferguson]

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

 

[René]

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 !

 

[Tim Ferguson]

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

 

[René]

You guys are great by the way, This is a piece that I'm looking forward to tackling