Forms and Reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use an Access database to keep track of my projects, and to report the
status of each to my super. In my form I have a comment section where I put
updates. As I add updates the comment section can get quite long. When I
create a report, I would like to include only the last comment for each
project (my comments are dated). Any suggestions how I might do this?
 
Hi Dennis

The best way would be to create a separate, related table for comments. It
needs only three fields - ProjectID, CommentDate, and CommentText. Then,
each time you add a comment, you simply add a new record to the table.

You can include on your report only the comment with the latest date. Also,
you han review comments in either ascending or descending chronological
order, or select only those within a certain date range. You could also add
other fields, such as comment type, or importance, and select on those
fields.
 
Graham,
Thanks for the info. I have added a separate table for comments, and have it
as a subform on my input form. I'm still having problems, though, in getting
the latest comments to print out in the report. My database is setup as
follows:

I have several tables (programs, design engineer, year, status, priority,
etc.) that feed into a larger table called Work Requests. As I work on the
different projects, I enter a string of comments (using your suggestion). I
created two queries to break requests into either program work, or methodolgy
work. The reports I print out are for open program requests and open
methodolgy requests.

Taking the program work report as an example, the query searches the
database for requests with an "open" status and programs that are not
methodology, "<>Methods". In addition, I have the query sort the remaining
requests by priority (an integer value). This gives me a prioritized list of
open, non-methods, projects. When I try to extract the last comment for the
report, the only way I see to do this is by adding additional criteria to the
query. Perhaps I am being dense, but when I try to do this I end up with only
one request - the one with the latest comment for ALL of the requests.

Can you offer some suggetions?

Dennis
 
Hi Dennis

Does your Comments table have a primary key? If not, then add an AutoNumber
field CommentID.

Now, you can add a subquery expression to your query:
... where CommentID in (Select top 1 CommentID from Comments as C where
C.RequestID = Requests.RequestID order by C.CommentDate DESC)
 
Graham,
Thanks again for the information. I did have a primary key in my Comments
table and used the subquery as you suggested. It took me awhile to figure it
out (I know a lot more about SQL than I did before), but everything is
working as planned. Now I'm going to expand the database so it will plug data
into templates I'll create for formal reports when the projects are completed.

Dennis

Graham Mandeno said:
Hi Dennis

Does your Comments table have a primary key? If not, then add an AutoNumber
field CommentID.

Now, you can add a subquery expression to your query:
... where CommentID in (Select top 1 CommentID from Comments as C where
C.RequestID = Requests.RequestID order by C.CommentDate DESC)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Dennis _M said:
Graham,
Thanks for the info. I have added a separate table for comments, and have
it
as a subform on my input form. I'm still having problems, though, in
getting
the latest comments to print out in the report. My database is setup as
follows:

I have several tables (programs, design engineer, year, status, priority,
etc.) that feed into a larger table called Work Requests. As I work on the
different projects, I enter a string of comments (using your suggestion).
I
created two queries to break requests into either program work, or
methodolgy
work. The reports I print out are for open program requests and open
methodolgy requests.

Taking the program work report as an example, the query searches the
database for requests with an "open" status and programs that are not
methodology, "<>Methods". In addition, I have the query sort the remaining
requests by priority (an integer value). This gives me a prioritized list
of
open, non-methods, projects. When I try to extract the last comment for
the
report, the only way I see to do this is by adding additional criteria to
the
query. Perhaps I am being dense, but when I try to do this I end up with
only
one request - the one with the latest comment for ALL of the requests.

Can you offer some suggetions?

Dennis
Click to expand...
Click to expand...
 
Back
Top