Customizing Tables and Forms?

[CCHD via AccessMonster.com]

Hi,

I need Help.
I’m trying to create an incident database. The table is called “Incident Logâ€.


I need the four fields of the table setup like this:

Incident # Patient # Patient # (2) Patient # (3) Patient # (4)
------------------------------------------------------------------------------
--------
0001-07 0001
0002-07
0003-07 0002 0003 0004 0005
0004-07 0006
0005-07 0007
0006-07
0007-07 0008 0009 0010 0011
0008-07 0012
0009-07 0013

…and so on

The “Incident #†needs to be generated automatically for every new record
entered and consists of a four digit number “0000†(up to 9999) then “-“ and
then two digits representing the current year “07†or “08†and so on. The
“Incident #†needs to reset to “0000†at the beginning of every year and the
“-07†should change to the current year automatically.

The “Patient #, (2), (3), (4)†needs consist of 4 digit number “0000†(up to
9999).
Since every incident logged does not have a patient, a patient number isn’t
always entered into the form each time a new incident is logged. (See example
above)
But the patient numbers should remain in sequential order. In the form the
next available patient number is displayed to the user and the user can
choose to enter the next Patient # or leave it blank depending on whether the
incident had a patient involved.

So far I have had no luck in creating this setup and I have little experience
with MS Access expression building and code.

Can all this be done?

Can anyone help?

Thank You

 

[John W. Vinson]

Hi,

I need Help.
I’m trying to create an incident database. The table is called “Incident Log”.


I need the four fields of the table setup like this:

Incident # Patient # Patient # (2) Patient # (3) Patient # (4)
------------------------------------------------------------------------------
--------
0001-07 0001
0002-07
0003-07 0002 0003 0004 0005
0004-07 0006
0005-07 0007
0006-07
0007-07 0008 0009 0010 0011
0008-07 0012
0009-07 0013

This is not a table. This is a spreadsheet. Having repeated Patient# fields
is simply WRONG design! Much better would be a "tall thin" design with two
tables, Incidents and PatientsInvolved, in a one to many relationship:

Incidents
IncidentID <don't use # in fieldnames>
IncidentDate
Description <memo field for narrative description of the incident>

PatientsInvolved
IncidentID <link to Incidents>
PatientID <link to a table of patients indicating who's involved>

This will let you enter as many patients as are needed - if an incident were
to involve FIVE patients, with your proposed design you would need to redesign
your table, redesign all your queries, reports, forms... ouch!

…and so on

The “Incident #” needs to be generated automatically for every new record
entered and consists of a four digit number “0000” (up to 9999) then “-“ and
then two digits representing the current year “07” or “08” and so on. The
“Incident #” needs to reset to “0000” at the beginning of every year and the
“-07” should change to the current year automatically.

This is called an "intelligent key" = not a compliment. Storing two pieces of
information (a sequential number and a date) in one field is generally a Bad
Idea. It can be done, and I've done it for compatibility with a deeply
entrenched manual system - but I'd certainly not do it on a new database! Just
store the date, as a date; store the sequential number, as a sequential
number. If you need to count incidents in a year (or a month or a quarter) use
a Totals Query.

The “Patient #, (2), (3), (4)” needs consist of 4 digit number “0000” (up to
9999).
Since every incident logged does not have a patient, a patient number isn’t
always entered into the form each time a new incident is logged. (See example
above)
But the patient numbers should remain in sequential order. In the form the
next available patient number is displayed to the user and the user can
choose to enter the next Patient # or leave it blank depending on whether the
incident had a patient involved.

You can use a sequential patient number in the PatientsInvolved table - but
will you never, ever under any circumstance have one patient involved in two
incidents? Is the identity of the patient irrelevant to this database?

So far I have had no luck in creating this setup and I have little experience
with MS Access expression building and code.

If you could describe something about the nature of the incidents you're
storing, perhaps we could suggest a properly normalized design... but what
you're proposing is emphatically NOT normalized. Perhaps you've fallen into
the very common trap of designing the tables based on the final output report
layout...?

John W. Vinson [MVP]

 

[CCHD via AccessMonster.com]

This is the way I need it.

All I need is the automatic numbering. It can either be displayed in a
textbox and then manually entered into a other textbox in the form by the
user or automatically entered into the Spreadsheet.

