Complex (for me) custom counter question....

Thanks Bruce, I understand how a PK works and how the tables link.
What I am looking for is something that distinguishes the PK in one
process table from the PK in another process table. Since the three
process tables are linked to the same measurement tables, they can't
all have an autonumber PK. For example, if the three process tables
had the a value in the PK of 7...how would an entry in MeasurementA
linked to the number 7 distinguish between processX and processY? To
fix this, I wanted to create a unique primary key with a combination of
a cutsom counter and a process abbreviation. I have figured out how to
do this, but you have told me that there is a better way. If you can
explain how to better to this, I would appreciate it greatly. I am
always in search of new (and especially better, more effecient) ways of
doing things. A life of learning for me.


Thanks,
Fletcher...

BruceM wrote:
A PK is unique. That's all. It can be text or number. It could probably
be other things, but these are the main ones. The point is that it is
unique. Looking back at your original description of your tables, I see
that you have PKs in the Data tables related to PKs in the Measurement
tables. That means you have a one-to-one relationship: each process can
have only one associated measurement record, and each measurement can be
associated with only one process. If that is what you intend, the PK will
be the same for the related record in each table. I'm not very familiar
with one-to-one relationships, not having found use for one so far, but I
believe you can use code to set the default value of MeasurementID to the
same number as ProcID
Remember, the numerical PK can be stored, and something else can be
displayed. For instance, in an unbound text box:

= "ProcXData" & Format(ProcXID,"000")

Again, you can create ProcXID in an event.

If Me.NewRecord Then
Me.ProcXID = DMax("ProcXID","ProcessXDataTable") +1
End If

You can use the form's Current event in a single-user environment, or you
can use Before Update, or the After Update event of a text box, or maybe the
form's Change event -- it depends on your needs, and on whether you need to
see the number when you start the record.

Remember, if two fields are related then releated records will contain the
same value in those fields. That's the point. Records are related when
then contain the same value in designated fields.

"Fletcher" wrote in message
ps.com...
So where does SRD and BDiff come into the picture? I thought they were
to
be part of the ID number. Are you saying that every ID number (counter)
in
ProcessXData starts with "PrcX", and is followed by an incremented
number?
If so, you absolutely do not need to store that text over and over.

Yes, that is exactly what I am saying. For a process table, the
abbreviation for the ID should be the abbreviation of that process.
How would I avoid storing the text over and over?

I'll re-describe the reason that I would like to do this. For example
I have 3 process tables and two measurement tables. Each process can
have either measurement preformed on it. So my problem is that if each
process table has a number field as PK, then the numbers will repeat in
the measurement tables and will not be correctly related back to the
process table. To fix that, I would like to have a PK that can relate
a measurement to it's process entry effectively and distinguish between
the processes. The PK that I have chosen too use is a combination of
process abbreviation and increasing number. As explained above, the
process abbreviation will be the same throughout a process table. If
you can explain how to do this without storing the text, that would be
awesome. I am always looking to learn new things about access.

As Jeff pointed out later in this thread, you could use a separate table
just for incrementing. I had not considered that option, but from what I
can figure out that is not what you want to do.

Correct...

BruceM wrote:
Responses inline.

"Fletcher" wrote in message
oups.com...
Well I've figured out one way to do it. I was having trouble with type
mismatching trying to do addition in a text field, so I'll tell you how
I did it.

I added a column to my table called DumbNumber. And a unbound textbox
on my form with a default as the abbreviation that I want. Then in
Form_Current I put a line like this:

Me.DataID = Left(Me.ProcessAbbr, 5) & (DMax("Number", "ProcessXData") +
1)


I don't see the point of the extra DumbNumber field.

This might have been one of the ways you described Bruce, but I don't
think I understood at first. If you can think of another way, I would
give it a shot as well.

Thanks for all your help,

Fletcher...

Fletcher wrote:
I can almost get this to work, but I think I need to explainmyself a
bit better apparently. I'll try harder this time.

Then you will need a table of abbreviations, or some other way of
assigning
an abbreviation to a process.

We would prefer that we don't put this table in, but rather just have
the code for the counter assign a constant abbreviation. That way, it
can't be changed accidentally. Even though it doesn't matter that
it's
constant and we simply desire a correlation between a given
measurement
of either type and the pass through a machine (i.e. *ProcessXData*),
we
would prefer that this abbreviation stays constant. If I have to use
a
table, I can do that...but I don't exactly want to go through and
track
down every single abbreviation that we use.

