different value between each record

  • Thread starter Thread starter Dirk
  • Start date Start date
D

Dirk

Hi, Im (almost desperately) looking for a solution how to
determine the difference between the values in
column/field per record? Inspite of my "search for the
holy grale" no luck yet.

e.g. :
field01 field02 field03
= date = dayly production difference

01/01/01 100 100
01/02/01 152 52 (more produded)
01/03/01 172 20 (more produced)
01/04/01 156 -16 (less produced)..

Thanks a mille for your help.
Dirk
 
It's just like a checkbook, but databases aren't real good at this, because
what should happen to the difference field, if you sorted the table by
field02?

You can produce the balance field on a report, easily. Anything other than
that will require some VBA code.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Steve has commented regarding reports.

If you want the value in the context of a form, try this:

Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

If you must get the value in a query, bear in mind that any further filter
or resorting that gets applied (e.g. in a form, report, or stacked query)
will result in wrong answers. It generally involves something like a
subquery to get the result, but then your query is read only. Example:
PriorValue: ( SELECT TOP 1 SomeField FROM MyTable AS Dupe WHERE Dupe.ID
< MyTable.ID ORDER BY ID DESC )
 
OK ... I throw in my Query suggestion also. Assume you have in [YourTable]
the Fields [Field1] and [Field2] as posted, try:

SELECT CPT.Field1, CPT.Field2 - Nz(PPT.Field2, 0) AS [Difference]
FROM [YourTable] AS CPT, [YourTable] AS PPT
WHERE PPT.Field1 =
( SELECT Max(SQ.Field1)
FROM [YourTable] AS SQT
WHERE SQT.Field1 < CPT.Field1 )
ORDER BY CPT.Field1

3 copies of [YourTable] are used. The meaning of the aliases are:

CPT: Current Production Table
PPT: Previous (day) Production Table
SQT: Sub-Query Table
 
Greetings,

Doesn't it seem ridiculous that in the 21 century
you should be desperate to do something so
simple?And to waste time on such a straightforward
thing?If you could move your data out of Access to
any version of MS Sql Server 2000 (including msde)
you would have your *holy grale* in the RAC utility.
And no coding to worry about:).RAC exists to make
tasks that should be simple in reality simple.
For example to get your differences all you need is:

Exec Rac
@transform='sum(field02) as sum02',
@rows='field01',
@pvtcol='Report Mode',
@from='yourtable',
@diffs='(sum02)'

And you could do it thru a gui too.

RAC v2.2 and QALite for Sql Server 2000 @
www.rac4sql.net
 
Dirk,

Whenever I've needed to do this, I start out with a query that tells
me what the previous production date is for any given date. That way,
if there is no production on a particular date (Sat/Sunday), Mondays
production will be compared to Friday. I used a table (tblProduction)
that contained two fields (ProdDate and ProdQty) for this example.

Query #1(qryPrevDate):

SELECT tblProduction.ProdDate, Max(Previous.ProdDate) AS PrevDate
FROM tblProduction, tblProduction AS Previous
WHERE Previous.ProdDate<[tblProduction].[ProdDate]
GROUP BY tblProduction.ProdDate
ORDER BY tblProduction.ProdDate;

Query #2 then uses Query #1 as an intermediate linking table between
two copies of the production table. I renamed the second instance of
the production table as PrevProduction to eliminate confusion. Use
Left joins between the production table, query #1, and the
PrevProduction table to make sure you get the record associated with
the first production date. If you want to put limits the range of
dates in your report, do that in this query.

SELECT tblProduction.ProdDate
, tblProduction.ProdQty
, qryPrevDate.PrevDate
, PrevProduction.ProdQty AS PrevQty
, [tblProduction].[ProdQty]-[PrevProduction].[ProdQty] AS
Delta
FROM (tblProduction LEFT JOIN qryPrevDate ON tblProduction.ProdDate =
qryPrevDate.ProdDate)
LEFT JOIN tblProduction AS PrevProduction ON qryPrevDate.PrevDate =
PrevProduction.ProdDate;


--
HTH

Dale Fye


Hi, Im (almost desperately) looking for a solution how to
determine the difference between the values in
column/field per record? Inspite of my "search for the
holy grale" no luck yet.

e.g. :
field01 field02 field03
= date = dayly production difference

01/01/01 100 100
01/02/01 152 52 (more produded)
01/03/01 172 20 (more produced)
01/04/01 156 -16 (less produced)..

Thanks a mille for your help.
Dirk
 
Back
Top