Utilizing existing tables while expanding

[BillA]

I am hoping for some advice. I have built an access database with multiple
tables. The main tables are: tbl_Investigator (names, addresses of outside
investigators); tbl_Project (project information); tbl_Reviewer (names,
addresses of internal reviewers). These tables are joined in M:M
relationships by linking tables; lk_Join (joining InvestigatorID with
ProjectID) and lk_Reviews (linking ProjectID to ReviewerID). Within the
lk_Reviews table I have included fields for Comments, Comment date and the
relationship of the reviewer (to a particular project).

The lk_Reviews table is key in my db as it is linked to many reports and to
a Allen Browne's search form where only the most recent comment is presented.
As it is presently designed, the displayed project information only relates
to open, closed, and pending projects that have or are being evaluated for
approval.

Now the problem. I’ve been asked to create a new field that will track
updates to a project, specifically renewals, amendments and/or awaiting a
response. These new fields should contain “update,†comments, date and
reviewer (from tbl_Reviewer), will have a minimum of 0 and a maximum of, say
25 entries. These new fields will only come into play once a project has
been ‘opened’ (approved).

I would like to somehow tap into the existing lk_Reviews table and utilize
the already available comments, date and reviewer information, but I need to
separate the “update†field so as to not lose the ability to separate a
project under evaluation (pending) or closed. I would like to be able to
keep comments together, yet separated by status closed/pending and open.

I hope I’ve explained my situation well. Thank you.
Bill

 

[Larry Daugherty]

You haven't fully qualified *All* of your desies and constraints so it
would be risky to all concerned to advise you without knowing. Table
layouts and field names and datatypes would be necessary to be able to
completely comprehend the schema and some prose tied to that layout to
understand the whole question. There may be easy ways to conjoin
existing comments to new ones but you'd probably like to indicate
sources??

 

[BillA]

Hello Larry:
I am including the tables I spoke of, and the relevant fields within these
tables. As I mentioned on my initial post. I'm looking to introduce a new
field that will allow multiple "updates" once a project has been approved. I
was hoping to use the existing commenting capabilities found in the
"lk_Reviews" table, while keeping the application approval process and its
comments distinct from the future "updates."
Thank you for your interest and time.
Bill

Table: tbl_Investigator
InvestigatorID
Title
FirstName
LastName
Academic
Position
Organization
Address [etc…]

Table: lk_Join (joining table)
JoinID
(fk) InvestigatorID
(fk) ProjectID
InvestigatorRelationID (lookup)
ActiveParticipant? (Yes/No)
ConfidentialPledge (Yes/No)

Table: tbl_Project
ProjectID
RequestType
ProjectNumber
DateReceived
StudyTitle
StudyHasWebPage
ApprovalDate
StatusDescriptionID (lookup – open/closed/withdrawn/pending/denied)

Table: lk_Reviews (joining table)
ReviewID
(fk) ProjectID
(fk) ReviewerID
CommentDate
Comments
ReviewerRelationID (lookup)
FollowUpDate

Table: tbl_reviewer
ReviewerID
TitleOfCourtesy
CommentingON (exclusion field)
Title
FirstName
MiddleInitial
LastName
OrganizationName
Address [etc…]

 

[Larry Daugherty]

I still don't comprehend your issue thus I can't help you. Maybe
someone else will chime in or you could pose your issue differently in
a new thread.

Be aware that while comments may be stored in tables, "commenting
capabilities" don't grow out of the tables themselves. Tables are
just unordered heaps of data. That's the limit of their capabilities.

"Commenting capabilities" grow out of functionality you have add.

HTH
--
-Larry-
--

Hello Larry:
I am including the tables I spoke of, and the relevant fields within these
tables. As I mentioned on my initial post. I'm looking to introduce a new
field that will allow multiple "updates" once a project has been approved. I
was hoping to use the existing commenting capabilities found in the
"lk_Reviews" table, while keeping the application approval process and its
comments distinct from the future "updates."
Thank you for your interest and time.
Bill

Table: tbl_Investigator
InvestigatorID
Title
FirstName
LastName
Academic
Position
Organization
Address [etc.]

Table: lk_Join (joining table)
JoinID
(fk) InvestigatorID
(fk) ProjectID
InvestigatorRelationID (lookup)
ActiveParticipant? (Yes/No)
ConfidentialPledge (Yes/No)

Table: tbl_Project
ProjectID
RequestType
ProjectNumber
DateReceived
StudyTitle
StudyHasWebPage
ApprovalDate
StatusDescriptionID (lookup - open/closed/withdrawn/pending/denied)

Table: lk_Reviews (joining table)
ReviewID
(fk) ProjectID
(fk) ReviewerID
CommentDate
Comments
ReviewerRelationID (lookup)
FollowUpDate

Table: tbl_reviewer
ReviewerID
TitleOfCourtesy
CommentingON (exclusion field)
Title
FirstName
MiddleInitial
LastName
OrganizationName
Address [etc.]

You haven't fully qualified *All* of your desies and constraints so it
would be risky to all concerned to advise you without knowing. Table
layouts and field names and datatypes would be necessary to be able to
completely comprehend the schema and some prose tied to that layout to
understand the whole question. There may be easy ways to conjoin
existing comments to new ones but you'd probably like to indicate
sources??