distinct records ....

J

jer

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
 
M

Michel Walsh

Not sure I got it all right, but I would start with a:


SELECT Object, MIN([date]), MAX([date])
FROM tableName
WHERE Action='Move'
GROUP BY Object


Or, if for *each* action, you want the previous action (for the same object
id):


SELECT a.object, a.[date], b.[date]
FROM ( tableName As a LEFT JOIN tableName As b ON a.object=b.object AND
a.[date]>b.[date])
LEFT JOIN tableName As c ON a.object=c.object AND a.[date] > c.[date]
GROUP BY a.object, a.[date], b.[date]
HAVING b.[date]=MAX(c.[date]) OR b.[date] IS NULL





Hoping it may help,
Vanderghast, Access MVP


jer said:
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
Click to expand...
 
J

John Spencer

The following may get you started.

SELECT Ta.Date, Ta.Action, Ta.Object
, Ta.[Old Value, Ta.[New Value]
, (SELECT Min(Tb.[Date])
FROM tblTest as Tb
WHERE Tb.Action = "move"
And Tb.Object = Ta.Object
And Tb.[Date] > Ta.Date) as NextTime
FROM tblTest as Ta
WHERE Ta.Action = "Move"



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

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
Click to expand...
 
J

jer

Thanks John, however when I try to create a report based on the query I am
getting the following message "Multi-level GROUP BY clause is not allowed in
a subquery." Is there a way around this?
thanks again
--
thanks as always for the help
jer


John Spencer said:
The following may get you started.

SELECT Ta.Date, Ta.Action, Ta.Object
, Ta.[Old Value, Ta.[New Value]
, (SELECT Min(Tb.[Date])
FROM tblTest as Tb
WHERE Tb.Action = "move"
And Tb.Object = Ta.Object
And Tb.[Date] > Ta.Date) as NextTime
FROM tblTest as Ta
WHERE Ta.Action = "Move"



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

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
Click to expand...
Click to expand...
 
J

John Spencer

You will probably have to use the DMin Function instead of the subquery.

The following may get you started.

SELECT Ta.Date, Ta.Action, Ta.Object
, Ta.[Old Value, Ta.[New Value]
, DMIN("[Date]","tblTest",
"Action=""Move"" and Object=""" & Ta.Object &
""" AND [Date] > #" & Ta.Date & "#") as NextTime
FROM tblTest as Ta
WHERE Ta.Action = "Move"


For ease of viewing I put the DMin on multiple lines. When you construct
it you will have to use one line. Also if your date usage is other than
the standard US date format of MM/DD/YYYY, then you will have to do some
work with the date field.

replace "Ta.Date" in the DMin function with
Format(Ta.Date,"yyyy\/mm\/dd")


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks John, however when I try to create a report based on the query I am
getting the following message "Multi-level GROUP BY clause is not allowed in
a subquery." Is there a way around this?
thanks again
Click to expand...

The following may get you started.

SELECT Ta.Date, Ta.Action, Ta.Object
, Ta.[Old Value, Ta.[New Value]
, (SELECT Min(Tb.[Date])
FROM tblTest as Tb
WHERE Tb.Action = "move"
And Tb.Object = Ta.Object
And Tb.[Date] > Ta.Date) as NextTime
FROM tblTest as Ta
WHERE Ta.Action = "Move"
 
J

jer

Thank you John, thank you very much
--
thanks as always for the help
jer

John Spencer said:
You will probably have to use the DMin Function instead of the subquery.

The following may get you started.

SELECT Ta.Date, Ta.Action, Ta.Object
, Ta.[Old Value, Ta.[New Value]
, DMIN("[Date]","tblTest",
"Action=""Move"" and Object=""" & Ta.Object &
""" AND [Date] > #" & Ta.Date & "#") as NextTime
FROM tblTest as Ta
WHERE Ta.Action = "Move"


For ease of viewing I put the DMin on multiple lines. When you construct
it you will have to use one line. Also if your date usage is other than
the standard US date format of MM/DD/YYYY, then you will have to do some
work with the date field.

replace "Ta.Date" in the DMin function with
Format(Ta.Date,"yyyy\/mm\/dd")


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks John, however when I try to create a report based on the query I am
getting the following message "Multi-level GROUP BY clause is not allowed in
a subquery." Is there a way around this?
thanks again
Click to expand...

The following may get you started.

SELECT Ta.Date, Ta.Action, Ta.Object
, Ta.[Old Value, Ta.[New Value]
, (SELECT Min(Tb.[Date])
FROM tblTest as Tb
WHERE Tb.Action = "move"
And Tb.Object = Ta.Object
And Tb.[Date] > Ta.Date) as NextTime
FROM tblTest as Ta
WHERE Ta.Action = "Move"
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Help 0
Subquery not integrating to query to display previous sum of quantities 3
Suming Up Time over the course of days 1
fixing a table to conform to current system 3
Grouping - Distinct 5
STSADM -o export does not appear to handle surveys correctly 4
Combine 2 Charts 2007 Excel 1
Login Logout 0

Top