How do you do this???? Averages, Reports, tying it all together He

[Guest]

I have a table that contains the following:

Auto-num key Part # Type Counter
1 ABC New 1
2 ABC Used 2
3 XYZ New 1
4 ABC Used 3
5 ABC Used 4
6 XYZ Used 2
7 ABC New 1
8 ABC Used 2
9 XYZ New 1

This continues on and on for the life of the db. I would like to make a
report that finds the average counter for each 'new' cycle on each part
number over a time period specified by the user. For example above, ABC had
one cycle that went up to 4 and the final cycle that went to 2, using only
the record that says 4 and 2, respectively, I would like the average to be 3.
Any help would be greatly appreciated.
Thanks
Steve

 

[Jeff Boyce]

Steve

I didn't notice in your example how you associated ABC's 3 & 4 with the
"first" "cycle" of ABC. Just looking at the data, couldn't it be associated
with the second cycle?

Without some "cycle" indicator, you won't be able to tell when one cycle
ends (i.e., the Max() value in that cycle) and the next begins (and ends,
giving you the Max() value in that cycle).

 

[Guest]

Jeff,
I was planning on using the auto-numbered key which correlates to an issue
date, so as long as I keep the list ordered by that all of the records will
be in order. Also, I know that they are all together as long as another
'New' item doesn't get issued out in the date order. The info I am tracking
is similiar to keeping track of a set of rechargable batteries, each time the
batteries go out before they are fully charged again, I keep a counter as
well as other info not shown in the example. Once the batteries are dead, I
recharge them and start there cycle counter over again. I can never issue
them out before they are received or vice versa so the order will always stay
there by the auto-numbered key. The reason I use the key is because they are
sometimes issued and received twice or three times in the same day.. hope
this help.

Thanks again,
Steve

 

[Jeff Boyce]

I suspect the REAL order is your Date/Time information. An Autonumber key
may not always be sequential (check the tablesdbdesign newsgroup for related
discussions).

But I'm still a little confused. Even if you took a battery in 4 times in
one day, what do you mean by "as long as another 'New' item doesn't get
issued out"? Is that another battery? What if the first battery comes back
in a fifth time? Does that make it count=5 for that battery, that day, or
count=1 for a "new" item, whatever that means?

Still not clear, still need more info, please...

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

Steve

So, can you put this back in context of what you are trying to accomplish?

I suspect it is something like "how many times did I polish SawBlade###
before I needed to re-tip it?"

 

[Guest]

No problem Jeff,
Thanks for bearing with me.

What I would like is to be able to:

1. Have a report that would show the average polish and total polishs per
re-tip for a specific saw.
2. Have a report that would show the average polish and total polish per
re-tip for a range of saws.

Both of these reports I would like to have the user input a date range. I
know how to do that part, it is part 1 and 2 that I don't know how to do. I
hope this helps.
Thank you very much again!

Steve

 

[Jeff Boyce]

Steve

This is still in the wide-open-notion phase...

You only "know" that a saw is ready to have this calculation done if it has
at least one "re-tip" entry (otherwise, it's still being polished).

Once a saw's been re-tipped, you aren't interested in it until it is AGAIN
re-tipped (otherwise, it's still being polished).

So one approach might be to look for saws that a) have a re-tip, and b)
have an earlier re-tip (or initial deployment). This would seem to give you
a look at the most recent run of polishing. This "single" look could be
done with a query.

But because there's no way to know how many cycles, it makes more sense to
me to create a function that calculates your average for a given saw. That
way, you could use a query and embed the function and get a saw and it's
average.

Hope the thoughts help...

Jeff Boyce
<Access MVP>

 

[Guest]

Jeff,
We have only been running the db for about 2 months so there is still not
too many records there so I was thinking about changing the code so when a
re-tip occurs it looks at the last polish for the saw and catches the number
of polish since the last re-tip and records a new record in a different table
that has the fields: saw number and number of runs. Then I would be able to
run all of my reports off of that table. What do you think?

Steve

 

[Jeff Boyce]

Steve

The two problems (not insurmountable) I see with that approach are:
1) to do the calculation, you'll have to work out most of the logic you'd
need for processing the entire set anyway, and
2) storing a calculated value in either the same or a different table
will require you to work out how you will keep the data synchronized. If
any value involved in the calculation is changed ("oops, I goofed, let me
just fix that last entry"), you have to be sure that the average is
recalculated and re-stored.

Not saying it couldn't/shouldn't be done, but that you need "eyes wide open"
if you go that route.

I'd probably still lean toward working out the logic/code/function to
calculate an average for a given saw "on the fly" ... but my spouse
regularly reminds me that I enjoy Scientific American for "light reading"
<g>

 

[Guest]

Jeff,
So if I wrote the code that adds the new record in a different table that
records the last polish for the total runs and add code in my deletion form
that says if they delete a re-tip issue then it will go to that run table and
find the previously added run average and delete. Is that more what you were
thinking?

Thank you very much for helping me work through this,
Steve

 

[Jeff Boyce]

Steve

Actually, I was only considering logic/code that would "calculate" the
average, given a specific saw#. I was envisioning creating a query that
returns saw# and "average" (the average being calculated by using the
function).

I wasn't paying good enough attention to realize you were trying to add
records and delete records. That's considerably more complex that the
original notion I understood - how to calculate an average.

What logic did you have in mind to do the actual calculation?