You are going to be in for a complex bit of coding if you want the
same
counter to span several tables, if it is even possible at all. You
would be
better off to use a single table, and to designate the record as
being
for
Process X, Y, or Z (by selecting an option button, or selecting the
process
from a list, or chicking a box, or whatever). Either that or you
will
need
to use a separate counter for each table.

Your last sentence was right on the money. That's exactly what I
would
like to do.

It would help if you provide an example of the numbering sequence
you
expect.

For example, I expect the counter for *ProcessXData* to go soemthing
like this:
PrcX1
PrcX2
PrcX3
And so on.
And for *ProcessYData* :
PrcY1
PrcY2
PrcY4
and the same for *ProcessZData*.

So where does SRD and BDiff come into the picture? I thought they were
to
be part of the ID number. Are you saying that every ID number (counter)
in
ProcessXData starts with "PrcX", and is followed by an incremented
number?
If so, you absolutely do not need to store that text over and over.


With the relationship, forms and subforms being used a given
measurement for any process will acquire the same entry for it's ID
field as it's corresponding Process pass.

Make the table's ID field Data Type (in table design view) Number
(Long
Integer).
Make a form based on the table.

The point of this post was to find a way to get away from the numeric
ID (because we can't have every entry in 3 different process tables
with numeric ID's).

There is no problem with numeric IDs from what you have stated so far.
However, if you need to perform math on text you could try looking up the
Val function.


You can concatenate this number with the department abbreviation in
an
unbound text box or in a query.

Unless this can be stored in the Process?Data.DataID field, it won't
help.

We really need an ID field with a unique identifying field, and the
only thing I can personally think of is the process abbr and a number.

What process abbreviation? Your example above shows the same prefix for
every ID number in a given table. Remember, we cannot see you database,
and
are not familiar with your line of business. "Process abbreviation" can
only mean what you have defined it to mean, and so far you have not
defined
it consistently.


I have no idea how to do this though. I imagine that it would have to
look at the previous entries and take off the abbreviation, then find
the maximum number, add one to it, and then put the abbreviation back
onto it. The only thing that I have trouble with is the coding.

The problem is very solvable, but I **need to know** about the prefixes.
See below, where I asked very specifically about that.


And thanks for taking the time to respond.

Fletcher....

BruceM wrote:
Responses inline.

"Fletcher" wrote in message
oups.com...
Bruce,
The abbreviation is derived from the Process name. Usually some
combination of letter or words that can identify the process. For
example, we have a Spin Rinse Dryer...we abbreviate that with SRD,
or
Boron Diffusion is Bdiff, or Gold Arsenic is Gars...it just
depends
on
what the process is.

Then you will need a table of abbreviations, or some other way of
assigning
an abbreviation to a process.

As for starting over at one, it doesn't really matter as long as
the
entries in *MeasurementsA* or *MeasurementsB* are related to a
given
entry in the *ProcessXData* or *ProcessYData* or *ProcessZData*.
We
really only care that a measurment can be attached to a pass
through
the machine. Chances are that the engineers won't even see this
field.

You are going to be in for a complex bit of coding if you want the
same
counter to span several tables, if it is even possible at all. You
would be
better off to use a single table, and to designate the record as
being
for
Process X, Y, or Z (by selecting an option button, or selecting the
process
from a list, or chicking a box, or whatever). Either that or you
will
need
to use a separate counter for each table.

As Jeff pointed out later in this thread, you could use a separate table
just for incrementing. I had not considered that option, but from what I
can figure out that is not what you want to do.


It would help if you provide an example of the numbering sequence
you
expect. If the first record is for SRD, the second for BDiff, the
third for
SRD, fourth SRD, and fifth BDiff, do you expect to see: SRD1,
BDiff1,
SRD2,
SRD3, BDiff2, or do you expect: SRD1, BDiff2, SRD3, SRD4, BDiff5?
If
the
former, I think you will need to store the abbreviation as part of
the
number. If the latter, you could just use DMax. Make the table's
ID
field
Data Type (in table design view) Number (Long Integer). Make a form
based
on the table. In the form's Current event:

How to proceed depends upon your answer to the question in the preceding
paragraph, and/or the question about whether the prefix is the same for
every ID number in a given table.


If Me.NewRecord Then
Me.YourID = Nz(DMax("YourID", "tblYourTable") + 1, 1)
End If

You can concatenate this number with the department abbreviation in
an
unbound text box or in a query.
Note that Nz is necessary only for the first record. It may not be
necessary at all if the default value for the field is 0, but I'm
not
sure.

If you need the number to be, say, five digits each time:
Me.YourID = Format(Nz(DMax("YourID", "tblYourTable") + 1,
1),"0000")


What is the difference between Dmax and DCount?

DMax is the highest value. DCount is the total number of records.
If
you
have five records (the last one being numbered 5) and delete the
fourth
one,
the record count will go down to 4. Adding one to that will make it
5,
which you already have. DMax is the highest value. If you have
five
records and delete the fourth one, the highest-numbered record will
still be
5. Adding one to that will produce a unique number.

Jeff,
I tried your code in Form_Current. I'm having some problems with
it
though. Maybe you can help. I put it in very similarly to what
you
had (I changed the generic names back to the real names), but I
don't
have a Me.ProcessName, so I changed that section to what I wanted
it
to
be (BDiff, one of the abbr.'s exampled above). The problem is
that
first it didn't want to start counting, so I put the first entry
in
the
table with BDiff1. Then it started counting, but it left off the
BDiff
and started putting only numbers in. Any idea how to fix this?

Thanks to you both.

Fletcher...

BruceM wrote:
You're must be specific. How is the abbreviation derived?
Knowing
that
you
want to specify it in the form's code etc. tells me nothing. If
later
personnel change it, do you expect the number to start over from
1
with
the
new abbreviation, or what? Jeff wrote a suggestion along the
lines
of
what
I would have suggested, except I would have used DMax rather than
DCount.

"Fletcher" wrote in message
oups.com...
Is the abbreviation for the process the first five letters of
the
process
name, or is it derived otherwise?
The abbreviation is derived otherwise. I would like to specify
it
in
the forms code and and have it commented so that later
personnel
will
know where to change it.

Also, do you want the numbers to start from one for each
process?
Yes, I do want them to start from one and be sequential.

Thanks,

Fletcher...

BruceM wrote:
Is the abbreviation for the process the first five letters of
the
process
name, or is it derived otherwise? Also, do you want the
numbers
to
start
from one for each process? That is, is the number First001,
First002,
etc.
and Second001, Second 002, etc., or is it First001, First002,
Second003,
Second004, etc.?

"Fletcher" wrote in message
oups.com...
Hi, I would like to set up a custom counter for processes in
my
facility. I'll list my table structure first, then explain:

*ProcessXData*
DataID, where I want the custom counter, PK
Spec, number
ProcessDateTime, date/time, Now()
Operator, text
Comments, memo

*ProcessYData*
DataID, where I want the custom counter, PK
Spec, number
ProcessDateTime, date/time, Now()
Operator, text
Comments, memo

*ProcessZData*
DataID, where I want the custom counter, PK
Spec, number
ProcessDateTime, date/time, Now()
Operator, text
Comments, memo

*MeasurementsA*
MeasAID, text, PK (linked to DataID)
Top, number
Middle, Number
Bottom, Number

*MeasurementsB*
MeasBID, text, PK (linked to DataID)
MeasData, number
Limit, number

(for simplicity, I lave left out several tables that hold
lists
of
data
that provide information such as operators and specs; I have
also
generic-ized the data to make it easier for you, and not
disclose
any
information that could lead to problems)

So, here's the meat of what I'm having trouble doing. I
have 3
different processes and each can have a different spec.
For
certain
specs on each process, a different measurement is done
(MeasurementA
or
MeasurementB). I have this worked out on a form where
depending on
the
spec number that you enter, a different subform for
measurments
will
be
enabled. My dilema is that I can't use autonumber for each
process
as
an ID, so I would like to creat a custom counter that
includes,
say,
the abreviation of process name (4 or 5 letters) and a
number.
I
cannot think of how to do this, so if anyone would be
helpful
enough
to
give me some input, I would appreciate it greatly.

Thanks ahead,
Fletcher...

The custom counters are applied across several databases.
That is why we use still use a custom counter. But originally
custom counters were designed that way to handle high
concurrency data entry (search for 'high concurrency on
the MS web site)

I mention the manual process because, if you can't use
DRI, as we can't in this case, you should be prepared
for possible errors.

(david)


"Jamie Collins" wrote in message
oups.com...

david epsom dot com dot au wrote:
We also have a manual process that scans the multiple
tables to correct any synchronisation errors between the
counter table and the data tables.

Why not database constraints to *prevent* synchronisation errors?

Jamie.

--

david epsom dot com dot au wrote:
search for 'high concurrency on
the MS web site

Concurrency? Yeah, I think I've heard of that. Legal tender among
prisoners, right ;-)

Jamie.

--