473,320 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
Nov 19 '08 #1
2 2679
}{ 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

Nov 19 '08 #2
}{

"Salad" <oi*@vinegar.comwrote in message
news:G7******************************@earthlink.co m...
}{ 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.
Nov 19 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Frnak McKenney | last post by:
Back when computer dinosaurs roamed the earth and the precursors to today's Internet were tiny flocks of TDMs living symbiotically with the silicon giants, tracking access to data processing...
1
by: Joris Kempen | last post by:
Hi people, I know that the question has come around sometimes: How to open an Access Report using ASP and export it to for example RTF. I'm trying to implement the first method of David...
14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
4
by: bhbgroup | last post by:
I have a query on one large table. I only add one condition, i.e. a date (the SQL reads like 'where date > parameterdate'. This query is rather quick if 'parameterdate' is either explicitly...
52
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server...
4
by: tt40 | last post by:
Anyone know how to prevent Access 2002 from automatically breaking all the incorrect joins in a query and then automatically saving the broken query? This is what I would call stupid design...
6
by: InnoCreate | last post by:
Hi everyone. I've recently written a classic asp website which uses an MS Access datasource. I know this is less than an ideal data source as it has limited functionality. I have a search form on...
3
by: mnjkahn via AccessMonster.com | last post by:
I'm running Access 2003, modifying a query that has over 45 fields. When I right click on the field name in Query Design View, and then click Build, Access crashes before the Build window...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
13
by: magickarle | last post by:
Hi, I got a pass-through query (that takes about 15 mins to process) I would like to integrate variables to it. IE: something simple: Select EmplID from empl_Lst where empl_lst.timestamp between...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.