Is this a possible query?

[RHomsey]

I have very little access/programming knowlegde so please treat me like an idiot..

ok, i need to make a query that tells me the difference in amperage from 2 different records. The 2 records that need to be compared are the 2 most recent dates... These dates can vary.

How would i go about doing this (if possible)?

 

[Jon K]

Assuming Date (a date/time field) and Amp (a numeric field) are two fields in table tblAmp, try this query (type/paste in the SQL View of a new query, replacing with your field names and table name):-

SELECT First(Date) AS [Date 1], First(Amp) AS [Amp 1],
Last(Date) AS [Date 2], Last(Amp) AS [Amp 2],
First(Amp)-Last(Amp) AS [Difference In Amp]
FROM tblAmp
WHERE Date in (Select Top 2 Date from tblAmp order by Date Desc);

When the query is run, it will retrieve the two most recent records from the table and display them in one row:-
[Date 1], [Amp 1], [Date 2], [Amp 2], [Difference In Amp]


If you want to show a running difference between two consecutive records in the table sorted by Date, you will need two queries:

qryAmpOne:-
SELECT Date, Amp, (select count(*) from tblAmp where Date <= a.Date) AS RunningNum
FROM tblAmp AS a;

qryAmpTwo:-
SELECT Date, Amp, Amp-(Select Amp from qryAmpOne where RunningNum -b.RunningNum=-1) AS [Difference In Amp]
FROM qryAmpOne AS b;

Run the second query.


Hope this helps.

 

[RHomsey]

thanks

Thanks so much, i havent tried this yet (getting ready to leave work) ... Will try tomorrow... This will help me out so very much. Thanks again

 

[RHomsey]

When you say

"it will retrieve the two most recent records from the table and display them in one row:- "

Do you mean the most recent dates or most recent records??? I need the most recent dates... thats where the main complication comes in (i guess, i really dont know what is and isnt possible or complicated)

 

[Jon K]

Sorry for the confusion. I mean records of the two most recent dates.


I have attached a DB as a Demo , in which I have revised the query a little:-

SELECT First(Date) AS [Date 1], First(Amp) AS [Amp 1],
Last(Date) AS [Date 2], Last(Amp) AS [Amp 2],
iif([Date 1] < [Date 2], Last(Amp)-First(Amp), First(Amp)-Last(Amp)) AS [Difference In Amp]
FROM tblAmp
WHERE Date in (Select Top 2 Date from tblAmp order by Date Desc);

the iif() function ensures that [Difference In Amp] is obtained by subtracting [Amp] of the smaller date from [Amp] of the larger date, in case the records are entered not in the order of Date.


I have also added:-

ORDER BY Date

to qryTwo for the same reason.

 

[RHomsey]

Im getting syntax errors probably becuase my Amps are set up as a text field. The reason why i set them up as a text field is because whenever they were set to numeric it would always round off the number (decimal places) ... When i changed it to text it didnt round off anymore so i left it.

How can i set it as a numeric field and not have it round off the number???

If i reset the field now as a numberic number will it round off all my current entries?? That would be bad....


Oh, and i couldnt use that test database you posted because it was read only ... and i guess access xp doesnt like that the first tiem you open it.

 

[RHomsey]

Just so there is no confusion ... Is there anyway i can tell you my fields and you can post the query with those fields applied.. The reason i ask is because i get somewhat confused (dont laugh) as to of where to put my feilds.. I know once i get the first one done i can look at it and fill things in... but regardless here are all the queries i am trying to achieve.


General Fields:

Date = Date

Specific Fields:

*For one query, The item in which i need to know the difference*

Running Amps for Pump 1

*For another query, The item in which i need to know the difference*

Running Amps for Pump 2

*For another query, The item in which i need to know the difference*

Run Times for Pump 1

*For another query, The item in which i need to know the difference*

Run Times for Pump 2

 

[Jon K]

