"Increment Numeric Portion of a String" and joint Primary Key

[pietlinden]

I suspect this is a bad design, but it's not my database. I'm just
trying to make the thing work.

In the database, the two fields that make up the primary key of my
parent table are

Site (long integer) - combobox
PatientID (long integer) - textbox

What they want is to have each site have PatientID's 1-
(PatientCount). Before I set of a mad discussion, YES, I KNOW this is
a really stupid idea, but that's what they want).

The user has to select a site, and then I guess in the AfterUpdate
event of the Site combobox on the form, I set the PatientID from code
similar to Dev's example here:

="REC-" & right(DMax("FOO", "FOOTable"), _
Len(DMax("FOO", "FOOTable")) - _
InStr(1, DMax("FOO", "FOOTable"), "-")) + 1
Note: As the multiple calls to DMax function can slow down this
operation on a large table, I'd suggest against using such an
expression as DefaultValue. Instead, assign this new value to a hidden
control on your form which is bound to field FOO. This way you only
have to use DMax once. For example,

Private Sub SomeField_AfterUpdate()
Dim strMax as string
strMax =DMax("FOO", "FOOTable")
me!HiddenFooCtl = "REC-" & right(strMax, _
len(strMax) - _
Instr(1,strMax, "-")) +1
End Sub

from here: http://www.mvps.org/access/strings/str0007.htm

Okay, I must be stupid, but I do this in the BeforeInsert event of the
form, so that the assignment only takes place there? And then I just
trap for the Site being null and throw a message if it is, cancelling
the insert?

What's confusing about this database is that the structure is like
this:

tblA, tblB, tblC (all of these are "parent" level/top of the
hierarchy) and are joined
tblA---(1,1)---tblB----(1,1)----tblC

Then under that, there are the "child" tables

tblD, tblE, tblF

and then below that there are grandchild tables...

the screwy part comes from the 1:1 stuff at the top. It's just that a
lot of it is entirely optional. (choose route 1, fill in tables B, D,
and F, for example).

If I were to use an Autonumber primary key, this stuff would be cake,
but I can't do that. they want the keys to be "smart", like
<Site><PatientID>, like this:

300-0001
300-0002
150-0001
150-0002

So I was thinking use Dev's function to get the next ID for a patient,
given the site he's at, and go from there. It's just a PITA to
implement (maybe I'm just not seeing it!)

Either I'm blind or this is a royal pain and I'm doing it the wrong
way. It's a ridiculous way to build a database, but I have to do it
the way the boss wants it. Tried it the other way and it got
rejected. ("But they're not sequential for each site!")

Any ideas? (I have ADH 2002 Desktop in front of me (using A2003))...
so if there' s a page I neglected to read, just point it out. No need
to spend a lot of energy!

Thanks!

Pieter

 

[Douglas J. Steele]

Simply store two separate fields in the table: one for Site, one for
PatientID. Your DMax becomes much simpler then, and you're not storing data
redundantly (assuming you already have Site and PatientID fields in your
table), nor two pieces of data in a single field (assuming you don't).

If they insist of have the site-number concatenated key, do that via a
computed field in a query, and use the query wherever you would otherwise
have used the table.

 

[vieille-branche]

Simply store two separate fields in the table: one for Site, one for
PatientID. Your DMax becomes much simpler then, and you're not storing
data redundantly (assuming you already have Site and PatientID fields in
your table), nor two pieces of data in a single field (assuming you
don't).

If they insist of have the site-number concatenated key, do that via a
computed field in a query, and use the query wherever you would otherwise
have used the table.