distinct records ....

Old value and new value are different stops of a work flow and I am trying to
determine how long it the object takes at each stop. The following is a
reqport from the invoice system

Date Action Object Old Value New Value
2/27/07 9:02:36 AM Move INV 20070227090236 InvCreate dTest

2/27/07 9:16:50 AM Move INV 20070227090236 dTest SVSMS

2/27/07 9:19:14 AM Move INV 20070227090236 SVSMS InvRepair

2/27/07 9:21:16 AM Move INV 20070227090236 InvRepair dTest

2/27/07 9:22:09 AM Move INV 20070227090236 dTest SVSMS

2/27/07 9:24:38 AM Move INV 20070227090236 SVSMS CallInv

2/27/07 3:35:25 PM Move INV 20070227090236 CallInv All_dist

2/27/07 3:36:50 PM Move INV 20070227090236 All_dist PrePro

2/27/07 3:37:55 PM Move INV 20070227090236 PrePro RecPC

2/27/07 3:38:13 PM Move INV 20070227090236 RecPC InvEnd

For the most part the following query shows when the invoice is moved,
however from the results of the query there are instances where the invoice
is showing more than once at the same stop.

query

SELECT [tblTest].Date, [tblTest].Action, [tblTest].Object, [tblTest].[Old
Value], [tblTest].[New Value], [tblTest]![Date] AS Before, [tblTest_1]![Date]
AS After, DateDiff("s",[Before],[After]) AS Seconds
FROM [tblTest], [tblTest] AS [tblTest_1]
WHERE ((([tblTest].Action)="Move") AND
(([tblTest].Object)=[tblTest_1]![Object]) AND
(([tblTest_1]![Date])>[tblTest]![Date]) AND (([tblTest_1]![Old
Value])=[tblTest]![New Value]))
ORDER BY [tblTest].Date, [tblTest].Object;

result


Date Action Object Old Value New Value Before After
2/27/07 9:02:36 AM Move TT 20070227090236FLUOR USD622939 W InvCreate dTest
2/27/07 9:02:36 AM 2/27/07 9:22:09 AM
2/27/07 9:02:36 AM Move TT 20070227090236FLUOR USD622939 W InvCreate dTest
2/27/07 9:02:36 AM 2/27/07 9:16:50 AM
2/27/07 9:16:50 AM Move TT 20070227090236FLUOR USD622939 W dTest SVSMS
2/27/07 9:16:50 AM 2/27/07 9:24:38 AM
2/27/07 9:16:50 AM Move TT 20070227090236FLUOR USD622939 W dTest SVSMS
2/27/07 9:16:50 AM 2/27/07 9:19:14 AM
2/27/07 9:19:14 AM Move TT 20070227090236FLUOR USD622939 W SVSMS
InvRepair 2/27/07 9:19:14 AM 2/27/07 9:21:16 AM
2/27/07 9:21:16 AM Move TT 20070227090236FLUOR USD622939 W InvRepair dTest
2/27/07 9:21:16 AM 2/27/07 9:22:09 AM
2/27/07 9:22:09 AM Move TT 20070227090236FLUOR USD622939 W dTest SVSMS
2/27/07 9:22:09 AM 2/27/07 9:24:38 AM
2/27/07 9:24:38 AM Move TT 20070227090236FLUOR USD622939 W SVSMS CallInv
2/27/07 9:24:38 AM 2/27/07 3:35:25 PM
2/27/07 3:35:25 PM Move TT 20070227090236FLUOR USD622939 W CallInv
All_dist 2/27/07 3:35:25 PM 2/27/07 3:36:50 PM
2/27/07 3:36:50 PM Move TT 20070227090236FLUOR USD622939 W Allocate PrePro
2/27/07 3:36:50 PM 2/27/07 3:37:55 PM
2/27/07 3:37:55 PM Move TT 20070227090236FLUOR USD622939 W PrePro RecPC
2/27/07 3:37:55 PM 2/27/07 3:38:13 PM

I have tried using select distinct however the result is the same. Is there
a way to write the query that is shows each stop only once, e.g., it show
InvCreate and dtest 2 times


--
thanks as always for the help
jer
.

Relevant Pages

  • RE: Query stopped working after entry 615 in table
    ... Macro Name: Find Jenish Invoice ... Table Name: Previous Invoices For Tracking ... How many records does Jenish Invoice Query pull? ...
    (microsoft.public.access.queries)
  • RE: Saving Form
    ... If it is a query, ... You HAVE the computer record of the invoice that you want. ... > everything we need to know about our inventory ex. ... >> would execute a report for the current record. ...
    (microsoft.public.access.forms)
  • RE: Saving Form
    ... ok well the form is bound to a query where the only thing in it is the "in ... You HAVE the computer record of the invoice that you want. ... >> everything we need to know about our inventory ex. ... >>> would execute a report for the current record. ...
    (microsoft.public.access.forms)
  • RE: Saving Form
    ... There would be no need to print an extra hard copy of each invoice ... would execute a report for the current record. ... an Update query is simply a type of query that changes data in ... Show the InvoiceItems and the Inventory tables. ...
    (microsoft.public.access.forms)
  • Re: Can (should) an auto number be set up on a invoice (report)?
    ... The staff takes the customer information (stored in the ... The staff then records what procedures the customer wants ... This table stores Customer ID, Concatenated Customer Name (separate query), ... Procedure, Cost, Sales Tax) except for the invoice number. ...
    (microsoft.public.access.reports)
Loading