I attach another DB here, which was written in Access 2000. When you open it in Access XP for the first time, I think you will be presented with two options. Choose "Convert" instead of "Open" and save the database with a new name.

In the new DB, I have changed Amp to text.

(I find that Access 97 and 2000 can do the subtractions even though Amp is text. You can run the original query qryAmpTwo in the new DB to see if it would give you an error in XP because Amp is text.)


Just in case Access XP doesn't like Amp in text, I have used the val() function in this query to convert text Amp to value:

qryAmpDiff TwoRecentDates:-
SELECT First(Date) AS [Date 1], First(Amp) AS [Amp 1],
Last(Date) AS [Date 2], Last(Amp) AS [Amp 2],
iif([Date 1] < [Date 2], Last(val(Amp))-First(val(Amp)), First(val(Amp))-Last(val(Amp))) AS [Difference In Amp]
FROM tblAmp
WHERE Date in (Select Top 2 Date from tblAmp order by Date Desc);

When this query is run, it should return:-
Date 1 ---- Amp 1 -- Date 2 ---- Amp 2 -- Difference In Amp
10/27/02 -- 7.512 -- 10/28/02 -- 10.23 -- 2.718

---------------------------

Field for the Pumps
To incorporate the pumps, I have added a new table with a PumpID field and a parameter query.

tblPumpAmp (in which Amp is text):-
PumpID - Date --------- Amp
P1 ----- 10/6/2002 ---- 11.284
P1 ----- 10/8/2002 ---- 8.261
P1 ----- 10/27/2002 --- 7.512
P1 ----- 10/28/2002 --- 10.23
P2 ----- 11/16/2002 --- 13.256
P2 ----- 11/18/2002 --- 9.223
P2 ----- 11/20/2002 --- 6.111


qryPumpAmpDiff TwoRecentDates:-
SELECT First(PumpID) AS [Pump ID], First(Date) AS [Date 1], First(Amp) AS [Amp 1],
Last(Date) AS [Date 2], Last(Amp) AS [Amp 2],
iif([Date 1] < [Date 2], Last(val(Amp))-First(val(Amp)), First(val(Amp))-Last(val(Amp))) AS [Difference In Amp]
FROM tblPumpAmp
WHERE PumpID=[Enter PumpID] and Date in (Select Top 2 Date from tblPumpAmp where PumpID=[Enter PumpID] order by Date Desc);

When the query is run, the user is asked to enter a PumpID.

If p1 is entered, it should return:-
Pump ID -- Date 1 ---- Amp 1 -- Date 2 ---- Amp 2 - Difference In Amp
P1 ------- 10/27/02 -- 7.512 -- 10/28/02 -- 10.23 -- 2.718

If p2 is entered, it should return:-
Pump ID -- Date 1 ---- Amp 1 -- Date 2 ---- Amp 2 - Difference In Amp
P2 ------- 11/18/02 -- 9.223 -- 11/20/02 -- 6.111 -- -3.112


If Time is recorded in the table similar to Amp, I am sure you can adapt the query for the Time without problem.

Hope this helps.

 

[RHomsey]

thanks again

You are such a big help and I cant thank you enough.

 

[RHomsey]

SELECT First(Date) AS [Date 1], First(Running Amps for Pump 1) AS [Amp 1],
Last(Date) AS [Date 2], Last(Running Amps for Pump1) AS [Amp 2],
iif([Date 1] < [Date 2], Last(val(Running Amps for Pump 1))-First(val(Running Amps for Pump 1)), First(val(Running Amps for Pump 1))-Last(val(Running Amps for Pump 1))) AS [Difference In Amp]
FROM Lift Station Database Table
WHERE Date in (Select Top 2 Date from Lift Station Database Table order by Date Desc);


There is my query with fields... Couple of questions... First off, im trying to get the difference in Running Amps for Pump 1 (that is the exact field) for the most recent 2 dates... And have it calculate the difference...

I get a syntax error, it says missing operator for First(Running Amps for Pump 1)...Not sure what that means

