Counter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I have a problem that I would like to be solved. I would like a field
called "Failure to date" to be a counter field, where it counts the number of
days between records by the date in a field called "Date Located". These
records will be related by a field called "PRNUmber", inwhich these records
will have the same number.

Ex: if both PRNumbers are 1245-B
#1 = 6/20/1994 "Failure to date in this field would be zero
#2 = 6/24/1994 "Falure to Date in this field would then be "4""
Please Help...
 
Storing data like this in a field is usually not a good idea. It violates
the principles of relational database design because the "Failure to date"
value depends on the values in another field and another record. If either
of these values is changed for any reason, the "Failure to date" value needs
to change as well or the data becomes inconsistent.

Instead, you can calculate the "Failure to date" values (shouldn't they be
called "days since last failure" or some such?) in a query, e.g.

SELECT A.PRUNumber,
A.DateLocated AS ThisDateLocated,
B.DateLocated AS PreviousDateLocated,
DateDiff("D", B.DateLocated, A.DateLocated) AS FailureToDate
FROM MyTable AS A
LEFT JOIN MyTable AS B
ON A.PRUNumber = B.PRUNumber
WHERE B.DateLocated = (
SELECT MAX(C.DateLocated) FROM MyTable AS C
WHERE C.DateLocated < A.DateLocated
AND C.PRUNumber = A.PRUNumber
)
ORDER BY A.PRUNumber, A.DateLocated DESC;
 
Hi there,

I am a bit confused by your example! Are you saying:

- There will always be exactly and only two records with the same PRNumber
value?
- You wish to store "0" in the failuretodate field where the date is the
lower of the two
- You wish to store the number of days difference between the lower and
higher date in the failuretodate field of the record with the higher date.

Strictly, I beleieve what you are asking is not possible in the way that you
explain it. Access doesn't support "calculated fields" within a table (ie a
table field that is the result of a calculation). The only way to
accomplish this would be to create a query using the DateDiff() function to
calculate the difference, then creating a second query to join these results
back to the original table. This would be tricky also, as you would
probably need to create two intermediate queries selecting Max and Min of
the date field with the PRNumbers, then another query selecting the
maxofdate, minofdate, PRNumber and date calculation from the two
intermediate queries, another to select the calculation along with the
related fields from the orginal tables, selecting conditionally "0" or the
calculation result depending on whether the record's date field was equal to
the max or the min. Phew!

That's four layers(?) of nested queries - or so. This might be best
implemented as an update query that you can run intermittently, as I'm
imagining it being slow, slow, slow once you're into largeish datasets.

If you have a do have large database (even if you don't actually!) it may be
an idea to actually write code that will parse your tables and calculate the
results your require. You could then run this on a daily basis (say, on
database startup).

Best of all would be to write a Sub or Function that would accept a PRNumber
as a parameter and simply go off and use ADO code to set the fields in the
way that you require. You could put this on the AfterUpdate event anywhere
the date fields are editable on a form by a user.

I'd also ask you consider (just for a minute!) your data design here - this
seems like a very tricky way of doing things... Is there not a better way?
Why are the two dates in two different records? Why do you need zero in one
and the date in the other? Could you not pre-parse your data into a more
convenient format?

Hope this is of some help anyway - fun though! A tricky one!

Ben.
 
Hi ben thanks for the response, What I am building is a database for AC Motors.
A PRNumber is a Number that is specific to each motor. So when a motor gets
sent out for repairs we need to relocate that motor to another Unit. So
there will be many records with the same PRNumber, also the DateLocated field
will be different for each record of the same motor. There may be many
records on the same motor, and I would Like the "DayToFailure" field to tell
me that days between the last date entered with the same PRNumber. When this
value is calculated I would Like it stored in its field. Also, yes I would
like the first record to be zero..

Sorry i am new at access trying to do advanced coding
thanks for your time
 
No. What I posted is the SQL statement for a complete query (which works in
my test database but will need modifying to work in yours).

Try this:
1) Create a new query but don't add any tables to it. Switch it to SQL view.
2) Paste the SQL I posted into the new query, and then change the field
names (e.g. PRUNumber and FailureToDate) and table name (MyTable) so they
are the same as yours.
3) Run the query. When you get it working you'll see that in each record
it's returning the number of days since the previous failure for that PR
number.
 
John Your code worked beautifully. I couldnt ask for a better function. I
am very sorry for my lack of skills, but do you think you can tell me how to
intergrate another field into the query. For each PR repair it switches the
"AreaOfLocation" so
how can I show the Previous(A) "AreaOfLocation" and The Present(B)
"AreaOfLocation"
 
I'm glad it's working for you. To add more fields, just type them into the
field list in the query with commas between them. If you use the alias A,
you'll get the value from the "current" incident; or use B for the value
from the previous one, as in the A.DateLocated and B.DateLocated in my
original posting:

SELECT A.PRUNumber,
A.DateLocated AS ThisDateLocated,
B.DateLocated AS PreviousDateLocated,
DateDiff("D", B.DateLocated, A.DateLocated) AS FailureToDate
FROM MyTable AS A
....

This is because the query joins the table to itself, using A and B for the
two instances:
...
FROM MyTable AS A
LEFT JOIN MyTable AS B
...

So to get the locations, you'd have something like this:
SELECT A.PRUNumber,
A.DateLocated AS ThisDateLocated,
B.DateLocated AS PreviousDateLocated,
DateDiff("D", B.DateLocated, A.DateLocated) AS FailureToDate,
A.AreaOfLocation AS CurrentArea,
B.AreaOfLocation AS PreviousArea
FROM MyTable AS A
....and so on
 
Back
Top