Complex Query (I Think)

M

Mike

Please help, I am pulling out my hair on this one.

I have a query I need to compare the previous record with these current
record and calculate the difference in Oil or Gas from the previous day.
And unsure how to do this.

Example:
Date Product Gals
5/3/05 Gas 5
5/2/05 Gas 4
5/1/05 Gas 6
5/3/05 Oil 10
5/2/05 Oil 12
5/1/05 Oil 3

Output/Result should be:
(Need to Calculate the Change from Day to Day)
Date Product Gals Change
5/3/05 Gas 5 1
5/2/05 Gas 4 -2
5/1/05 Gas 6 Nothing
5/3/05 Oil 10 -2
5/2/05 Oil 12 10
5/1/05 Oil 3 Nothing

Thank you in advance for any help!!!!!
Mike
 
C

Chaim

Use a subquery to get the previous days record.

SELECT [YT1].[ReadingDate], [Product], [Gals],
[Gals] - (select [YT2].[Gals] from [Your Table] YT2
where YT2.[ReadingDate] = [[YT1].[ReadingDate] -
1)
FROM [Your Table] YT1
ORDER BY [YT1].[ReadingDate];

I am not certain of the syntax- there may be ambiguous references in here-
but that is the approach you want to use.

Good Luck!
 
K

Ken Snell [MVP]

Try this:

SELECT Y.[Date], Y.[Product], Y.[Gals], ([Gals] - (SELECT TOP 1
T.[Gals] FROM YourTableName AS T WHERE T.[Date] <
Y.[Date] And T.[Product]=Y.[Product] ORDER BY T.[Date] DESC))
AS [Change] FROM YourTableName AS Y
ORDER BY Y.[Date] DESC, Y.[Product];
 
C

Chaim

Left out one item from the subquery:

where [YT2].[ReadingDate] = [YT1].[ReadingDate] - 1
and [YT2].[Product] = [YT1].[Product])


--

Chaim


Chaim said:
Use a subquery to get the previous days record.

SELECT [YT1].[ReadingDate], [Product], [Gals],
[Gals] - (select [YT2].[Gals] from [Your Table] YT2
where YT2.[ReadingDate] = [[YT1].[ReadingDate] -
1)
FROM [Your Table] YT1
ORDER BY [YT1].[ReadingDate];

I am not certain of the syntax- there may be ambiguous references in here-
but that is the approach you want to use.

Good Luck!
--
----
Chaim
Mike said:
Please help, I am pulling out my hair on this one.

I have a query I need to compare the previous record with these current
record and calculate the difference in Oil or Gas from the previous day.
And unsure how to do this.

Example:
Date Product Gals
5/3/05 Gas 5
5/2/05 Gas 4
5/1/05 Gas 6
5/3/05 Oil 10
5/2/05 Oil 12
5/1/05 Oil 3

Output/Result should be:
(Need to Calculate the Change from Day to Day)
Date Product Gals Change
5/3/05 Gas 5 1
5/2/05 Gas 4 -2
5/1/05 Gas 6 Nothing
5/3/05 Oil 10 -2
5/2/05 Oil 12 10
5/1/05 Oil 3 Nothing

Thank you in advance for any help!!!!!
Mike
Click to expand...
Click to expand...
 
M

Michael Kintner

Thank you very much KEN!!!! It worked!!!!


Ken Snell said:
Try this:

SELECT Y.[Date], Y.[Product], Y.[Gals], ([Gals] - (SELECT TOP 1
T.[Gals] FROM YourTableName AS T WHERE T.[Date] <
Y.[Date] And T.[Product]=Y.[Product] ORDER BY T.[Date] DESC))
AS [Change] FROM YourTableName AS Y
ORDER BY Y.[Date] DESC, Y.[Product];

--

Ken Snell
<MS ACCESS MVP>


Mike said:
Please help, I am pulling out my hair on this one.

I have a query I need to compare the previous record with these current
record and calculate the difference in Oil or Gas from the previous day.
And unsure how to do this.

Example:
Date Product Gals
5/3/05 Gas 5
5/2/05 Gas 4
5/1/05 Gas 6
5/3/05 Oil 10
5/2/05 Oil 12
5/1/05 Oil 3

Output/Result should be:
(Need to Calculate the Change from Day to Day)
Date Product Gals Change
5/3/05 Gas 5 1
5/2/05 Gas 4 -2
5/1/05 Gas 6 Nothing
5/3/05 Oil 10 -2
5/2/05 Oil 12 10
5/1/05 Oil 3 Nothing

Thank you in advance for any help!!!!!
Mike
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

You're welcome.

Note that it's not a good idea to use Date as the name of a field. See these
Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

--

Ken Snell
<MS ACCESS MVP>


Michael Kintner said:
Thank you very much KEN!!!! It worked!!!!


Ken Snell said:
Try this:

SELECT Y.[Date], Y.[Product], Y.[Gals], ([Gals] - (SELECT TOP 1
T.[Gals] FROM YourTableName AS T WHERE T.[Date] <
Y.[Date] And T.[Product]=Y.[Product] ORDER BY T.[Date] DESC))
AS [Change] FROM YourTableName AS Y
ORDER BY Y.[Date] DESC, Y.[Product];

--

Ken Snell
<MS ACCESS MVP>


Mike said:
Please help, I am pulling out my hair on this one.

I have a query I need to compare the previous record with these current
record and calculate the difference in Oil or Gas from the previous day.
And unsure how to do this.

Example:
Date Product Gals
5/3/05 Gas 5
5/2/05 Gas 4
5/1/05 Gas 6
5/3/05 Oil 10
5/2/05 Oil 12
5/1/05 Oil 3

Output/Result should be:
(Need to Calculate the Change from Day to Day)
Date Product Gals Change
5/3/05 Gas 5 1
5/2/05 Gas 4 -2
5/1/05 Gas 6 Nothing
5/3/05 Oil 10 -2
5/2/05 Oil 12 10
5/1/05 Oil 3 Nothing

Thank you in advance for any help!!!!!
Mike
Click to expand...
Click to expand...
Click to expand...
 
Top