I must be plugging the fields in places im not supposed to or something...

Also, i would like to be able to narrow this down to a specific store number (there is a field called "store #").

Thanks once again...

 

[Jon K]

If table names and field names contain spaces or special characters, they must be put in square brackets.


Try these two queries:

SELECT First(Date) AS [Date 1], First([Running Amps for Pump 1]) AS [Amp 1],
Last(Date) AS [Date 2], Last([Running Amps for Pump 1]) AS [Amp 2],
iif([Date 1] < [Date 2], Last(val([Running Amps for Pump 1]))-First(val([Running Amps for Pump 1])), First(val([Running Amps for Pump 1]))-Last(val([Running Amps for Pump 1]))) AS [Difference In Amp]
FROM [Lift Station Database Table]
WHERE Date in (Select Top 2 Date from [Lift Station Database Table] order by Date Desc);


SELECT First([Store #]) AS [Store No:],
First(Date) AS [Date 1], First([Running Amps for Pump 1]) AS [Amp 1],
Last(Date) AS [Date 2], Last([Running Amps for Pump 1]) AS [Amp 2],
iif([Date 1] < [Date 2], Last(val([Running Amps for Pump 1]))-First(val([Running Amps for Pump 1])), First(val([Running Amps for Pump 1]))-Last(val([Running Amps for Pump 1]))) AS [Difference In Amp]
FROM [Lift Station Database Table]
WHERE [Store #]=[Enter Store #] and Date in (Select Top 2 Date from [Lift Station Database Table] WHERE [Store #]=[Enter Store #] order by Date Desc);

The second query includes Store #.

 

[RHomsey]

Ok, we are getting very close now . The query you made above worked for me. But not quite exactly what i want....If im a bother please let me know...

Anyhow, Im not sure how to get these queries to pull exactly what i want. What i want is to pull the 2 most recent dates from one (only one, not 2 stores) store #. And calculate the difference.

I think the queries you made me pull up the 2 most recent records for 2 store numbers?? I need the most recent records calculated for just one store number.

Im probably doing something wrong... But when the second query asked for the store number... it asked for it twice and i put the same in ... but it pulled the most recent record and compared it to the store number i put in. Any ideas? Thanks again..

 

[Jon K]

The first query in my last post pulls the two most recent dates from the Date field regardless of Store #. The second query asks for a Store # (only once) and pulls the two most recent dates for the Store # entered.

When Access asks for something more times than it should, it means Access can't find something stated in the query. It's most likely caused by some inconsistencies in spellings. Check the spellings of the field name Store # in the table and in the query. Check also the spellings of the two instances of [Enter Store #] in the query.


I attach my working sample here. The table [Lift Station Database Table] contains a Store # field:-

Store # --- Date ----- Running Amps for Pump 1
1 --------- 10/6/02 -- 11.284
1 --------- 10/8/02 -- 8.261
1 -------- 10/27/02 -- 7.512
1 -------- 10/28/02 -- 10.23
2 -------- 11/16/02 -- 13.256
2 -------- 11/18/02 -- 9.223
2 -------- 11/20/02 -- 6.111


When the query "Second Query_AmpDiff WithStore#" (which is the second query in my last post) is run, the user should only be asked to enter a Store # once.

When 1 is enter, the query should return:
Store No: -- Date 1 --- Amp 1 -- Date 2 --- Amp 2 - Difference In Amp
1 ---------- 10/27/02 - 7.512 -- 10/28/02 - 10.23 -- 2.718

When 2 is enter, it should return:
Store No: -- Date 1 --- Amp 1 -- Date 2 --- Amp 2 - Difference In Amp
2 ---------- 11/18/02 - 9.223 -- 11/20/02 - 6.111 -- -3.122


(It doesn't matter whether Store # is a numeric or text field.)

Hope this helps.

 

[RHomsey]

Thanks..! You have been a big help and i am forever grateful... Take Care