Multiple Comments in Access 2003

[LCaddell]

I am creating a database to house patient information and there is a need for an insurance review screen where multiple people can add comments. The problem is I want each individual occurrence to be its own.
For instance:
02-28-2012 ABC Called insurance company

02-28-2012 XYZ Faxed clinical to insurance company

The only way I know how to do this would be to create a table with multiple fields of the same origin, meaning like this:
Date1
Initials1
Comments1

Date2
Initials2
Comments2

Date3
Initials3
Comments3

The problem is that I end up with a table 500 feet long and sometimes I only use one date, initial and comment "Block" while on others I may use 20-30.
So I guess my question is this:
How do I create multiple instances of basically the same fields without having a data table that is 500 feet long and is there a way to have the "Block" auto populate only when the inital "block" has been entered?
So for instance,
Date2
Initials2
Comments2
ONLY populates if
Date1
Initials1
Comments1
is filled in and
Date3
Initials3
Comments3
ONLY populates if
Date2
Initials2
Comments2
is filled in.

Does this make sense?

 

[LCaddell]

Next question.
If I use the Billing # (which uniquely identifies my patient) and create just one table for comments, couldnt I just have multiple records using the same billing ID?
For instance:
Billing ID: 123
Review Date: 02-28-2012
Initials: ABC
Comments: Called insurance company

NEW RECORD:
Billing ID: 123
Review Date: 02-28-2012
Initials: XYZ
Comments: Faxed information to insurance company

(Can you tell I am brand new to Access?

 

[PHV]

Your second post is the right one.
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[carmenlisa]

The short answer is that your second post is the answer.

I have had similar issues with History tables for marketing purposes where users want to see all contact that has existed with a customer. When calling on a customer or prospect, it is important to see the notes from previous interactions with who you are calling on. Some of the comments will tell us what to bring up in conversation, others will tell us what to avoid in conversation. Some will tell us if our prospect is married, where he/she has worked before, who they listen to for advice, and on and one.

I always used a separate table with fields for date, commenter, comment, CommentID (the primary key), and CustID (also keyed - a non-uniform key - for faster access for queries).

If you want to see all comments about a specific customer, query it that way. You can also look for all comments on a certain date, by a certain commenter.

Yes the table was usually the largest table in the system. With the CustID field, it was quite simple to show an entire history of contacts with any customer at any time. It could be sorted by date, by commenter, or by any other fields we wanted to use.

The issue I ran into was seeing the report of contacts either in a report or a form on screen. They individual entries were of varying lengths, some 3 words, others 10 paragraphs. Access wants to set up forms (with similar issues in reports) where you define exactly how high the field is on screen rather than having a size to fit option, equivalent to Excel's Size to fit height options for rows with varying amounts of data in them.

The necessary compromises either leave you with 3 word entries that use up 20 lines of blank space, or a long entry in a less tall field that you need to scroll through to see it all.

So I am telling you that yes, you need the table you are talking about and, yes it will have what may seem to be an absurd number of entries.

 

[PHV]

carmenlisa, you may use the CanShrink and CanGrow properties in a report.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LCaddell]

So my table would look like like this:

*Billing # | Date | Initials | Comments


with billing # being my primary key. Correct? Then when I pull a query it will grab all the notes for a specific patient based on the billing number, because that is the relationship between all my sheets. Everything leads back to billing #.

 

[PHV]

with billing # being my primary key
No, a Foreign Key (allowing duplicates) referencing your main table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886