Is there a way to setup a feild that has a sequential numbering system?

Hi,

[quoted text clipped - 15 lines]

0008-07 0012
0009-07 0013

This is not a table. This is a spreadsheet. Having repeated Patient# fields
is simply WRONG design! Much better would be a "tall thin" design with two
tables, Incidents and PatientsInvolved, in a one to many relationship:

Incidents
IncidentID <don't use # in fieldnames>
IncidentDate
Description <memo field for narrative description of the incident>

PatientsInvolved
IncidentID <link to Incidents>
PatientID <link to a table of patients indicating who's involved>

This will let you enter as many patients as are needed - if an incident were
to involve FIVE patients, with your proposed design you would need to redesign
your table, redesign all your queries, reports, forms... ouch!

Â…and so on

[quoted text clipped - 3 lines]

“Incident #” needs to reset to “0000” at the beginning of every year and the
“-07” should change to the current year automatically.

This is called an "intelligent key" = not a compliment. Storing two pieces of
information (a sequential number and a date) in one field is generally a Bad
Idea. It can be done, and I've done it for compatibility with a deeply
entrenched manual system - but I'd certainly not do it on a new database! Just
store the date, as a date; store the sequential number, as a sequential
number. If you need to count incidents in a year (or a month or a quarter) use
a Totals Query.

The “Patient #, (2), (3), (4)” needs consist of 4 digit number “0000” (up to
9999).

[quoted text clipped - 5 lines]

choose to enter the next Patient # or leave it blank depending on whether the
incident had a patient involved.

You can use a sequential patient number in the PatientsInvolved table - but
will you never, ever under any circumstance have one patient involved in two
incidents? Is the identity of the patient irrelevant to this database?

So far I have had no luck in creating this setup and I have little experience
with MS Access expression building and code.

If you could describe something about the nature of the incidents you're
storing, perhaps we could suggest a properly normalized design... but what
you're proposing is emphatically NOT normalized. Perhaps you've fallen into
the very common trap of designing the tables based on the final output report
layout...?

John W. Vinson [MVP]

 

[John W. Vinson]

This is the way I need it.

Just bear in mind that data PRESENTATION and data STORAGE can be - and usually
are - different requirements. You may well need this final output appearance;
but that does NOT require that the data be *stored* in this way. In fact
storing it in this way will make your application inflexible and much harder
to adapt to any future changes. Your call!

All I need is the automatic numbering. It can either be displayed in a
textbox and then manually entered into a other textbox in the form by the
user or automatically entered into the Spreadsheet.

Is there a way to setup a feild that has a sequential numbering system?

Not in a Table, no; with VBA code in a Form, yes. You can use code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim vIncident As String
Dim iIncident As Integer
Dim strYear As String
strYear = Format(Date(), "yy")
vIncident = DMax("[Incident #]", "[tablename]", "[Incident #] LIKE '*-" _
& strYear & "'")
If IsNull(vIncident) Then
Me![Incident #] = "0001-" & strYear)
Else
iIncident = CInt(Left([Incident #], 4))
If iIncident = 9999 Then
Cancel = True
MsgBox "Go home, no more incidents this year", vbOKOnly
Else
Me![Incident #] = Format(iIncident + 1, "0000") & "-" & strYear
End If
End If
End Sub

You can do similar coding for the patient #.

John W. Vinson [MVP]

 

[Jamie Collins]

Just bear in mind that data PRESENTATION and data STORAGE can be - and usually
are - different requirements. You may well need this final output appearance;
but that does NOT require that the data be *stored* in this way.

One could justify a third class: logical, physical and presentation.
Example: a value may logically be a temporal value of type DATETIME,
be physical stored as a double floating point numeric and always be
presented as text.

Jamie.

--

 

[John W. Vinson]

One could justify a third class: logical, physical and presentation.
Example: a value may logically be a temporal value of type DATETIME,
be physical stored as a double floating point numeric and always be
presented as text.

Good point, Jamie! However a database designer doesn't have much control
(other than purchasing one or another DBMS) over the physical level. It's
certainly valuable to *know* how your engine stores data physically; it can
affect the choices one makes in designing queries. Dates are a classic
example.

John W. Vinson [MVP]