Access Query

[}{]

Can someone suggest the best way to accomlish the following:

I have a query that lists all of the records between two dates, these
records contain Machine owner, machine serial number and machine cycle count
among other things.

I would like to calculate the cycle count for each machine between the two
dates.

I have used the Min and Max functions against each serial but the problem is
if there is only one entry for a particular serial number, the min and max
values are the same. Ideally I need to use the last value before the first
set in the current query if that makes any sense.

Your suggestions/comments would be welcomed

[Salad]

Re: Access Query

}{ wrote:

Can someone suggest the best way to accomlish the following:
>
I have a query that lists all of the records between two dates, these
records contain Machine owner, machine serial number and machine cycle count
among other things.
>
I would like to calculate the cycle count for each machine between the two
dates.
>
I have used the Min and Max functions against each serial but the problem is
if there is only one entry for a particular serial number, the min and max
values are the same. Ideally I need to use the last value before the first
set in the current query if that makes any sense.

Not really. Do you want to subtract the Min from Max...only if the
values aren't the same? If so, for ease of use, create two queries.
Let's say your current query is called Query1 and the columns are
MaxCycle and MinCycle. Create a new query, drop in Query1, drop the
fields you want to display from Query1, and create a new column called
CycleValue. Like this
CycleValue : IIF(MaxCycle <MinCycle, MaxCycle-MinCycle, MaxCycle)
Save this as Query2 and call/run Query2. If that isn't what you want,
define your problem better.

Your suggestions/comments would be welcomed
>
>

[}{]

Re: Access Query


"Salad" <oil@vinegar.co mwrote in message
news:G7-dnTvhWKMkzrnUnZ 2dnUVZ_qfinZ2d@ earthlink.com.. .

}{ wrote:
>

>Can someone suggest the best way to accomlish the following:
>>
>I have a query that lists all of the records between two dates, these
>records contain Machine owner, machine serial number and machine cycle
>count among other things.
>>
>I would like to calculate the cycle count for each machine between the
>two dates.
>>
>I have used the Min and Max functions against each serial but the problem
>is if there is only one entry for a particular serial number, the min and
>max values are the same. Ideally I need to use the last value before the
>first set in the current query if that makes any sense.

>
Not really. Do you want to subtract the Min from Max...only if the values
aren't the same? If so, for ease of use, create two queries. Let's say
your current query is called Query1 and the columns are MaxCycle and
MinCycle. Create a new query, drop in Query1, drop the fields you want to
display from Query1, and create a new column called CycleValue. Like this
CycleValue : IIF(MaxCycle <MinCycle, MaxCycle-MinCycle, MaxCycle)
Save this as Query2 and call/run Query2. If that isn't what you want,
define your problem better.
>

>Your suggestions/comments would be welcomed

Ok Suppose I have the following information in a table.

Date Customer Serial No
CycleCount

10/07/08 Customer A 123
1000
11/07/08 Customer B 321
400
03/08/08 Customer A 123
14000
10/08/08 Customer A 123
22500
10/08/08 Customer B 321
15000
11/08/08 Customer A 123
28000
29/08/08 Customer A 123
31212

I have a query that will filter out the customer and/or serial number that I
would like, but what I would also like is an extra field with the difference
between one cycle count and the previous,
ie the cycle count for serial 123 on 03/03/08 and the cycle count for serial
123 on 10/07/08. Basically the cycles between two dates.

As I have said, I have a query set up to do the filtering, I just don't know
how to find the difference between the two values.

It is probably very basic, but I am a newbie.