Last entry +1 help....Reset when desired.

[Fletcher]

I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

 

[Graham Mandeno]

Hi Fletcher

I'm not certain I understand you, but I figure you mean something like a
countdown timer (except it's counting up) so that when it reaches 20 the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you want to
lock, set Locked to fLock and/or Enabled to Not fLock.

 

[Fletcher]

Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Hi Fletcher

I'm not certain I understand you, but I figure you mean something like a
countdown timer (except it's counting up) so that when it reaches 20 the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

 

[strive4peace]

Hi Fletcher,

you will need to use a form to enter data so you can trap events

I will assume that your data structure looks something like this:

*Equipment*
EquipID, autonumber
Equipment, text
etc

*Passes*
PassID, autonumber
EquipID, Long Integer
PassDate, date
Pass_Number, integer
etc

*Recharges*
RechargeID, autonumber
EquipID, Long Integer
ReDate, date
etc


If your structure is different, please give details of how your tables
are set up so that we can effectively help you

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Hi Fletcher

I'm not certain I understand you, but I figure you mean something like a
countdown timer (except it's counting up) so that when it reaches 20 the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

 

[Fletcher]

So since you're replying to so many of my posts, I should explain that
I don't really know what all of what you wrote above means. I'm pretty
new at this access stuff and most of what I'm doing is learn as you go
kind of thing. So I'll ask some questions to further my understanding.

When you say:

*Equipment*
EquipID, autonumber
Equipment, text
etc

Does that show a table? If that is the case, I only have two tables
for now.

To follow your lead I have (if I'm correct):

*FSI_Particle_Data*
Date/TimeOut, Now()
Operator, text
Pass_Number, number (this is what I would like to count from 1 to 20
in)
FSI_Number, number
Pre_Meas, number
Post_Meas, number
RunNumber1, number
RunNumber2, number
..
..
Run Number6, number
Comments, memo

*FSI_Number*
FSI_Number, number
(this table exists simply to give a combobox on the form somewhere to
look up equipment number)

Also, this is related to my other post about combining columns in a
query (which works very well by the way).

What I want to do is to have a box on the form that looks at the last
pass number and adds one to it, and when it hits 20, I have it set up
to lock everything in the form except for the "reset" button. I would
like this reset button to set the pass number back to 1 and re-enable
the form so that data entrance can continue.

I'm sure this is something simple and that I can handle it, but it is
avoiding me as of yet.

Thanks for all your help,

Fletcher

Hi Fletcher,

you will need to use a form to enter data so you can trap events

I will assume that your data structure looks something like this:

*Equipment*
EquipID, autonumber
Equipment, text
etc

*Passes*
PassID, autonumber
EquipID, Long Integer
PassDate, date
Pass_Number, integer
etc

*Recharges*
RechargeID, autonumber
EquipID, Long Integer
ReDate, date
etc


If your structure is different, please give details of how your tables
are set up so that we can effectively help you

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Hi Fletcher

I'm not certain I understand you, but I figure you mean something like a
countdown timer (except it's counting up) so that when it reaches 20 the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

 

[Fletcher]

Maybe there's a simpler way to do this though....I've been playing with
it and I think that if I knew how to have this text box look up it's
last entry and add one to it, and I knew how to have a button that set
that value back to one, I could do it.

So lets say I have one text box called PassNumber. I would like it to
look at it's last entry and for the current entry add one to that. And
lets say that I have a command button called ResetCmd. I would like
this button to set the value of PassNumber to 1.

I think that is the easiest way I can think of to solve this problem.

I don't think the table structure should come into play on this. If
I'm wrong or am misleading myself, please inform me because I really am
inexperienced with this.

I hope you're not getting frustrated with me, and if you are I'm sorry.

Thanks for all your help again.

Fletcher...

So since you're replying to so many of my posts, I should explain that
I don't really know what all of what you wrote above means. I'm pretty
new at this access stuff and most of what I'm doing is learn as you go
kind of thing. So I'll ask some questions to further my understanding.

When you say:

*Equipment*
EquipID, autonumber
Equipment, text
etc

Does that show a table? If that is the case, I only have two tables
for now.

To follow your lead I have (if I'm correct):

*FSI_Particle_Data*
Date/TimeOut, Now()
Operator, text
Pass_Number, number (this is what I would like to count from 1 to 20
in)
FSI_Number, number
Pre_Meas, number
Post_Meas, number
RunNumber1, number
RunNumber2, number
.
.
Run Number6, number
Comments, memo

*FSI_Number*
FSI_Number, number
(this table exists simply to give a combobox on the form somewhere to
look up equipment number)

Also, this is related to my other post about combining columns in a
query (which works very well by the way).

What I want to do is to have a box on the form that looks at the last
pass number and adds one to it, and when it hits 20, I have it set up
to lock everything in the form except for the "reset" button. I would
like this reset button to set the pass number back to 1 and re-enable
the form so that data entrance can continue.

I'm sure this is something simple and that I can handle it, but it is
avoiding me as of yet.

Thanks for all your help,

Fletcher

Hi Fletcher,

you will need to use a form to enter data so you can trap events

I will assume that your data structure looks something like this:

*Equipment*
EquipID, autonumber
Equipment, text
etc

*Passes*
PassID, autonumber
EquipID, Long Integer
PassDate, date
Pass_Number, integer
etc

*Recharges*
RechargeID, autonumber
EquipID, Long Integer
ReDate, date
etc


If your structure is different, please give details of how your tables
are set up so that we can effectively help you

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Graham Mandeno wrote:
Hi Fletcher

I'm not certain I understand you, but I figure you mean something like a
countdown timer (except it's counting up) so that when it reaches 20 the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

 

[Graham Mandeno]

Hi Fletcher

I'm afraid that the design of your table has set some major alarm bells
jangling.

Fields such as:

RunNumber1, number
RunNumber2, number
.
.
Run Number6, number

are an indication that some fundamental design rules have been broken.

There is an important principle of database design called "normalization".
There are a number of very good links on normalization put together by MVP
Jeff Conrad here:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

While the rules may seem a little stringent to a beginner, I assure you that
they have been developed over the years in order to avoid problems and make
things *work*

So, it appears you have a number of *FSIs* (whatever they may be) and each
one may be used to perform a number of *Passes*, each pass is performed by
an *Operator*, and consists of a number of *Runs*. Also, each FSI can
undergo a number of *Recharges*.

So, we have just defined the need for FIVE tables:

*FSIs* contains ONE record for each FSI. This contains ONLY data about the
actual piece of equipment - perhaps this might include a serial number,
purchase date, location, colour, etc. Each record must have a "primary
key". This is a field containing a unique value which identifies each FSI.
If you already have a system of unique FSI numbers, then fine - use that as
the primary key.

*Operators* contains ONE record for each operator. This contains ONLY data
about the person - perhaps this might include first name, last name,
employee number, address, name of spouse, etc. Each of these records must
also have a primary key. If you already have a system of unique employee
numbers, then fine - use that as the primary key, otherwise it is common to
use an "AutoNumber" field which generates unique numbers which don't ever
need to be seen by a user (call this "OperatorID").

*Passes* contains ONE record for each pass. This contains ONLY data about
the pass, including the date and time, the FSI_Number, the OperatorID,
Pre_Meas, Post_Meas, comments - but NOT the individual run results. This
table needs a primary key also, and an AutoNumber would be suitable (call
this PassID).

*Runs* contains ONE record for each run. This contains ONLY data about the
run (are you seeing the pattern here? <g>), including the PassID (linking it
back to a pass record) and a RunNumber, and also field(s) to store the
result(s) of that run. PassID and RunNumber together should be a unique
index (to avoid duplicates) and this could also be the primary key for this
table.

Now, each time a pass is performed, you use a form to create a record in
*Passes* with the date and time. You select the FSI and the Operator from
combo boxes and enter the values for the other fields. Then you specify the
number of runs for that pass (default 6?) and this created that number of
related numbered records in *Runs* which can be displayed in a subform for
you to enter the run results.

OK so far??

I haven't yet mentioned the *Recharges* table. This (you guessed it!)
contains ONE record for each recharge and contains ONLY data about the
recharge, including FSI_Number, recharge date and time, perhaps OperatorID
of the person doing the recharge, and other fields. Just for good measure
we'll add a primary key - an autonumber "RechargeID".

Now, you are no doubt saying: "What happened to the Pass_Number field which
started this whole thread?"

The answer is that it is unnecessary. We can easily ascertain, with a
simple query, when any given FSI was last recharged. We can also use a
query to count the number of passes that FSI has performed since that
recharge. This is your Pass_Number, and it entirely takes care of itself!!

I've probably given you more than enough to digest here for the moment. If
you decide to bite the bullet and go for a redesign (and I STRONGLY
recommend you do!) then post a message back here and I or one of the many
other experts in this group will gladly give you all the help you need to
get back on the "straight and narrow".
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

So since you're replying to so many of my posts, I should explain that
I don't really know what all of what you wrote above means. I'm pretty
new at this access stuff and most of what I'm doing is learn as you go
kind of thing. So I'll ask some questions to further my understanding.

When you say:

*Equipment*
EquipID, autonumber
Equipment, text
etc

Does that show a table? If that is the case, I only have two tables
for now.

To follow your lead I have (if I'm correct):

*FSI_Particle_Data*
Date/TimeOut, Now()
Operator, text
Pass_Number, number (this is what I would like to count from 1 to 20
in)
FSI_Number, number
Pre_Meas, number
Post_Meas, number
RunNumber1, number
RunNumber2, number
.
.
Run Number6, number
Comments, memo

*FSI_Number*
FSI_Number, number
(this table exists simply to give a combobox on the form somewhere to
look up equipment number)

Also, this is related to my other post about combining columns in a
query (which works very well by the way).

What I want to do is to have a box on the form that looks at the last
pass number and adds one to it, and when it hits 20, I have it set up
to lock everything in the form except for the "reset" button. I would
like this reset button to set the pass number back to 1 and re-enable
the form so that data entrance can continue.

I'm sure this is something simple and that I can handle it, but it is
avoiding me as of yet.

Thanks for all your help,

Fletcher

Hi Fletcher,

you will need to use a form to enter data so you can trap events

I will assume that your data structure looks something like this:

*Equipment*
EquipID, autonumber
Equipment, text
etc

*Passes*
PassID, autonumber
EquipID, Long Integer
PassDate, date
Pass_Number, integer
etc

*Recharges*
RechargeID, autonumber
EquipID, Long Integer
ReDate, date
etc


If your structure is different, please give details of how your tables
are set up so that we can effectively help you

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Graham Mandeno wrote:
Hi Fletcher

I'm not certain I understand you, but I figure you mean something like
a
countdown timer (except it's counting up) so that when it reaches 20
the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the
counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you
want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to
do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

 

[Fletcher]

Thank you Graham for all your helpful information. I don't know that
everything that you have said is necessary for my database, but I
believe that some of it would help. I'll do my best to explain why I
do or do not think that what you told me is necessary. Please don't
take this as a blow to your person.

I'll start by saying that your proposed tables for FSIs and Operators
would be pointless in our facility. Mainly because we don't desire -
yet - to have information on those stored electronically, but it may
happen in the future. Although, I may look at creating an FSI table
now for reasons that I'll mention later.

Your proposed table called *Passes* is basically what I already have.
It includes the pass number, which is what I would like to count from 1
to 20 and then start over (and is what started this thread); it
includes the date/time, which I have condensed to one entry instead of
two; it includes the operator ID, which in our facility is simply
initials; it contains the pre_meas and post_meas; it also contains
comments. This table also brings us to the problem. You tell me that
I need to have a unique pass number for each pass, and I say that I
only want numbers 1 to 20. I'll come back to this.

To continue, I don't really see a point in a having a table for
Recharges. Again simply because we don't care to have infomation
stored about this process. We only desire to know that it happened.
Which we would know when the pass number entry resets to 1.

What I am interested in is having a table for Passes and a table for
runs linked because we can have multiple runs in a single pass and we
would like to later be able to search for a given run and see all the
information about it. This takes us back to the table for passes and
the table for FSIs...

I would like to have the three table set up similar to what you've
explained. I would like to have the Pass Table that you described,
and the Run table that you described, and I believe that this set up
will also require the FSI table you described. Perhaps you can help me
with this. I'll explain further.

I would like to have:
*FSI*
FSI_ID, Number (we only have 3 machines, so these would be 1, 2, or 3)

*Runs*
Pass_ID, Number (to link to *Pass* coming up)
Run_Number, Number (5digit ID number that remains constant throughout
each step in the facility)

*Pass*
Pass_ID, ? (I'm reluctant to use autonumber)
Pass_Number (Numbers 1 through 20, unless there is some other way to
display on a form how manny passes have been done since recharge?)
Date/TimeOut, Date/Time, Now()
FSI_ID, Number (to link to *FSI*)
Operator, Text (2 or 3 letter initials of operator simply to know who
ran the pass)
Pre, Number
Post, Number
Comments

Now, in your description of *Runs* you said that Pass_ID and Run_Number
could be combined to form a primary key? I am not sure how to do this.
And I am most definitely not sure how to link the tables as everyone
talks about. I hope I have satisfied you by deciding to revamp my
database. If you could help me through this, I would be most
apreciative. Thank you.

One more question though. If I were to do this, what would happen if a
given "Run" went through a FSI more than once?

Fletcher.

Hi Fletcher

I'm afraid that the design of your table has set some major alarm bells
jangling.

Fields such as:

RunNumber1, number
RunNumber2, number
.
.
Run Number6, number

are an indication that some fundamental design rules have been broken.

There is an important principle of database design called "normalization".
There are a number of very good links on normalization put together by MVP
Jeff Conrad here:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

While the rules may seem a little stringent to a beginner, I assure you that
they have been developed over the years in order to avoid problems and make
things *work*

So, it appears you have a number of *FSIs* (whatever they may be) and each
one may be used to perform a number of *Passes*, each pass is performed by
an *Operator*, and consists of a number of *Runs*. Also, each FSI can
undergo a number of *Recharges*.

So, we have just defined the need for FIVE tables:

*FSIs* contains ONE record for each FSI. This contains ONLY data about the
actual piece of equipment - perhaps this might include a serial number,
purchase date, location, colour, etc. Each record must have a "primary
key". This is a field containing a unique value which identifies each FSI.
If you already have a system of unique FSI numbers, then fine - use that as
the primary key.

*Operators* contains ONE record for each operator. This contains ONLY data
about the person - perhaps this might include first name, last name,
employee number, address, name of spouse, etc. Each of these records must
also have a primary key. If you already have a system of unique employee
numbers, then fine - use that as the primary key, otherwise it is common to
use an "AutoNumber" field which generates unique numbers which don't ever
need to be seen by a user (call this "OperatorID").

*Passes* contains ONE record for each pass. This contains ONLY data about
the pass, including the date and time, the FSI_Number, the OperatorID,
Pre_Meas, Post_Meas, comments - but NOT the individual run results. This
table needs a primary key also, and an AutoNumber would be suitable (call
this PassID).

*Runs* contains ONE record for each run. This contains ONLY data about the
run (are you seeing the pattern here? <g>), including the PassID (linking it
back to a pass record) and a RunNumber, and also field(s) to store the
result(s) of that run. PassID and RunNumber together should be a unique
index (to avoid duplicates) and this could also be the primary key for this
table.

Now, each time a pass is performed, you use a form to create a record in
*Passes* with the date and time. You select the FSI and the Operator from
combo boxes and enter the values for the other fields. Then you specify the
number of runs for that pass (default 6?) and this created that number of
related numbered records in *Runs* which can be displayed in a subform for
you to enter the run results.

OK so far??

I haven't yet mentioned the *Recharges* table. This (you guessed it!)
contains ONE record for each recharge and contains ONLY data about the
recharge, including FSI_Number, recharge date and time, perhaps OperatorID
of the person doing the recharge, and other fields. Just for good measure
we'll add a primary key - an autonumber "RechargeID".

Now, you are no doubt saying: "What happened to the Pass_Number field which
started this whole thread?"

The answer is that it is unnecessary. We can easily ascertain, with a
simple query, when any given FSI was last recharged. We can also use a
query to count the number of passes that FSI has performed since that
recharge. This is your Pass_Number, and it entirely takes care of itself!!

I've probably given you more than enough to digest here for the moment. If
you decide to bite the bullet and go for a redesign (and I STRONGLY
recommend you do!) then post a message back here and I or one of the many
other experts in this group will gladly give you all the help you need to
get back on the "straight and narrow".
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

So since you're replying to so many of my posts, I should explain that
I don't really know what all of what you wrote above means. I'm pretty
new at this access stuff and most of what I'm doing is learn as you go
kind of thing. So I'll ask some questions to further my understanding.

When you say:

*Equipment*
EquipID, autonumber
Equipment, text
etc

Does that show a table? If that is the case, I only have two tables
for now.

To follow your lead I have (if I'm correct):

*FSI_Particle_Data*
Date/TimeOut, Now()
Operator, text
Pass_Number, number (this is what I would like to count from 1 to 20
in)
FSI_Number, number
Pre_Meas, number
Post_Meas, number
RunNumber1, number
RunNumber2, number
.
.
Run Number6, number
Comments, memo

*FSI_Number*
FSI_Number, number
(this table exists simply to give a combobox on the form somewhere to
look up equipment number)

Also, this is related to my other post about combining columns in a
query (which works very well by the way).

What I want to do is to have a box on the form that looks at the last
pass number and adds one to it, and when it hits 20, I have it set up
to lock everything in the form except for the "reset" button. I would
like this reset button to set the pass number back to 1 and re-enable
the form so that data entrance can continue.

I'm sure this is something simple and that I can handle it, but it is
avoiding me as of yet.

Thanks for all your help,

Fletcher

Hi Fletcher,

you will need to use a form to enter data so you can trap events

I will assume that your data structure looks something like this:

*Equipment*
EquipID, autonumber
Equipment, text
etc

*Passes*
PassID, autonumber
EquipID, Long Integer
PassDate, date
Pass_Number, integer
etc

*Recharges*
RechargeID, autonumber
EquipID, Long Integer
ReDate, date
etc


If your structure is different, please give details of how your tables
are set up so that we can effectively help you

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Fletcher wrote:
Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Graham Mandeno wrote:
Hi Fletcher

I'm not certain I understand you, but I figure you mean something like
a
countdown timer (except it's counting up) so that when it reaches 20
the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the
counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you
want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to
do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

 

[Fletcher]

I figured out how to do multiple primary keys. If that's what you
meant for me to do. If it's not, let me know.

Thank you Graham for all your helpful information. I don't know that
everything that you have said is necessary for my database, but I
believe that some of it would help. I'll do my best to explain why I
do or do not think that what you told me is necessary. Please don't
take this as a blow to your person.

I'll start by saying that your proposed tables for FSIs and Operators
would be pointless in our facility. Mainly because we don't desire -
yet - to have information on those stored electronically, but it may
happen in the future. Although, I may look at creating an FSI table
now for reasons that I'll mention later.

Your proposed table called *Passes* is basically what I already have.
It includes the pass number, which is what I would like to count from 1
to 20 and then start over (and is what started this thread); it
includes the date/time, which I have condensed to one entry instead of
two; it includes the operator ID, which in our facility is simply
initials; it contains the pre_meas and post_meas; it also contains
comments. This table also brings us to the problem. You tell me that
I need to have a unique pass number for each pass, and I say that I
only want numbers 1 to 20. I'll come back to this.

To continue, I don't really see a point in a having a table for
Recharges. Again simply because we don't care to have infomation
stored about this process. We only desire to know that it happened.
Which we would know when the pass number entry resets to 1.

What I am interested in is having a table for Passes and a table for
runs linked because we can have multiple runs in a single pass and we
would like to later be able to search for a given run and see all the
information about it. This takes us back to the table for passes and
the table for FSIs...

I would like to have the three table set up similar to what you've
explained. I would like to have the Pass Table that you described,
and the Run table that you described, and I believe that this set up
will also require the FSI table you described. Perhaps you can help me
with this. I'll explain further.

I would like to have:
*FSI*
FSI_ID, Number (we only have 3 machines, so these would be 1, 2, or 3)

*Runs*
Pass_ID, Number (to link to *Pass* coming up)
Run_Number, Number (5digit ID number that remains constant throughout
each step in the facility)

*Pass*
Pass_ID, ? (I'm reluctant to use autonumber)
Pass_Number (Numbers 1 through 20, unless there is some other way to
display on a form how manny passes have been done since recharge?)
Date/TimeOut, Date/Time, Now()
FSI_ID, Number (to link to *FSI*)
Operator, Text (2 or 3 letter initials of operator simply to know who
ran the pass)
Pre, Number
Post, Number
Comments

Now, in your description of *Runs* you said that Pass_ID and Run_Number
could be combined to form a primary key? I am not sure how to do this.
And I am most definitely not sure how to link the tables as everyone
talks about. I hope I have satisfied you by deciding to revamp my
database. If you could help me through this, I would be most
apreciative. Thank you.

One more question though. If I were to do this, what would happen if a
given "Run" went through a FSI more than once?

Fletcher.

Hi Fletcher

I'm afraid that the design of your table has set some major alarm bells
jangling.

Fields such as:

RunNumber1, number
RunNumber2, number
.
.
Run Number6, number

are an indication that some fundamental design rules have been broken.

There is an important principle of database design called "normalization".
There are a number of very good links on normalization put together by MVP
Jeff Conrad here:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

While the rules may seem a little stringent to a beginner, I assure you that
they have been developed over the years in order to avoid problems and make
things *work*

So, it appears you have a number of *FSIs* (whatever they may be) and each
one may be used to perform a number of *Passes*, each pass is performed by
an *Operator*, and consists of a number of *Runs*. Also, each FSI can
undergo a number of *Recharges*.

So, we have just defined the need for FIVE tables:

*FSIs* contains ONE record for each FSI. This contains ONLY data about the
actual piece of equipment - perhaps this might include a serial number,
purchase date, location, colour, etc. Each record must have a "primary
key". This is a field containing a unique value which identifies each FSI.
If you already have a system of unique FSI numbers, then fine - use that as
the primary key.

*Operators* contains ONE record for each operator. This contains ONLY data
about the person - perhaps this might include first name, last name,
employee number, address, name of spouse, etc. Each of these records must
also have a primary key. If you already have a system of unique employee
numbers, then fine - use that as the primary key, otherwise it is common to
use an "AutoNumber" field which generates unique numbers which don't ever
need to be seen by a user (call this "OperatorID").

*Passes* contains ONE record for each pass. This contains ONLY data about
the pass, including the date and time, the FSI_Number, the OperatorID,
Pre_Meas, Post_Meas, comments - but NOT the individual run results. This
table needs a primary key also, and an AutoNumber would be suitable (call
this PassID).

*Runs* contains ONE record for each run. This contains ONLY data about the
run (are you seeing the pattern here? <g>), including the PassID (linking it
back to a pass record) and a RunNumber, and also field(s) to store the
result(s) of that run. PassID and RunNumber together should be a unique
index (to avoid duplicates) and this could also be the primary key for this
table.

Now, each time a pass is performed, you use a form to create a record in
*Passes* with the date and time. You select the FSI and the Operator from
combo boxes and enter the values for the other fields. Then you specify the
number of runs for that pass (default 6?) and this created that number of
related numbered records in *Runs* which can be displayed in a subform for
you to enter the run results.

OK so far??

I haven't yet mentioned the *Recharges* table. This (you guessed it!)
contains ONE record for each recharge and contains ONLY data about the
recharge, including FSI_Number, recharge date and time, perhaps OperatorID
of the person doing the recharge, and other fields. Just for good measure
we'll add a primary key - an autonumber "RechargeID".

Now, you are no doubt saying: "What happened to the Pass_Number field which
started this whole thread?"

The answer is that it is unnecessary. We can easily ascertain, with a
simple query, when any given FSI was last recharged. We can also use a
query to count the number of passes that FSI has performed since that
recharge. This is your Pass_Number, and it entirely takes care of itself!!

I've probably given you more than enough to digest here for the moment. If
you decide to bite the bullet and go for a redesign (and I STRONGLY
recommend you do!) then post a message back here and I or one of the many
other experts in this group will gladly give you all the help you need to
get back on the "straight and narrow".
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

So since you're replying to so many of my posts, I should explain that
I don't really know what all of what you wrote above means. I'm pretty
new at this access stuff and most of what I'm doing is learn as you go
kind of thing. So I'll ask some questions to further my understanding.

When you say:
*Equipment*
EquipID, autonumber
Equipment, text
etc
Does that show a table? If that is the case, I only have two tables
for now.

To follow your lead I have (if I'm correct):

*FSI_Particle_Data*
Date/TimeOut, Now()
Operator, text
Pass_Number, number (this is what I would like to count from 1 to 20
in)
FSI_Number, number
Pre_Meas, number
Post_Meas, number
RunNumber1, number
RunNumber2, number
.
.
Run Number6, number
Comments, memo

*FSI_Number*
FSI_Number, number
(this table exists simply to give a combobox on the form somewhere to
look up equipment number)

Also, this is related to my other post about combining columns in a
query (which works very well by the way).

What I want to do is to have a box on the form that looks at the last
pass number and adds one to it, and when it hits 20, I have it set up
to lock everything in the form except for the "reset" button. I would
like this reset button to set the pass number back to 1 and re-enable
the form so that data entrance can continue.

I'm sure this is something simple and that I can handle it, but it is
avoiding me as of yet.

Thanks for all your help,

Fletcher

strive4peace wrote:
Hi Fletcher,

you will need to use a form to enter data so you can trap events

I will assume that your data structure looks something like this:

*Equipment*
EquipID, autonumber
Equipment, text
etc

*Passes*
PassID, autonumber
EquipID, Long Integer
PassDate, date
Pass_Number, integer
etc

*Recharges*
RechargeID, autonumber
EquipID, Long Integer
ReDate, date
etc


If your structure is different, please give details of how your tables
are set up so that we can effectively help you

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Fletcher wrote:
Sorry if I wasn't clear. I'll try again.

Actually, I don't want it to be time dependent. But thanks for the
effort.

We have this piece of equipment that we have to "recharge" after at
most 20 "passes" (which it rarely gets to because we normally recharge
it at 18 or 19 passes). I would like this text box on my form to tie
into a Pass_Number column on the table. It would be awesome if I could
set this text box to look at the previous entry and add 1 to it and
then reset to 1 when the "reset" button is clicked. The lock down at
20 is simply a precaution to tell operators that it's time to recharge
even though they already know that. It's simply a safeguard and a
record keeping tool.

Thanks for all your help. I hope I explained it better.

Graham Mandeno wrote:
Hi Fletcher

I'm not certain I understand you, but I figure you mean something like
a
countdown timer (except it's counting up) so that when it reaches 20
the
form is locked.

If I'm correct, then use the form's Timer event and the TimerInterval
property.

Something like this:

In Form_Current:
Call cmdReset_Click

In cmdReset_Click:
Call LockForm False
txtCounter = 1
Me.TimerInterval = 1000
(note that the unit is milliseconds, so this will increment the
counter once
every second. You can alter that as required)

In Form_Timer:
txtCounter = txtCounter + 1
If txtCounter = 20 then
Me.TimerInterval = 0 ' stop the clock
Call LockForm True
End If

And in Private Sub LockForm(fLock as Boolean), for each control you
want to
lock, set Locked to fLock and/or Enabled to Not fLock.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a number field on a form that I would like to count up to 20
from one for each entry. This is probalby something easy, but is
avoiding me at the moment. The catch is that I would like a command
button to reset it even if it hasn't reached 20 yet. Also, when it
does reach 20, I have it set up to lock the form except for this
"reset" button. If anywone would mind explaining me an easy way to
do
this (code is not scary for me), I would apreiciate it greatly.

Thanks,

Fletcher.

 

[Graham Mandeno]

Hi Fletcher

I'll answer you questions and points inline...

Thank you Graham for all your helpful information. I don't know that
everything that you have said is necessary for my database, but I
believe that some of it would help. I'll do my best to explain why I
do or do not think that what you told me is necessary. Please don't
take this as a blow to your person.

Hey - it takes a LOT more than that to offend me!

I'll start by saying that your proposed tables for FSIs and Operators
would be pointless in our facility. Mainly because we don't desire -
yet - to have information on those stored electronically, but it may
happen in the future. Although, I may look at creating an FSI table
now for reasons that I'll mention later.

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time

There are two reasons why I would still recommend an Operators table:
1. it makes data entry easier, because you can use a combo box which
does autocomplete to select the operator
2. it prevents errors - for example, typing the initials HM when you
mean GM

It doesn't need to be complex:
OperatorID - autonumber, primary key
OpName - text, no duplicates
OpInits - text, no duplicates (if you need this for a short field in
forms/reports)

Your proposed table called *Passes* is basically what I already have.
It includes the pass number, which is what I would like to count from 1
to 20 and then start over (and is what started this thread); it
includes the date/time, which I have condensed to one entry instead of
two; it includes the operator ID, which in our facility is simply
initials; it contains the pre_meas and post_meas; it also contains
comments. This table also brings us to the problem. You tell me that
I need to have a unique pass number for each pass, and I say that I
only want numbers 1 to 20. I'll come back to this.

You DO need a unique pass number for each pass, because that's the only way
to link the pass with the runs for that pass. If it's not unique, then how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass 13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?

You DO NOT need a counter for the number of passes since the last recharge,
as the number of pass records since that date can easily be counted.

To continue, I don't really see a point in a having a table for
Recharges. Again simply because we don't care to have infomation
stored about this process. We only desire to know that it happened.
Which we would know when the pass number entry resets to 1.

Fine - if you have no need to record recharge history then all you need is a
LastRecharge field in the FSIs table (see above).

However, when someone comes to you in the future and says: "Can you tell me
how many times this FSI has been recharged in the last year?", then I would
be the first to say "I told you so!"

What I am interested in is having a table for Passes and a table for
runs linked because we can have multiple runs in a single pass and we
would like to later be able to search for a given run and see all the
information about it. This takes us back to the table for passes and
the table for FSIs...

I would like to have the three table set up similar to what you've
explained. I would like to have the Pass Table that you described,
and the Run table that you described, and I believe that this set up
will also require the FSI table you described. Perhaps you can help me
with this. I'll explain further.

I would like to have:
*FSI*
FSI_ID, Number (we only have 3 machines, so these would be 1, 2, or 3)

*Runs*
Pass_ID, Number (to link to *Pass* coming up)
Run_Number, Number (5digit ID number that remains constant throughout
each step in the facility)

*Pass*
Pass_ID, ? (I'm reluctant to use autonumber)
Pass_Number (Numbers 1 through 20, unless there is some other way to
display on a form how manny passes have been done since recharge?)
Date/TimeOut, Date/Time, Now()
FSI_ID, Number (to link to *FSI*)
Operator, Text (2 or 3 letter initials of operator simply to know who
ran the pass)
Pre, Number
Post, Number
Comments

Now, in your description of *Runs* you said that Pass_ID and Run_Number
could be combined to form a primary key? I am not sure how to do this.

The easiest way is to open the table in design view, select BOTH the fields,
and then click the primary key button on the toolbar.

And I am most definitely not sure how to link the tables as everyone
talks about. I hope I have satisfied you by deciding to revamp my
database. If you could help me through this, I would be most
apreciative. Thank you.

Click on Tools>Relationships. Then you can add tables to the window
(View>show tables) and drag the mouse from the primary key of one table to
the related field (foreign key) of another.

One more question though. If I were to do this, what would happen if a
given "Run" went through a FSI more than once?

Not quite sure what you mean by this. I think you'll need to explain a bit
more about exactly what a run is, and what an FSI does.

 

[Fletcher]

Okay, this is probably the most productive discussion I've had about
this since I've begun. Thank you Graham. I'll answer your questions
and add some of my own to the mix. And I'll concede to the operators
table...I think it's a good idea now that you explained it more.

Firstly, I'll explain an FSI and Runs like you asked at the end of your
last post.
An FSI is a cleaning device that can clean up to 6 runs (aka lots to
some people, it just depends on your preference, but everyone who works
here understands either) which all have a unique run number associated
with it that is carried throughout the facility. A run is also a group
of 25 potential products.

So that can bring us to my last question: What would happen if a run
number went through an FSI more than once (e.g. it was cleaned more
than once, which happens very regularly)? I believe that it wouldn't
matter since the PassID and RunNumber will both be primary keys and
therefore I will never have a single run number going through the same
pass and never creating a duplicate entry in *Runs*. As well as the
chance that it will go through the same peice of machinery (FSI).

I hope that's enough. I'm not really allowed to disclose what it is
that we do because of government contracts. Sorry that I can't go more
in depth.

Now, to move back into the discusion:

You DO need a unique pass number for each pass, because that's the only way
to link the pass with the runs for that pass. If it's not unique, then how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass 13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?

I'll put a unique identifier, but I am very reluctant to use auto
number because I've found that it can become slightly messy if edits or
deletions are necessary. Do you have any suggestions on this? Also,
how would I calculate the number of passes since the last recharge and
display this value on a form (which was the ultimate goal of this
thread, and I am determined to figure this out)?

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time

Check, but how do I tell it (or have it retrieve) what the last
recharge date was?

And I'm just starting to run this through my head: how do I keep track
of the need for each FSI to be recharged. Since there are 3 machines
and the information for every pass on all 3 are stored in the same
table, how will I sort out which FSI has had 5 passes since recharge
and which has had 19 since recharge?

Here is my updated table structure:
*Passes*
PassID, Number, Primary Key, link to *Runs*>PassID
FSINumber, Number, link to *FSIs*>FSINumber
DateTimeOut, Date/Time, default Now()
Operator, Text, Link to *Operators*> (I'm not sure if I should link
this to peratorID or OperatorInitials)
Pre, Number
Post, Number
Comment, Memo

*Operators*
OperatorID, AutoNumber, (maybe) link to *Passes*>Operator
OperatorName, text
OperatorInitials, text (maybe) link to *Passes*>Operator

*Runs*
PassID, Number, link to *Passes*>PassID
RunNumber, Number

*FSIs*
FSINumber, Number, link to *Passes*>FSINumber
RCHGDate, date/time

If you see anything wrong, please point it out. And could you tell me
which link would be better on *operators*?

Thanks for your help, it's greatly apreciated.

Hi Fletcher

I'll answer you questions and points inline...

Thank you Graham for all your helpful information. I don't know that
everything that you have said is necessary for my database, but I
believe that some of it would help. I'll do my best to explain why I
do or do not think that what you told me is necessary. Please don't
take this as a blow to your person.

Hey - it takes a LOT more than that to offend me!

I'll start by saying that your proposed tables for FSIs and Operators
would be pointless in our facility. Mainly because we don't desire -
yet - to have information on those stored electronically, but it may
happen in the future. Although, I may look at creating an FSI table
now for reasons that I'll mention later.

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time

There are two reasons why I would still recommend an Operators table:
1. it makes data entry easier, because you can use a combo box which
does autocomplete to select the operator
2. it prevents errors - for example, typing the initials HM when you
mean GM

It doesn't need to be complex:
OperatorID - autonumber, primary key
OpName - text, no duplicates
OpInits - text, no duplicates (if you need this for a short field in
forms/reports)

Your proposed table called *Passes* is basically what I already have.
It includes the pass number, which is what I would like to count from 1
to 20 and then start over (and is what started this thread); it
includes the date/time, which I have condensed to one entry instead of
two; it includes the operator ID, which in our facility is simply
initials; it contains the pre_meas and post_meas; it also contains
comments. This table also brings us to the problem. You tell me that
I need to have a unique pass number for each pass, and I say that I
only want numbers 1 to 20. I'll come back to this.

You DO need a unique pass number for each pass, because that's the only way
to link the pass with the runs for that pass. If it's not unique, then how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass 13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?

You DO NOT need a counter for the number of passes since the last recharge,
as the number of pass records since that date can easily be counted.

To continue, I don't really see a point in a having a table for
Recharges. Again simply because we don't care to have infomation
stored about this process. We only desire to know that it happened.
Which we would know when the pass number entry resets to 1.

Fine - if you have no need to record recharge history then all you need is a
LastRecharge field in the FSIs table (see above).

However, when someone comes to you in the future and says: "Can you tell me
how many times this FSI has been recharged in the last year?", then I would
be the first to say "I told you so!"

What I am interested in is having a table for Passes and a table for
runs linked because we can have multiple runs in a single pass and we
would like to later be able to search for a given run and see all the
information about it. This takes us back to the table for passes and
the table for FSIs...

I would like to have the three table set up similar to what you've
explained. I would like to have the Pass Table that you described,
and the Run table that you described, and I believe that this set up
will also require the FSI table you described. Perhaps you can help me
with this. I'll explain further.

I would like to have:
*FSI*
FSI_ID, Number (we only have 3 machines, so these would be 1, 2, or 3)

*Runs*
Pass_ID, Number (to link to *Pass* coming up)
Run_Number, Number (5digit ID number that remains constant throughout
each step in the facility)

*Pass*
Pass_ID, ? (I'm reluctant to use autonumber)
Pass_Number (Numbers 1 through 20, unless there is some other way to
display on a form how manny passes have been done since recharge?)
Date/TimeOut, Date/Time, Now()
FSI_ID, Number (to link to *FSI*)
Operator, Text (2 or 3 letter initials of operator simply to know who
ran the pass)
Pre, Number
Post, Number
Comments

Now, in your description of *Runs* you said that Pass_ID and Run_Number
could be combined to form a primary key? I am not sure how to do this.

The easiest way is to open the table in design view, select BOTH the fields,
and then click the primary key button on the toolbar.

And I am most definitely not sure how to link the tables as everyone
talks about. I hope I have satisfied you by deciding to revamp my
database. If you could help me through this, I would be most
apreciative. Thank you.

Click on Tools>Relationships. Then you can add tables to the window
(View>show tables) and drag the mouse from the primary key of one table to
the related field (foreign key) of another.

One more question though. If I were to do this, what would happen if a
given "Run" went through a FSI more than once?

Not quite sure what you mean by this. I think you'll need to explain a bit
more about exactly what a run is, and what an FSI does.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Fletcher

Glad you're slowly coming around to my way of thinking ;-)

First, your fears about autonumbers are unfounded. You never need to edit
them, as they only serve as a way of linking records on a one-to-many basis.
Also, there is no problem deleting a record with an autonumber primary key,
UNLESS it has related records in another table, in which case you would want
to prevent their deletion anyway.

On the problem of counting passes since the last recharge, the following
query will return two columns showing the FSINumber and the number of
passes:

Select FSIs.FSINumber, (Select Count(*) from Passes
where Passes.FSINumber=FSIs.FSINumber
and Passes.DateTimeOut>FSIs.RchgDate) AS NumberOfPasses
from FSIs;

You could save this as a query (qryNumberOfPasses) and then retrieve the
number wherever it is required - for example, on your form where you enter
details about a pass, you could have a textbox (txtNumberOfPasses) with this
ControlSource:
=DLookup("NumberOfPasses", "qryNumberOfPasses",
"FSINumber=" & [FSINumber])

Now, more on design...

Let me get this straight... One FSI can clean up to six runs in one pass,
and can perform up to 20 passes before it needs to be recharged. Correct?

It sounds to me like your Runs table should be holding information about the
Runs (RunNumber and perhaps RunDescription) and you need a separate table
(RunPasses?) to record which runs were "done" in each pass.

Something like this:

*Runs*
RunNumber: numeric, long integer, primary key
RunDescription: text

*RunPasses*
(Both these fields would combine to make the PK.)
PassID: numeric, long integer, FK to Passes.PassID
RunNumber: numeric, long integer, FK to Runs.RunNumber
(and any other data pertaining to the results of that run, if necessary)


(Note that FK means "foreign key" - the "child" side of a relationship.)

Also, on the question about OperatorID vs OperatorInits as the related
field, a relationship must always be with a PK, so if you have an autonumber
(OperatorID) as your PK then the FK in Passes must be numeric (long integer)
and if you have text (inits) as your PK then you must have a text field as
your FK.

There's no strong reason not to use OperatorInits as your PK, except that it
might cause problems if the initials change for any reason ( e.g. change of
name).
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Okay, this is probably the most productive discussion I've had about
this since I've begun. Thank you Graham. I'll answer your questions
and add some of my own to the mix. And I'll concede to the operators
table...I think it's a good idea now that you explained it more.

Firstly, I'll explain an FSI and Runs like you asked at the end of your
last post.
An FSI is a cleaning device that can clean up to 6 runs (aka lots to
some people, it just depends on your preference, but everyone who works
here understands either) which all have a unique run number associated
with it that is carried throughout the facility. A run is also a group
of 25 potential products.

So that can bring us to my last question: What would happen if a run
number went through an FSI more than once (e.g. it was cleaned more
than once, which happens very regularly)? I believe that it wouldn't
matter since the PassID and RunNumber will both be primary keys and
therefore I will never have a single run number going through the same
pass and never creating a duplicate entry in *Runs*. As well as the
chance that it will go through the same peice of machinery (FSI).

I hope that's enough. I'm not really allowed to disclose what it is
that we do because of government contracts. Sorry that I can't go more
in depth.

Now, to move back into the discusion:

You DO need a unique pass number for each pass, because that's the only
way
to link the pass with the runs for that pass. If it's not unique, then
how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass
13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?

I'll put a unique identifier, but I am very reluctant to use auto
number because I've found that it can become slightly messy if edits or
deletions are necessary. Do you have any suggestions on this? Also,
how would I calculate the number of passes since the last recharge and
display this value on a form (which was the ultimate goal of this
thread, and I am determined to figure this out)?

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time

Check, but how do I tell it (or have it retrieve) what the last
recharge date was?

And I'm just starting to run this through my head: how do I keep track
of the need for each FSI to be recharged. Since there are 3 machines
and the information for every pass on all 3 are stored in the same
table, how will I sort out which FSI has had 5 passes since recharge
and which has had 19 since recharge?

Here is my updated table structure:
*Passes*
PassID, Number, Primary Key, link to *Runs*>PassID
FSINumber, Number, link to *FSIs*>FSINumber
DateTimeOut, Date/Time, default Now()
Operator, Text, Link to *Operators*> (I'm not sure if I should link
this to peratorID or OperatorInitials)
Pre, Number
Post, Number
Comment, Memo

*Operators*
OperatorID, AutoNumber, (maybe) link to *Passes*>Operator
OperatorName, text
OperatorInitials, text (maybe) link to *Passes*>Operator

*Runs*
PassID, Number, link to *Passes*>PassID
RunNumber, Number

*FSIs*
FSINumber, Number, link to *Passes*>FSINumber
RCHGDate, date/time

If you see anything wrong, please point it out. And could you tell me
which link would be better on *operators*?

Thanks for your help, it's greatly apreciated.

Hi Fletcher

I'll answer you questions and points inline...

Thank you Graham for all your helpful information. I don't know that
everything that you have said is necessary for my database, but I
believe that some of it would help. I'll do my best to explain why I
do or do not think that what you told me is necessary. Please don't
take this as a blow to your person.

Hey - it takes a LOT more than that to offend me!

I'll start by saying that your proposed tables for FSIs and Operators
would be pointless in our facility. Mainly because we don't desire -
yet - to have information on those stored electronically, but it may
happen in the future. Although, I may look at creating an FSI table
now for reasons that I'll mention later.

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time

There are two reasons why I would still recommend an Operators table:
1. it makes data entry easier, because you can use a combo box which
does autocomplete to select the operator
2. it prevents errors - for example, typing the initials HM when you
mean GM

It doesn't need to be complex:
OperatorID - autonumber, primary key
OpName - text, no duplicates
OpInits - text, no duplicates (if you need this for a short field in
forms/reports)

Your proposed table called *Passes* is basically what I already have.
It includes the pass number, which is what I would like to count from 1
to 20 and then start over (and is what started this thread); it
includes the date/time, which I have condensed to one entry instead of
two; it includes the operator ID, which in our facility is simply
initials; it contains the pre_meas and post_meas; it also contains
comments. This table also brings us to the problem. You tell me that
I need to have a unique pass number for each pass, and I say that I
only want numbers 1 to 20. I'll come back to this.

You DO need a unique pass number for each pass, because that's the only
way
to link the pass with the runs for that pass. If it's not unique, then
how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass
13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?

You DO NOT need a counter for the number of passes since the last
recharge,
as the number of pass records since that date can easily be counted.

To continue, I don't really see a point in a having a table for
Recharges. Again simply because we don't care to have infomation
stored about this process. We only desire to know that it happened.
Which we would know when the pass number entry resets to 1.

Fine - if you have no need to record recharge history then all you need
is a
LastRecharge field in the FSIs table (see above).

However, when someone comes to you in the future and says: "Can you tell
me
how many times this FSI has been recharged in the last year?", then I
would
be the first to say "I told you so!"

What I am interested in is having a table for Passes and a table for
runs linked because we can have multiple runs in a single pass and we
would like to later be able to search for a given run and see all the
information about it. This takes us back to the table for passes and
the table for FSIs...

I would like to have the three table set up similar to what you've
explained. I would like to have the Pass Table that you described,
and the Run table that you described, and I believe that this set up
will also require the FSI table you described. Perhaps you can help me
with this. I'll explain further.

I would like to have:
*FSI*
FSI_ID, Number (we only have 3 machines, so these would be 1, 2, or 3)

*Runs*
Pass_ID, Number (to link to *Pass* coming up)
Run_Number, Number (5digit ID number that remains constant throughout
each step in the facility)

*Pass*
Pass_ID, ? (I'm reluctant to use autonumber)
Pass_Number (Numbers 1 through 20, unless there is some other way to
display on a form how manny passes have been done since recharge?)
Date/TimeOut, Date/Time, Now()
FSI_ID, Number (to link to *FSI*)
Operator, Text (2 or 3 letter initials of operator simply to know who
ran the pass)
Pre, Number
Post, Number
Comments

Now, in your description of *Runs* you said that Pass_ID and Run_Number
could be combined to form a primary key? I am not sure how to do this.

The easiest way is to open the table in design view, select BOTH the
fields,
and then click the primary key button on the toolbar.

And I am most definitely not sure how to link the tables as everyone
talks about. I hope I have satisfied you by deciding to revamp my
database. If you could help me through this, I would be most
apreciative. Thank you.

Click on Tools>Relationships. Then you can add tables to the window
(View>show tables) and drag the mouse from the primary key of one table
to
the related field (foreign key) of another.

One more question though. If I were to do this, what would happen if a
given "Run" went through a FSI more than once?

Not quite sure what you mean by this. I think you'll need to explain a
bit
more about exactly what a run is, and what an FSI does.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Fletcher]

Hey Graham, I just want to say thanks one more time for spending so
much time on this with me. You're the only person who has actually sat
down and explained to me how this is supposed to work. So, thank you.

Back to work now. I see what you mean about the autonumbers, and I
will use them in some cases. Although, I am still slightly nervous
about using them. I did not know about linking an autonumber to a
number or a text to a text, so that is good information to know.

Let me get this straight... One FSI can clean up to six runs in one pass,
and can perform up to 20 passes before it needs to be recharged. Correct?

Yes, that is correct. But also, a run can go through an FSI more than
once, and not necessarily through the same FSI or with the same runs it
went through with before.

It sounds to me like your Runs table should be holding information about the
Runs (RunNumber and perhaps RunDescription) and you need a separate table
(RunPasses?) to record which runs were "done" in each pass.

Storing this information about the runs would be unnecessary since the
information is stored on a "Runcard" that accompanies the "run" through
the facility. I think I see what is happening here though. Let me
take a shot at it:

When I build a form, I will have a main form that has *Passes* as it's
record source and a subform that has *Runs* as it's record source, and
when the Information for Passes is entered, I can enter as many runs as
necessary on the subformand the link between the two will be PassID,
and therefore the PassID will be carried over to each run? Also, on
the main form, I will have a combo box for FSINumber and
OperatorInitials that look up from *FSIs* and *Operators*. Am I good
so far? I think so.

This brings me back to the likelyhood of a run going through an FSI
more than once. Since the primary keys in *Runs* is both PassID and
RunNumber, will having more than one RunNumber with a different PassID
for each create a problem, or will access recognize it as a different
entry because it has a different pass? Does that make sense at all?
I hope so.

I think we're getting closer to the end of our discussion, which makes
me happy. I hope other people can read this and get an idea of what is
necessary to get the hang of this database building. Thanks again,

Fletcher...

Hi Fletcher

Glad you're slowly coming around to my way of thinking ;-)

First, your fears about autonumbers are unfounded. You never need to edit
them, as they only serve as a way of linking records on a one-to-many basis.
Also, there is no problem deleting a record with an autonumber primary key,
UNLESS it has related records in another table, in which case you would want
to prevent their deletion anyway.

On the problem of counting passes since the last recharge, the following
query will return two columns showing the FSINumber and the number of
passes:

Select FSIs.FSINumber, (Select Count(*) from Passes
where Passes.FSINumber=FSIs.FSINumber
and Passes.DateTimeOut>FSIs.RchgDate) AS NumberOfPasses
from FSIs;

You could save this as a query (qryNumberOfPasses) and then retrieve the
number wherever it is required - for example, on your form where you enter
details about a pass, you could have a textbox (txtNumberOfPasses) with this
ControlSource:
=DLookup("NumberOfPasses", "qryNumberOfPasses",
"FSINumber=" & [FSINumber])

Now, more on design...

Let me get this straight... One FSI can clean up to six runs in one pass,
and can perform up to 20 passes before it needs to be recharged. Correct?

It sounds to me like your Runs table should be holding information about the
Runs (RunNumber and perhaps RunDescription) and you need a separate table
(RunPasses?) to record which runs were "done" in each pass.

Something like this:

*Runs*
RunNumber: numeric, long integer, primary key
RunDescription: text

*RunPasses*
(Both these fields would combine to make the PK.)
PassID: numeric, long integer, FK to Passes.PassID
RunNumber: numeric, long integer, FK to Runs.RunNumber
(and any other data pertaining to the results of that run, if necessary)


(Note that FK means "foreign key" - the "child" side of a relationship.)

Also, on the question about OperatorID vs OperatorInits as the related
field, a relationship must always be with a PK, so if you have an autonumber
(OperatorID) as your PK then the FK in Passes must be numeric (long integer)
and if you have text (inits) as your PK then you must have a text field as
your FK.

There's no strong reason not to use OperatorInits as your PK, except that it
might cause problems if the initials change for any reason ( e.g. change of
name).
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Okay, this is probably the most productive discussion I've had about
this since I've begun. Thank you Graham. I'll answer your questions
and add some of my own to the mix. And I'll concede to the operators
table...I think it's a good idea now that you explained it more.

Firstly, I'll explain an FSI and Runs like you asked at the end of your
last post.
An FSI is a cleaning device that can clean up to 6 runs (aka lots to
some people, it just depends on your preference, but everyone who works
here understands either) which all have a unique run number associated
with it that is carried throughout the facility. A run is also a group
of 25 potential products.

So that can bring us to my last question: What would happen if a run
number went through an FSI more than once (e.g. it was cleaned more
than once, which happens very regularly)? I believe that it wouldn't
matter since the PassID and RunNumber will both be primary keys and
therefore I will never have a single run number going through the same
pass and never creating a duplicate entry in *Runs*. As well as the
chance that it will go through the same peice of machinery (FSI).

I hope that's enough. I'm not really allowed to disclose what it is
that we do because of government contracts. Sorry that I can't go more
in depth.

Now, to move back into the discusion:

You DO need a unique pass number for each pass, because that's the only
way
to link the pass with the runs for that pass. If it's not unique, then
how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass
13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?

I'll put a unique identifier, but I am very reluctant to use auto
number because I've found that it can become slightly messy if edits or
deletions are necessary. Do you have any suggestions on this? Also,
how would I calculate the number of passes since the last recharge and
display this value on a form (which was the ultimate goal of this
thread, and I am determined to figure this out)?

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time

Check, but how do I tell it (or have it retrieve) what the last
recharge date was?

And I'm just starting to run this through my head: how do I keep track
of the need for each FSI to be recharged. Since there are 3 machines
and the information for every pass on all 3 are stored in the same
table, how will I sort out which FSI has had 5 passes since recharge
and which has had 19 since recharge?

Here is my updated table structure:
*Passes*
PassID, Number, Primary Key, link to *Runs*>PassID
FSINumber, Number, link to *FSIs*>FSINumber
DateTimeOut, Date/Time, default Now()
Operator, Text, Link to *Operators*> (I'm not sure if I should link
this to peratorID or OperatorInitials)
Pre, Number
Post, Number
Comment, Memo

*Operators*
OperatorID, AutoNumber, (maybe) link to *Passes*>Operator
OperatorName, text
OperatorInitials, text (maybe) link to *Passes*>Operator

*Runs*
PassID, Number, link to *Passes*>PassID
RunNumber, Number

*FSIs*
FSINumber, Number, link to *Passes*>FSINumber
RCHGDate, date/time

If you see anything wrong, please point it out. And could you tell me
which link would be better on *operators*?

Thanks for your help, it's greatly apreciated.

Hi Fletcher

I'll answer you questions and points inline...

Thank you Graham for all your helpful information. I don't know that
everything that you have said is necessary for my database, but I
believe that some of it would help. I'll do my best to explain why I
do or do not think that what you told me is necessary. Please don't
take this as a blow to your person.

Hey - it takes a LOT more than that to offend me!

I'll start by saying that your proposed tables for FSIs and Operators
would be pointless in our facility. Mainly because we don't desire -
yet - to have information on those stored electronically, but it may
happen in the future. Although, I may look at creating an FSI table
now for reasons that I'll mention later.

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time

There are two reasons why I would still recommend an Operators table:
1. it makes data entry easier, because you can use a combo box which
does autocomplete to select the operator
2. it prevents errors - for example, typing the initials HM when you
mean GM

It doesn't need to be complex:
OperatorID - autonumber, primary key
OpName - text, no duplicates
OpInits - text, no duplicates (if you need this for a short field in
forms/reports)

Your proposed table called *Passes* is basically what I already have.
It includes the pass number, which is what I would like to count from 1
to 20 and then start over (and is what started this thread); it
includes the date/time, which I have condensed to one entry instead of
two; it includes the operator ID, which in our facility is simply
initials; it contains the pre_meas and post_meas; it also contains
comments. This table also brings us to the problem. You tell me that
I need to have a unique pass number for each pass, and I say that I
only want numbers 1 to 20. I'll come back to this.

You DO need a unique pass number for each pass, because that's the only
way
to link the pass with the runs for that pass. If it's not unique, then
how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass
13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?

You DO NOT need a counter for the number of passes since the last
recharge,
as the number of pass records since that date can easily be counted.

To continue, I don't really see a point in a having a table for
Recharges. Again simply because we don't care to have infomation
stored about this process. We only desire to know that it happened.
Which we would know when the pass number entry resets to 1.

Fine - if you have no need to record recharge history then all you need
is a
LastRecharge field in the FSIs table (see above).

However, when someone comes to you in the future and says: "Can you tell
me
how many times this FSI has been recharged in the last year?", then I
would
be the first to say "I told you so!"

What I am interested in is having a table for Passes and a table for
runs linked because we can have multiple runs in a single pass and we
would like to later be able to search for a given run and see all the
information about it. This takes us back to the table for passes and
the table for FSIs...

I would like to have the three table set up similar to what you've
explained. I would like to have the Pass Table that you described,
and the Run table that you described, and I believe that this set up
will also require the FSI table you described. Perhaps you can help me
with this. I'll explain further.

I would like to have:
*FSI*
FSI_ID, Number (we only have 3 machines, so these would be 1, 2, or 3)

*Runs*
Pass_ID, Number (to link to *Pass* coming up)
Run_Number, Number (5digit ID number that remains constant throughout
each step in the facility)

*Pass*
Pass_ID, ? (I'm reluctant to use autonumber)
Pass_Number (Numbers 1 through 20, unless there is some other way to
display on a form how manny passes have been done since recharge?)
Date/TimeOut, Date/Time, Now()
FSI_ID, Number (to link to *FSI*)
Operator, Text (2 or 3 letter initials of operator simply to know who
ran the pass)
Pre, Number
Post, Number
Comments

Now, in your description of *Runs* you said that Pass_ID and Run_Number
could be combined to form a primary key? I am not sure how to do this.

The easiest way is to open the table in design view, select BOTH the
fields,
and then click the primary key button on the toolbar.

And I am most definitely not sure how to link the tables as everyone
talks about. I hope I have satisfied you by deciding to revamp my
database. If you could help me through this, I would be most
apreciative. Thank you.

Click on Tools>Relationships. Then you can add tables to the window
(View>show tables) and drag the mouse from the primary key of one table
to
the related field (foreign key) of another.


One more question though. If I were to do this, what would happen if a
given "Run" went through a FSI more than once?

Not quite sure what you mean by this. I think you'll need to explain a
bit
more about exactly what a run is, and what an FSI does.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Fletcher]

One other question though, how will I store a date in the RCHGDate
field in *FSIs* when each FSI is recharged? This is the only place
that I'm really confused right now.

Hey Graham, I just want to say thanks one more time for spending so
much time on this with me. You're the only person who has actually sat
down and explained to me how this is supposed to work. So, thank you.

Back to work now. I see what you mean about the autonumbers, and I
will use them in some cases. Although, I am still slightly nervous
about using them. I did not know about linking an autonumber to a
number or a text to a text, so that is good information to know.

Let me get this straight... One FSI can clean up to six runs in one pass,
and can perform up to 20 passes before it needs to be recharged. Correct?

Yes, that is correct. But also, a run can go through an FSI more than
once, and not necessarily through the same FSI or with the same runs it
went through with before.

It sounds to me like your Runs table should be holding information about the
Runs (RunNumber and perhaps RunDescription) and you need a separate table
(RunPasses?) to record which runs were "done" in each pass.

Storing this information about the runs would be unnecessary since the
information is stored on a "Runcard" that accompanies the "run" through
the facility. I think I see what is happening here though. Let me
take a shot at it:

When I build a form, I will have a main form that has *Passes* as it's
record source and a subform that has *Runs* as it's record source, and
when the Information for Passes is entered, I can enter as many runs as
necessary on the subformand the link between the two will be PassID,
and therefore the PassID will be carried over to each run? Also, on
the main form, I will have a combo box for FSINumber and
OperatorInitials that look up from *FSIs* and *Operators*. Am I good
so far? I think so.

This brings me back to the likelyhood of a run going through an FSI
more than once. Since the primary keys in *Runs* is both PassID and
RunNumber, will having more than one RunNumber with a different PassID
for each create a problem, or will access recognize it as a different
entry because it has a different pass? Does that make sense at all?
I hope so.

I think we're getting closer to the end of our discussion, which makes
me happy. I hope other people can read this and get an idea of what is
necessary to get the hang of this database building. Thanks again,

Fletcher...

Hi Fletcher

Glad you're slowly coming around to my way of thinking ;-)

First, your fears about autonumbers are unfounded. You never need to edit
them, as they only serve as a way of linking records on a one-to-many basis.
Also, there is no problem deleting a record with an autonumber primary key,
UNLESS it has related records in another table, in which case you would want
to prevent their deletion anyway.

On the problem of counting passes since the last recharge, the following
query will return two columns showing the FSINumber and the number of
passes:

Select FSIs.FSINumber, (Select Count(*) from Passes
where Passes.FSINumber=FSIs.FSINumber
and Passes.DateTimeOut>FSIs.RchgDate) AS NumberOfPasses
from FSIs;

You could save this as a query (qryNumberOfPasses) and then retrieve the
number wherever it is required - for example, on your form where you enter
details about a pass, you could have a textbox (txtNumberOfPasses) with this
ControlSource:
=DLookup("NumberOfPasses", "qryNumberOfPasses",
"FSINumber=" & [FSINumber])

Now, more on design...

Let me get this straight... One FSI can clean up to six runs in one pass,
and can perform up to 20 passes before it needs to be recharged. Correct?

It sounds to me like your Runs table should be holding information about the
Runs (RunNumber and perhaps RunDescription) and you need a separate table
(RunPasses?) to record which runs were "done" in each pass.

Something like this:

*Runs*
RunNumber: numeric, long integer, primary key
RunDescription: text

*RunPasses*
(Both these fields would combine to make the PK.)
PassID: numeric, long integer, FK to Passes.PassID
RunNumber: numeric, long integer, FK to Runs.RunNumber
(and any other data pertaining to the results of that run, if necessary)


(Note that FK means "foreign key" - the "child" side of a relationship.)

Also, on the question about OperatorID vs OperatorInits as the related
field, a relationship must always be with a PK, so if you have an autonumber
(OperatorID) as your PK then the FK in Passes must be numeric (long integer)
and if you have text (inits) as your PK then you must have a text field as
your FK.

There's no strong reason not to use OperatorInits as your PK, except that it
might cause problems if the initials change for any reason ( e.g. change of
name).
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Okay, this is probably the most productive discussion I've had about
this since I've begun. Thank you Graham. I'll answer your questions
and add some of my own to the mix. And I'll concede to the operators
table...I think it's a good idea now that you explained it more.

Firstly, I'll explain an FSI and Runs like you asked at the end of your
last post.
An FSI is a cleaning device that can clean up to 6 runs (aka lots to
some people, it just depends on your preference, but everyone who works
here understands either) which all have a unique run number associated
with it that is carried throughout the facility. A run is also a group
of 25 potential products.

So that can bring us to my last question: What would happen if a run
number went through an FSI more than once (e.g. it was cleaned more
than once, which happens very regularly)? I believe that it wouldn't
matter since the PassID and RunNumber will both be primary keys and
therefore I will never have a single run number going through the same
pass and never creating a duplicate entry in *Runs*. As well as the
chance that it will go through the same peice of machinery (FSI).

I hope that's enough. I'm not really allowed to disclose what it is
that we do because of government contracts. Sorry that I can't go more
in depth.

Now, to move back into the discusion:
You DO need a unique pass number for each pass, because that's the only
way
to link the pass with the runs for that pass. If it's not unique, then
how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass
13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?
I'll put a unique identifier, but I am very reluctant to use auto
number because I've found that it can become slightly messy if edits or
deletions are necessary. Do you have any suggestions on this? Also,
how would I calculate the number of passes since the last recharge and
display this value on a form (which was the ultimate goal of this
thread, and I am determined to figure this out)?

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time
Check, but how do I tell it (or have it retrieve) what the last
recharge date was?

And I'm just starting to run this through my head: how do I keep track
of the need for each FSI to be recharged. Since there are 3 machines
and the information for every pass on all 3 are stored in the same
table, how will I sort out which FSI has had 5 passes since recharge
and which has had 19 since recharge?

Here is my updated table structure:
*Passes*
PassID, Number, Primary Key, link to *Runs*>PassID
FSINumber, Number, link to *FSIs*>FSINumber
DateTimeOut, Date/Time, default Now()
Operator, Text, Link to *Operators*> (I'm not sure if I should link
this to peratorID or OperatorInitials)
Pre, Number
Post, Number
Comment, Memo

*Operators*
OperatorID, AutoNumber, (maybe) link to *Passes*>Operator
OperatorName, text
OperatorInitials, text (maybe) link to *Passes*>Operator

*Runs*
PassID, Number, link to *Passes*>PassID
RunNumber, Number

*FSIs*
FSINumber, Number, link to *Passes*>FSINumber
RCHGDate, date/time

If you see anything wrong, please point it out. And could you tell me
which link would be better on *operators*?

Thanks for your help, it's greatly apreciated.


Graham Mandeno wrote:
Hi Fletcher

I'll answer you questions and points inline...

Thank you Graham for all your helpful information. I don't know that
everything that you have said is necessary for my database, but I
believe that some of it would help. I'll do my best to explain why I
do or do not think that what you told me is necessary. Please don't
take this as a blow to your person.

Hey - it takes a LOT more than that to offend me!

I'll start by saying that your proposed tables for FSIs and Operators
would be pointless in our facility. Mainly because we don't desire -
yet - to have information on those stored electronically, but it may
happen in the future. Although, I may look at creating an FSI table
now for reasons that I'll mention later.

Yes, I can see you need an FSIs table. It needs a minimum of two fields:
FSINumber - Numeric, integer, primary key - values 1, 2, 3
LastRecharge - Date/Time

There are two reasons why I would still recommend an Operators table:
1. it makes data entry easier, because you can use a combo box which
does autocomplete to select the operator
2. it prevents errors - for example, typing the initials HM when you
mean GM

It doesn't need to be complex:
OperatorID - autonumber, primary key
OpName - text, no duplicates
OpInits - text, no duplicates (if you need this for a short field in
forms/reports)

Your proposed table called *Passes* is basically what I already have.
It includes the pass number, which is what I would like to count from 1
to 20 and then start over (and is what started this thread); it
includes the date/time, which I have condensed to one entry instead of
two; it includes the operator ID, which in our facility is simply
initials; it contains the pre_meas and post_meas; it also contains
comments. This table also brings us to the problem. You tell me that
I need to have a unique pass number for each pass, and I say that I
only want numbers 1 to 20. I'll come back to this.

You DO need a unique pass number for each pass, because that's the only
way
to link the pass with the runs for that pass. If it's not unique, then
how
can you tell which pass a run with PassNumber=13 belongs to? Was it pass
13
for FSD#1 or FSD#2, and was it pass 13 from this week, or from several
recharges ago?

You DO NOT need a counter for the number of passes since the last
recharge,
as the number of pass records since that date can easily be counted.

To continue, I don't really see a point in a having a table for
Recharges. Again simply because we don't care to have infomation
stored about this process. We only desire to know that it happened.
Which we would know when the pass number entry resets to 1.

Fine - if you have no need to record recharge history then all you need
is a
LastRecharge field in the FSIs table (see above).

However, when someone comes to you in the future and says: "Can you tell
me
how many times this FSI has been recharged in the last year?", then I
would
be the first to say "I told you so!"

What I am interested in is having a table for Passes and a table for
runs linked because we can have multiple runs in a single pass and we
would like to later be able to search for a given run and see all the
information about it. This takes us back to the table for passes and
the table for FSIs...

I would like to have the three table set up similar to what you've
explained. I would like to have the Pass Table that you described,
and the Run table that you described, and I believe that this set up
will also require the FSI table you described. Perhaps you can help me
with this. I'll explain further.

I would like to have:
*FSI*
FSI_ID, Number (we only have 3 machines, so these would be 1, 2, or 3)

*Runs*
Pass_ID, Number (to link to *Pass* coming up)
Run_Number, Number (5digit ID number that remains constant throughout
each step in the facility)

*Pass*
Pass_ID, ? (I'm reluctant to use autonumber)
Pass_Number (Numbers 1 through 20, unless there is some other way to
display on a form how manny passes have been done since recharge?)
Date/TimeOut, Date/Time, Now()
FSI_ID, Number (to link to *FSI*)
Operator, Text (2 or 3 letter initials of operator simply to know who
ran the pass)
Pre, Number
Post, Number
Comments

Now, in your description of *Runs* you said that Pass_ID and Run_Number
could be combined to form a primary key? I am not sure how to do this.

The easiest way is to open the table in design view, select BOTH the
fields,
and then click the primary key button on the toolbar.

And I am most definitely not sure how to link the tables as everyone
talks about. I hope I have satisfied you by deciding to revamp my
database. If you could help me through this, I would be most
apreciative. Thank you.

Click on Tools>Relationships. Then you can add tables to the window
(View>show tables) and drag the mouse from the primary key of one table
to
the related field (foreign key) of another.


One more question though. If I were to do this, what would happen if a
given "Run" went through a FSI more than once?

Not quite sure what you mean by this. I think you'll need to explain a
bit
more about exactly what a run is, and what an FSI does.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Fletcher

Hey Graham, I just want to say thanks one more time for spending so
much time on this with me. You're the only person who has actually sat
down and explained to me how this is supposed to work. So, thank you.

You're very welcome! that's why I enjoy doing this

Storing this information about the runs would be unnecessary since the
information is stored on a "Runcard" that accompanies the "run" through
the facility. I think I see what is happening here though. Let me
take a shot at it:

OK - that's fine. My reasoning was similar to the argument for having an
Operators table - to ensure that only valid run numbers were used.

When I build a form, I will have a main form that has *Passes* as it's
record source and a subform that has *Runs* as it's record source, and
when the Information for Passes is entered, I can enter as many runs as
necessary on the subformand the link between the two will be PassID,
and therefore the PassID will be carried over to each run? Also, on
the main form, I will have a combo box for FSINumber and
OperatorInitials that look up from *FSIs* and *Operators*. Am I good
so far? I think so.

You've got it

This brings me back to the likelyhood of a run going through an FSI
more than once. Since the primary keys in *Runs* is both PassID and
RunNumber, will having more than one RunNumber with a different PassID
for each create a problem, or will access recognize it as a different
entry because it has a different pass? Does that make sense at all?
I hope so.

Right again! The only thing you will not be able to do is enter the same
run number twice for a single pass (which I assume could not happen so it
will serve as good protection).

 

[Graham Mandeno]

Hi again

One other question though, how will I store a date in the RCHGDate
field in *FSIs* when each FSI is recharged? This is the only place
that I'm really confused right now.

Ah well... that depends on what date/time you want to record.

Will it always be the current date and time, or do you want to allow the
user to enter a date/time, perhaps with a default of Now()?

If you don't want a user override option, then you can use a simple update
query:

CurrentDb.Execute "Update FSIs set RCHGDate=Now() where FSINumber=" &
Me.FSINumber

You might want to pop up a MsgBox to request confirmation before executing
the update.

If you want the override option, then I suggest you make another form bound
to FSIs, with locked, disabled textboxes bound to FSINumber and RCHGDate.
Add another unbound textbox (txtNextRecharge) with an appropriate date/time
format and a default value =Now(). Your form will look something like this:

FSI Number: 2
Last Recharge: 20-Jul-2006 09:45
This Recharge: 29-Jul-2006 14:46

OK Cancel

The user can change the date in the unbound field if required, and then
click OK.

The Click event procedure for the OK button looks like this:

Private Sub cmdOK_Click()
' update the "real" field
Me.RCHGDate = txtNextRecharge
' save the changed record
Me.Dirty = False
On Error Resume Next
' requery the txtNumberOfPasses textboxes on the main form
Forms!frmPasses!txtNumberOfPasses.Requery
' close this form
DoCmd.Close acForm, Me.Name
End Sub

The Cancel button would just close the form without updating.

 

[Fletcher]

I don't know if you saw my last question or not, but here is what I
asked:

One more question though, how will I store a date in the RCHGDate
field in *FSIs* when each FSI is recharged? This is the only place
that I'm really confused right now.

OK - that's fine. My reasoning was similar to the argument for having an
Operators table - to ensure that only valid run numbers were used.

To ensure that valid run numbers are used here we are implimenting
bar-code scanning on the "runcards." That way, if a given run number
is wrong once, it will be wrong forever and will be easily tracked. We
have a turn around time of many months, so runs accumulate alot of
information.

And thanks for all your help. It's been awesome.

Hi Fletcher

Hey Graham, I just want to say thanks one more time for spending so
much time on this with me. You're the only person who has actually sat
down and explained to me how this is supposed to work. So, thank you.

You're very welcome! that's why I enjoy doing this

Storing this information about the runs would be unnecessary since the
information is stored on a "Runcard" that accompanies the "run" through
the facility. I think I see what is happening here though. Let me
take a shot at it:

OK - that's fine. My reasoning was similar to the argument for having an
Operators table - to ensure that only valid run numbers were used.

When I build a form, I will have a main form that has *Passes* as it's
record source and a subform that has *Runs* as it's record source, and
when the Information for Passes is entered, I can enter as many runs as
necessary on the subformand the link between the two will be PassID,
and therefore the PassID will be carried over to each run? Also, on
the main form, I will have a combo box for FSINumber and
OperatorInitials that look up from *FSIs* and *Operators*. Am I good
so far? I think so.

You've got it

This brings me back to the likelyhood of a run going through an FSI
more than once. Since the primary keys in *Runs* is both PassID and
RunNumber, will having more than one RunNumber with a different PassID
for each create a problem, or will access recognize it as a different
entry because it has a different pass? Does that make sense at all?
I hope so.

Right again! The only thing you will not be able to do is enter the same
run number twice for a single pass (which I assume could not happen so it
will serve as good protection).
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

I don't know if you saw my last question or not, but here is what I
asked:

Yes - sure did! Here it is again in case you missed it:

Hi again

One other question though, how will I store a date in the RCHGDate
field in *FSIs* when each FSI is recharged? This is the only place
that I'm really confused right now.

Ah well... that depends on what date/time you want to record.

Will it always be the current date and time, or do you want to allow the
user to enter a date/time, perhaps with a default of Now()?

If you don't want a user override option, then you can use a simple update
query:

CurrentDb.Execute "Update FSIs set RCHGDate=Now() where FSINumber=" &
Me.FSINumber

You might want to pop up a MsgBox to request confirmation before executing
the update.

If you want the override option, then I suggest you make another form
bound
to FSIs, with locked, disabled textboxes bound to FSINumber and RCHGDate.
Add another unbound textbox (txtNextRecharge) with an appropriate
date/time
format and a default value =Now(). Your form will look something like
this:

FSI Number: 2
Last Recharge: 20-Jul-2006 09:45
This Recharge: 29-Jul-2006 14:46

OK Cancel

The user can change the date in the unbound field if required, and then
click OK.

The Click event procedure for the OK button looks like this:

Private Sub cmdOK_Click()
' update the "real" field
Me.RCHGDate = txtNextRecharge
' save the changed record
Me.Dirty = False
On Error Resume Next
' requery the txtNumberOfPasses textboxes on the main form
Forms!frmPasses!txtNumberOfPasses.Requery
' close this form
DoCmd.Close acForm, Me.Name
End Sub

The Cancel button would just close the form without updating.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Fletcher]

Hey graham,

All of this stuff works awesome. Thanks for everything that you
explained to me. I really apreciate it.

Now I only have one more question. I chose to use the recharge option
that uses Now(). One thing that I've noticed is that when you go to
the form and input an FSI number, it will tell you the number of passes
since the last recharge. This is all good and well, but when you
actually do click the recharge button, time has elapsed since you've
opened the form. So when you click the recharge button and then enter
in a new pass, the time on the form has not updated and believes that
it is behind the last recharge time.

So my question is: Is there any way to put an update form line in the
code of the recharge button? Or is there a way to subtract 1 minute
from the Now() in the recharge code? Or another option could be set
the recharge date to the date currently on the form. I would prefer
the first option, but I have no idea how to do it. I'll work on it and
let you know if I figure it out before you get back to me.

Thanks,

Fletcher....

I don't know if you saw my last question or not, but here is what I
asked:

Yes - sure did! Here it is again in case you missed it:

Hi again

One other question though, how will I store a date in the RCHGDate
field in *FSIs* when each FSI is recharged? This is the only place
that I'm really confused right now.

Ah well... that depends on what date/time you want to record.

Will it always be the current date and time, or do you want to allow the
user to enter a date/time, perhaps with a default of Now()?

If you don't want a user override option, then you can use a simple update
query:

CurrentDb.Execute "Update FSIs set RCHGDate=Now() where FSINumber=" &
Me.FSINumber

You might want to pop up a MsgBox to request confirmation before executing
the update.

If you want the override option, then I suggest you make another form
bound
to FSIs, with locked, disabled textboxes bound to FSINumber and RCHGDate.
Add another unbound textbox (txtNextRecharge) with an appropriate
date/time
format and a default value =Now(). Your form will look something like
this:

FSI Number: 2
Last Recharge: 20-Jul-2006 09:45
This Recharge: 29-Jul-2006 14:46

OK Cancel

The user can change the date in the unbound field if required, and then
click OK.

The Click event procedure for the OK button looks like this:

Private Sub cmdOK_Click()
' update the "real" field
Me.RCHGDate = txtNextRecharge
' save the changed record
Me.Dirty = False
On Error Resume Next
' requery the txtNumberOfPasses textboxes on the main form
Forms!frmPasses!txtNumberOfPasses.Requery
' close this form
DoCmd.Close acForm, Me.Name
End Sub

The Cancel button would just close the form without updating.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Fletcher

Let's just make sure that I'm understanding you correctly.

Are you saying that you are adding a new pass record and the form has a
default value of =Now() for the PassDate, but you want to register the
recharge as happening *before* the time of the pass you are just entering?

You have three options:

1. Set the recharge date/time to the same as the value in the pass date/time
textbox:
CurrentDb.Execute "Update FSIs set RCHGDate=" _
& Format(Me!PassDate, "\#mm/dd/yyyy hh:nn:ss\#") _
& " where FSINumber=" & Me!FSINumber

2. Reset the pass date/time to Now() *after* the recharge date/time has been
updated:
CurrentDb.Execute ...
Me!Passdate = Now()

3. Forget about using a default value and record the pass date/time at the
time that the record is saved.
In Form_BeforeUpdate:
If Me.NewRecord then Me!PassDate = Now()

I'm glad everything else is working so well
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hey graham,

All of this stuff works awesome. Thanks for everything that you
explained to me. I really apreciate it.

Now I only have one more question. I chose to use the recharge option
that uses Now(). One thing that I've noticed is that when you go to
the form and input an FSI number, it will tell you the number of passes
since the last recharge. This is all good and well, but when you
actually do click the recharge button, time has elapsed since you've
opened the form. So when you click the recharge button and then enter
in a new pass, the time on the form has not updated and believes that
it is behind the last recharge time.

So my question is: Is there any way to put an update form line in the
code of the recharge button? Or is there a way to subtract 1 minute
from the Now() in the recharge code? Or another option could be set
the recharge date to the date currently on the form. I would prefer
the first option, but I have no idea how to do it. I'll work on it and
let you know if I figure it out before you get back to me.

Thanks,

Fletcher....

I don't know if you saw my last question or not, but here is what I
asked:

Yes - sure did! Here it is again in case you missed it:

Hi again

One other question though, how will I store a date in the RCHGDate
field in *FSIs* when each FSI is recharged? This is the only place
that I'm really confused right now.

Ah well... that depends on what date/time you want to record.

Will it always be the current date and time, or do you want to allow
the
user to enter a date/time, perhaps with a default of Now()?

If you don't want a user override option, then you can use a simple
update
query:

CurrentDb.Execute "Update FSIs set RCHGDate=Now() where FSINumber=" &
Me.FSINumber

You might want to pop up a MsgBox to request confirmation before
executing
the update.

If you want the override option, then I suggest you make another form
bound
to FSIs, with locked, disabled textboxes bound to FSINumber and
RCHGDate.
Add another unbound textbox (txtNextRecharge) with an appropriate
date/time
format and a default value =Now(). Your form will look something like
this:

FSI Number: 2
Last Recharge: 20-Jul-2006 09:45
This Recharge: 29-Jul-2006 14:46

OK Cancel

The user can change the date in the unbound field if required, and then
click OK.

The Click event procedure for the OK button looks like this:

Private Sub cmdOK_Click()
' update the "real" field
Me.RCHGDate = txtNextRecharge
' save the changed record
Me.Dirty = False
On Error Resume Next
' requery the txtNumberOfPasses textboxes on the main form
Forms!frmPasses!txtNumberOfPasses.Requery
' close this form
DoCmd.Close acForm, Me.Name
End Sub

The Cancel button would just close the form without updating.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand