tracking log times ...

[Guest]

Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it takes to move
from 1 station to the next (that is from old value to new value. From the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and 24 minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value], Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer examination of the
query results (and reports) some information is distoretd. I am not sure how
to inclued the last move. The original source of the log is an excel file,
so before importing into access I added a row of data and changed the values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily log where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Tom Ellison]

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you presume these
values NEVER REPEAT. Could they? Is this a record of changing channels on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences, perhaps
identified in the Name column? What we would call "Groups" in database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time column is
the only true way to find the "previous" row for this comparison. Now, this
could cause sequencing problems. Is this value recorded only to the nearest
minute, or is your example just formatted to show only that much resolution?

Specifically, it would be most workable to have Name and Date/Time together
be unique. As an alternative, we can try to sort this out by also filtering
where the New Value of the previous row is equal to the Old Value of the
subsequent row. While this may help, it isn't foolproof. Consider this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2 1
W53 3/20/2006 10:45 Station2 Station1 2
W53 3/20/2006 10:45 Station1 Station2 3

Now, in the above, does the No column uniquely number the rows for a given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time, then this may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending on the
characteristics of your data. I do not know these characteristics. I'll
need your help to understand them.

Tom Ellison

Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it takes to
move
from 1 station to the next (that is from old value to new value. From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and 24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer examination of the
query results (and reports) some information is distoretd. I am not sure
how
to inclued the last move. The original source of the log is an excel
file,
so before importing into access I added a row of data and changed the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily log where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Guest]

Dear Tom
Thanks for your quick response. Unfortunately, I am unable to test the
query at this time, but thought that I should answer your questions none the
less
1 The old value = new value may repeat for a given name
2 The time/date values actually inclued seconds -3/16/2006 3:47:52 PM and
this is a unique value for every record in the log
The No does uniquely numer the rows for a given name (for a given date). If
a name does not pass all stations today it will continue on to the next
station tomorrow. That said though, I am querying the log daily to get the
time differences by day. Also, I am not sure that this will make a
difference or not, but when I import from excel, the access does not keep the
sort order - name, date time
Will keep you posted though
thanks again
--
thanks as always for the help
jer

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you presume these
values NEVER REPEAT. Could they? Is this a record of changing channels on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences, perhaps
identified in the Name column? What we would call "Groups" in database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time column is
the only true way to find the "previous" row for this comparison. Now, this
could cause sequencing problems. Is this value recorded only to the nearest
minute, or is your example just formatted to show only that much resolution?

Specifically, it would be most workable to have Name and Date/Time together
be unique. As an alternative, we can try to sort this out by also filtering
where the New Value of the previous row is equal to the Old Value of the
subsequent row. While this may help, it isn't foolproof. Consider this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2 1
W53 3/20/2006 10:45 Station2 Station1 2
W53 3/20/2006 10:45 Station1 Station2 3

Now, in the above, does the No column uniquely number the rows for a given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time, then this may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending on the
characteristics of your data. I do not know these characteristics. I'll
need your help to understand them.

Tom Ellison

Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it takes to
move
from 1 station to the next (that is from old value to new value. From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and 24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer examination of the
query results (and reports) some information is distoretd. I am not sure
how
to inclued the last move. The original source of the log is an excel
file,
so before importing into access I added a row of data and changed the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily log where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Guest]

Dear Tom
Thanks for the quick response. Unfornutaley I am unable to test the the
query however I thought that I should at least answer your questions
1 OLD VALUE = NEW VALUE can repeat for a given name
2 The date/time is unique for every record in the log and include seconds -
3/16/2006 3:47:52 PM
The No does uniquely number the rows for a given name.
The stations are folders on computers on a lan accessed by certain
individuals. The name is a document that moves from person to person and I
am trying to determine how long an individual takes with a document before
moving it along
I am sure to keep you posted with my findings
thanks again
--
thanks as always for the help
jer

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you presume these
values NEVER REPEAT. Could they? Is this a record of changing channels on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences, perhaps
identified in the Name column? What we would call "Groups" in database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time column is
the only true way to find the "previous" row for this comparison. Now, this
could cause sequencing problems. Is this value recorded only to the nearest
minute, or is your example just formatted to show only that much resolution?

Specifically, it would be most workable to have Name and Date/Time together
be unique. As an alternative, we can try to sort this out by also filtering
where the New Value of the previous row is equal to the Old Value of the
subsequent row. While this may help, it isn't foolproof. Consider this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2 1
W53 3/20/2006 10:45 Station2 Station1 2
W53 3/20/2006 10:45 Station1 Station2 3

Now, in the above, does the No column uniquely number the rows for a given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time, then this may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending on the
characteristics of your data. I do not know these characteristics. I'll
need your help to understand them.

Tom Ellison

Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it takes to
move
from 1 station to the next (that is from old value to new value. From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and 24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer examination of the
query results (and reports) some information is distoretd. I am not sure
how
to inclued the last move. The original source of the log is an excel
file,
so before importing into access I added a row of data and changed the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily log where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Tom Ellison]

Dear Jer:

On the basis of uniqueness of Name and Date/Time together, I believe this
can be solved:

SELECT [Name], [Date/Time], [Old Value], [New Value], No,
(SELECT MAX([Date/Time])
FROM Log L1
WHERE L1.[Name] = L.[Name]
AND L1.[Date/Time] < L.[Date/Time])
AS Previous
FROM Log L
ORDER BY [Name], [Date/Time]

You can readily calculate the elapsed time using this.

Please let me know if this looks good.

Tom Ellison

Dear Tom
Thanks for your quick response. Unfortunately, I am unable to test the
query at this time, but thought that I should answer your questions none
the
less
1 The old value = new value may repeat for a given name
2 The time/date values actually inclued seconds -3/16/2006 3:47:52 PM and
this is a unique value for every record in the log
The No does uniquely numer the rows for a given name (for a given date).
If
a name does not pass all stations today it will continue on to the next
station tomorrow. That said though, I am querying the log daily to get
the
time differences by day. Also, I am not sure that this will make a
difference or not, but when I import from excel, the access does not keep
the
sort order - name, date time
Will keep you posted though
thanks again
--
thanks as always for the help
jer

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you presume
these
values NEVER REPEAT. Could they? Is this a record of changing channels
on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences, perhaps
identified in the Name column? What we would call "Groups" in database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time column is
the only true way to find the "previous" row for this comparison. Now,
this
could cause sequencing problems. Is this value recorded only to the
nearest
minute, or is your example just formatted to show only that much
resolution?

Specifically, it would be most workable to have Name and Date/Time
together
be unique. As an alternative, we can try to sort this out by also
filtering
where the New Value of the previous row is equal to the Old Value of the
subsequent row. While this may help, it isn't foolproof. Consider this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2 1
W53 3/20/2006 10:45 Station2 Station1 2
W53 3/20/2006 10:45 Station1 Station2 3

Now, in the above, does the No column uniquely number the rows for a
given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time, then this
may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending on the
characteristics of your data. I do not know these characteristics. I'll
need your help to understand them.

Tom Ellison

Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it takes to
move
from 1 station to the next (that is from old value to new value. From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and 24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer examination of
the
query results (and reports) some information is distoretd. I am not
sure
how
to inclued the last move. The original source of the log is an excel
file,
so before importing into access I added a row of data and changed the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily log
where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Guest]

Dear Tom
Yes the query works fine. I have included the datediff function and that
works as well. Now I am trying to create a report based on the query and I
am getting a dialog box with the following:-
Multi-level GROUP BY clauses is not allowed in a subquery
Any suggestions on how to get around this
Thanks again
--
thanks as always for the help
jer

Dear Jer:

On the basis of uniqueness of Name and Date/Time together, I believe this
can be solved:

SELECT [Name], [Date/Time], [Old Value], [New Value], No,
(SELECT MAX([Date/Time])
FROM Log L1
WHERE L1.[Name] = L.[Name]
AND L1.[Date/Time] < L.[Date/Time])
AS Previous
FROM Log L
ORDER BY [Name], [Date/Time]

You can readily calculate the elapsed time using this.

Please let me know if this looks good.

Tom Ellison

Dear Tom
Thanks for your quick response. Unfortunately, I am unable to test the
query at this time, but thought that I should answer your questions none
the
less
1 The old value = new value may repeat for a given name
2 The time/date values actually inclued seconds -3/16/2006 3:47:52 PM and
this is a unique value for every record in the log
The No does uniquely numer the rows for a given name (for a given date).
If
a name does not pass all stations today it will continue on to the next
station tomorrow. That said though, I am querying the log daily to get
the
time differences by day. Also, I am not sure that this will make a
difference or not, but when I import from excel, the access does not keep
the
sort order - name, date time
Will keep you posted though
thanks again
--
thanks as always for the help
jer

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you presume
these
values NEVER REPEAT. Could they? Is this a record of changing channels
on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences, perhaps
identified in the Name column? What we would call "Groups" in database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time column is
the only true way to find the "previous" row for this comparison. Now,
this
could cause sequencing problems. Is this value recorded only to the
nearest
minute, or is your example just formatted to show only that much
resolution?

Specifically, it would be most workable to have Name and Date/Time
together
be unique. As an alternative, we can try to sort this out by also
filtering
where the New Value of the previous row is equal to the Old Value of the
subsequent row. While this may help, it isn't foolproof. Consider this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2 1
W53 3/20/2006 10:45 Station2 Station1 2
W53 3/20/2006 10:45 Station1 Station2 3

Now, in the above, does the No column uniquely number the rows for a
given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time, then this
may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending on the
characteristics of your data. I do not know these characteristics. I'll
need your help to understand them.

Tom Ellison


Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it takes to
move
from 1 station to the next (that is from old value to new value. From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and 24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer examination of
the
query results (and reports) some information is distoretd. I am not
sure
how
to inclued the last move. The original source of the log is an excel
file,
so before importing into access I added a row of data and changed the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily log
where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Tom Ellison]

Dear Jer:

As the query I sent had no GROUP BY, I'm thinking you are using something
else. If so, I don't know what it is. Could you post the query giving this
error? Does the query that gives this error work when it is not used for a
report?

Tom Ellison

Dear Tom
Yes the query works fine. I have included the datediff function and that
works as well. Now I am trying to create a report based on the query and
I
am getting a dialog box with the following:-
Multi-level GROUP BY clauses is not allowed in a subquery
Any suggestions on how to get around this
Thanks again
--
thanks as always for the help
jer

Dear Jer:

On the basis of uniqueness of Name and Date/Time together, I believe this
can be solved:

SELECT [Name], [Date/Time], [Old Value], [New Value], No,
(SELECT MAX([Date/Time])
FROM Log L1
WHERE L1.[Name] = L.[Name]
AND L1.[Date/Time] < L.[Date/Time])
AS Previous
FROM Log L
ORDER BY [Name], [Date/Time]

You can readily calculate the elapsed time using this.

Please let me know if this looks good.

Tom Ellison

Dear Tom
Thanks for your quick response. Unfortunately, I am unable to test the
query at this time, but thought that I should answer your questions
none
the
less
1 The old value = new value may repeat for a given name
2 The time/date values actually inclued seconds -3/16/2006 3:47:52 PM
and
this is a unique value for every record in the log
The No does uniquely numer the rows for a given name (for a given
date).
If
a name does not pass all stations today it will continue on to the next
station tomorrow. That said though, I am querying the log daily to get
the
time differences by day. Also, I am not sure that this will make a
difference or not, but when I import from excel, the access does not
keep
the
sort order - name, date time
Will keep you posted though
thanks again
--
thanks as always for the help
jer

:

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you presume
these
values NEVER REPEAT. Could they? Is this a record of changing
channels
on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences, perhaps
identified in the Name column? What we would call "Groups" in
database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time column
is
the only true way to find the "previous" row for this comparison.
Now,
this
could cause sequencing problems. Is this value recorded only to the
nearest
minute, or is your example just formatted to show only that much
resolution?

Specifically, it would be most workable to have Name and Date/Time
together
be unique. As an alternative, we can try to sort this out by also
filtering
where the New Value of the previous row is equal to the Old Value of
the
subsequent row. While this may help, it isn't foolproof. Consider
this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2
1
W53 3/20/2006 10:45 Station2 Station1
2
W53 3/20/2006 10:45 Station1 Station2
3

Now, in the above, does the No column uniquely number the rows for a
given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time, then
this
may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending on the
characteristics of your data. I do not know these characteristics.
I'll
need your help to understand them.

Tom Ellison


Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it takes
to
move
from 1 station to the next (that is from old value to new value.
From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and 24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer examination
of
the
query results (and reports) some information is distoretd. I am not
sure
how
to inclued the last move. The original source of the log is an
excel
file,
so before importing into access I added a row of data and changed
the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily log
where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Guest]

Dear Tom
As requested,
SELECT Log.Name, Log.[Date/Time], Log.[Old Value], Log.[New Value],
Log.[No], (SELECT MAX([Date/Time]) FROM Log AS L1 WHERE L1.[Name] =
Log.[Name] AND L1.[Date/Time] < Log.[Date/Time]) AS Previous,
DateDiff("s",[Previous],[Date/Time]) AS Seconds
FROM Log
ORDER BY Log.Name, Log.[Date/Time];

I pasted what you sent and when I reviewew the statement the top line is
prefixed with "log" and also I added in "Log as" L1

--
thanks as always for the help
jer

Dear Jer:

As the query I sent had no GROUP BY, I'm thinking you are using something
else. If so, I don't know what it is. Could you post the query giving this
error? Does the query that gives this error work when it is not used for a
report?

Tom Ellison

Dear Tom
Yes the query works fine. I have included the datediff function and that
works as well. Now I am trying to create a report based on the query and
I
am getting a dialog box with the following:-
Multi-level GROUP BY clauses is not allowed in a subquery
Any suggestions on how to get around this
Thanks again
--
thanks as always for the help
jer

Dear Jer:

On the basis of uniqueness of Name and Date/Time together, I believe this
can be solved:

SELECT [Name], [Date/Time], [Old Value], [New Value], No,
(SELECT MAX([Date/Time])
FROM Log L1
WHERE L1.[Name] = L.[Name]
AND L1.[Date/Time] < L.[Date/Time])
AS Previous
FROM Log L
ORDER BY [Name], [Date/Time]

You can readily calculate the elapsed time using this.

Please let me know if this looks good.

Tom Ellison


Dear Tom
Thanks for your quick response. Unfortunately, I am unable to test the
query at this time, but thought that I should answer your questions
none
the
less
1 The old value = new value may repeat for a given name
2 The time/date values actually inclued seconds -3/16/2006 3:47:52 PM
and
this is a unique value for every record in the log
The No does uniquely numer the rows for a given name (for a given
date).
If
a name does not pass all stations today it will continue on to the next
station tomorrow. That said though, I am querying the log daily to get
the
time differences by day. Also, I am not sure that this will make a
difference or not, but when I import from excel, the access does not
keep
the
sort order - name, date time
Will keep you posted though
thanks again
--
thanks as always for the help
jer

:

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you presume
these
values NEVER REPEAT. Could they? Is this a record of changing
channels
on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences, perhaps
identified in the Name column? What we would call "Groups" in
database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time column
is
the only true way to find the "previous" row for this comparison.
Now,
this
could cause sequencing problems. Is this value recorded only to the
nearest
minute, or is your example just formatted to show only that much
resolution?

Specifically, it would be most workable to have Name and Date/Time
together
be unique. As an alternative, we can try to sort this out by also
filtering
where the New Value of the previous row is equal to the Old Value of
the
subsequent row. While this may help, it isn't foolproof. Consider
this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2
1
W53 3/20/2006 10:45 Station2 Station1
2
W53 3/20/2006 10:45 Station1 Station2
3

Now, in the above, does the No column uniquely number the rows for a
given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time, then
this
may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending on the
characteristics of your data. I do not know these characteristics.
I'll
need your help to understand them.

Tom Ellison


Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it takes
to
move
from 1 station to the next (that is from old value to new value.
From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and 24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer examination
of
the
query results (and reports) some information is distoretd. I am not
sure
how
to inclued the last move. The original source of the log is an
excel
file,
so before importing into access I added a row of data and changed
the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily log
where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Tom Ellison]

Dear Jer:

Please answer the question:

"Does the query that gives this error work when it is not used for a
report?"

Are you using this query as a saved query for the report, or have you put
the query code into the report?

Tom Ellison

Dear Tom
As requested,
SELECT Log.Name, Log.[Date/Time], Log.[Old Value], Log.[New Value],
Log.[No], (SELECT MAX([Date/Time]) FROM Log AS L1 WHERE L1.[Name] =
Log.[Name] AND L1.[Date/Time] < Log.[Date/Time]) AS Previous,
DateDiff("s",[Previous],[Date/Time]) AS Seconds
FROM Log
ORDER BY Log.Name, Log.[Date/Time];

I pasted what you sent and when I reviewew the statement the top line is
prefixed with "log" and also I added in "Log as" L1

--
thanks as always for the help
jer

Dear Jer:

As the query I sent had no GROUP BY, I'm thinking you are using something
else. If so, I don't know what it is. Could you post the query giving
this
error? Does the query that gives this error work when it is not used for
a
report?

Tom Ellison

Dear Tom
Yes the query works fine. I have included the datediff function and
that
works as well. Now I am trying to create a report based on the query
and
I
am getting a dialog box with the following:-
Multi-level GROUP BY clauses is not allowed in a subquery
Any suggestions on how to get around this
Thanks again
--
thanks as always for the help
jer


:

Dear Jer:

On the basis of uniqueness of Name and Date/Time together, I believe
this
can be solved:

SELECT [Name], [Date/Time], [Old Value], [New Value], No,
(SELECT MAX([Date/Time])
FROM Log L1
WHERE L1.[Name] = L.[Name]
AND L1.[Date/Time] < L.[Date/Time])
AS Previous
FROM Log L
ORDER BY [Name], [Date/Time]

You can readily calculate the elapsed time using this.

Please let me know if this looks good.

Tom Ellison


Dear Tom
Thanks for your quick response. Unfortunately, I am unable to test
the
query at this time, but thought that I should answer your questions
none
the
less
1 The old value = new value may repeat for a given name
2 The time/date values actually inclued seconds -3/16/2006 3:47:52
PM
and
this is a unique value for every record in the log
The No does uniquely numer the rows for a given name (for a given
date).
If
a name does not pass all stations today it will continue on to the
next
station tomorrow. That said though, I am querying the log daily to
get
the
time differences by day. Also, I am not sure that this will make a
difference or not, but when I import from excel, the access does not
keep
the
sort order - name, date time
Will keep you posted though
thanks again
--
thanks as always for the help
jer

:

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you
presume
these
values NEVER REPEAT. Could they? Is this a record of changing
channels
on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences,
perhaps
identified in the Name column? What we would call "Groups" in
database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time
column
is
the only true way to find the "previous" row for this comparison.
Now,
this
could cause sequencing problems. Is this value recorded only to
the
nearest
minute, or is your example just formatted to show only that much
resolution?

Specifically, it would be most workable to have Name and Date/Time
together
be unique. As an alternative, we can try to sort this out by also
filtering
where the New Value of the previous row is equal to the Old Value
of
the
subsequent row. While this may help, it isn't foolproof. Consider
this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2
1
W53 3/20/2006 10:45 Station2 Station1
2
W53 3/20/2006 10:45 Station1 Station2
3

Now, in the above, does the No column uniquely number the rows for
a
given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time, then
this
may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending on
the
characteristics of your data. I do not know these characteristics.
I'll
need your help to understand them.

Tom Ellison


Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it
takes
to
move
from 1 station to the next (that is from old value to new value.
From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and
24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer
examination
of
the
query results (and reports) some information is distoretd. I am
not
sure
how
to inclued the last move. The original source of the log is an
excel
file,
so before importing into access I added a row of data and changed
the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily
log
where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Guest]

Dear Tom
Yes it does
--
thanks as always for the help
jer

Dear Jer:

Please answer the question:

"Does the query that gives this error work when it is not used for a
report?"

Are you using this query as a saved query for the report, or have you put
the query code into the report?

Tom Ellison

Dear Tom
As requested,
SELECT Log.Name, Log.[Date/Time], Log.[Old Value], Log.[New Value],
Log.[No], (SELECT MAX([Date/Time]) FROM Log AS L1 WHERE L1.[Name] =
Log.[Name] AND L1.[Date/Time] < Log.[Date/Time]) AS Previous,
DateDiff("s",[Previous],[Date/Time]) AS Seconds
FROM Log
ORDER BY Log.Name, Log.[Date/Time];

I pasted what you sent and when I reviewew the statement the top line is
prefixed with "log" and also I added in "Log as" L1

--
thanks as always for the help
jer

Dear Jer:

As the query I sent had no GROUP BY, I'm thinking you are using something
else. If so, I don't know what it is. Could you post the query giving
this
error? Does the query that gives this error work when it is not used for
a
report?

Tom Ellison


Dear Tom
Yes the query works fine. I have included the datediff function and
that
works as well. Now I am trying to create a report based on the query
and
I
am getting a dialog box with the following:-
Multi-level GROUP BY clauses is not allowed in a subquery
Any suggestions on how to get around this
Thanks again
--
thanks as always for the help
jer


:

Dear Jer:

On the basis of uniqueness of Name and Date/Time together, I believe
this
can be solved:

SELECT [Name], [Date/Time], [Old Value], [New Value], No,
(SELECT MAX([Date/Time])
FROM Log L1
WHERE L1.[Name] = L.[Name]
AND L1.[Date/Time] < L.[Date/Time])
AS Previous
FROM Log L
ORDER BY [Name], [Date/Time]

You can readily calculate the elapsed time using this.

Please let me know if this looks good.

Tom Ellison


Dear Tom
Thanks for your quick response. Unfortunately, I am unable to test
the
query at this time, but thought that I should answer your questions
none
the
less
1 The old value = new value may repeat for a given name
2 The time/date values actually inclued seconds -3/16/2006 3:47:52
PM
and
this is a unique value for every record in the log
The No does uniquely numer the rows for a given name (for a given
date).
If
a name does not pass all stations today it will continue on to the
next
station tomorrow. That said though, I am querying the log daily to
get
the
time differences by day. Also, I am not sure that this will make a
difference or not, but when I import from excel, the access does not
keep
the
sort order - name, date time
Will keep you posted though
thanks again
--
thanks as always for the help
jer

:

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you
presume
these
values NEVER REPEAT. Could they? Is this a record of changing
channels
on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences,
perhaps
identified in the Name column? What we would call "Groups" in
database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time
column
is
the only true way to find the "previous" row for this comparison.
Now,
this
could cause sequencing problems. Is this value recorded only to
the
nearest
minute, or is your example just formatted to show only that much
resolution?

Specifically, it would be most workable to have Name and Date/Time
together
be unique. As an alternative, we can try to sort this out by also
filtering
where the New Value of the previous row is equal to the Old Value
of
the
subsequent row. While this may help, it isn't foolproof. Consider
this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2
1
W53 3/20/2006 10:45 Station2 Station1
2
W53 3/20/2006 10:45 Station1 Station2
3

Now, in the above, does the No column uniquely number the rows for
a
given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time, then
this
may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending on
the
characteristics of your data. I do not know these characteristics.
I'll
need your help to understand them.

Tom Ellison


Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it
takes
to
move
from 1 station to the next (that is from old value to new value.
From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and
24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer
examination
of
the
query results (and reports) some information is distoretd. I am
not
sure
how
to inclued the last move. The original source of the log is an
excel
file,
so before importing into access I added a row of data and changed
the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily
log
where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Tom Ellison]

Dear Jer:

The problem is then not in the query. It is in the facilities added to the
query by the form.

It is probably a mistake to build up a report without first perfecting the
query on which it is based. I expect that something you have done with the
data in the report is causing the problem. If you were just building the
report, and testing the features as you add them, then you would know when
you had just added a feature that causes a problem.

Finding it after the fact will be more difficult. Perhaps you would be well
off to start making the report again from scratch at least up to the point
where you start having this problem. Copy and paste from the existing
report till one of the properties causes this problem.

It is also possible that the experts in the access.reports newsgroup may be
more familiar with what could cause this.

Tom Ellison

Dear Tom
Yes it does
--
thanks as always for the help
jer

Dear Jer:

Please answer the question:

"Does the query that gives this error work when it is not used for a
report?"

Are you using this query as a saved query for the report, or have you put
the query code into the report?

Tom Ellison

Dear Tom
As requested,
SELECT Log.Name, Log.[Date/Time], Log.[Old Value], Log.[New Value],
Log.[No], (SELECT MAX([Date/Time]) FROM Log AS L1 WHERE L1.[Name] =
Log.[Name] AND L1.[Date/Time] < Log.[Date/Time]) AS Previous,
DateDiff("s",[Previous],[Date/Time]) AS Seconds
FROM Log
ORDER BY Log.Name, Log.[Date/Time];

I pasted what you sent and when I reviewew the statement the top line
is
prefixed with "log" and also I added in "Log as" L1

--
thanks as always for the help
jer

:

Dear Jer:

As the query I sent had no GROUP BY, I'm thinking you are using
something
else. If so, I don't know what it is. Could you post the query
giving
this
error? Does the query that gives this error work when it is not used
for
a
report?

Tom Ellison


Dear Tom
Yes the query works fine. I have included the datediff function and
that
works as well. Now I am trying to create a report based on the
query
and
I
am getting a dialog box with the following:-
Multi-level GROUP BY clauses is not allowed in a subquery
Any suggestions on how to get around this
Thanks again
--
thanks as always for the help
jer


:

Dear Jer:

On the basis of uniqueness of Name and Date/Time together, I
believe
this
can be solved:

SELECT [Name], [Date/Time], [Old Value], [New Value], No,
(SELECT MAX([Date/Time])
FROM Log L1
WHERE L1.[Name] = L.[Name]
AND L1.[Date/Time] < L.[Date/Time])
AS Previous
FROM Log L
ORDER BY [Name], [Date/Time]

You can readily calculate the elapsed time using this.

Please let me know if this looks good.

Tom Ellison


Dear Tom
Thanks for your quick response. Unfortunately, I am unable to
test
the
query at this time, but thought that I should answer your
questions
none
the
less
1 The old value = new value may repeat for a given name
2 The time/date values actually inclued seconds -3/16/2006
3:47:52
PM
and
this is a unique value for every record in the log
The No does uniquely numer the rows for a given name (for a given
date).
If
a name does not pass all stations today it will continue on to
the
next
station tomorrow. That said though, I am querying the log daily
to
get
the
time differences by day. Also, I am not sure that this will make
a
difference or not, but when I import from excel, the access does
not
keep
the
sort order - name, date time
Will keep you posted though
thanks again
--
thanks as always for the help
jer

:

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you
presume
these
values NEVER REPEAT. Could they? Is this a record of changing
channels
on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences,
perhaps
identified in the Name column? What we would call "Groups" in
database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time
column
is
the only true way to find the "previous" row for this
comparison.
Now,
this
could cause sequencing problems. Is this value recorded only to
the
nearest
minute, or is your example just formatted to show only that much
resolution?

Specifically, it would be most workable to have Name and
Date/Time
together
be unique. As an alternative, we can try to sort this out by
also
filtering
where the New Value of the previous row is equal to the Old
Value
of
the
subsequent row. While this may help, it isn't foolproof.
Consider
this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2
1
W53 3/20/2006 10:45 Station2 Station1
2
W53 3/20/2006 10:45 Station1 Station2
3

Now, in the above, does the No column uniquely number the rows
for
a
given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time,
then
this
may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending
on
the
characteristics of your data. I do not know these
characteristics.
I'll
need your help to understand them.

Tom Ellison


Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE
No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it
takes
to
move
from 1 station to the next (that is from old value to new
value.
From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours
and
24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer
examination
of
the
query results (and reports) some information is distoretd. I
am
not
sure
how
to inclued the last move. The original source of the log is
an
excel
file,
so before importing into access I added a row of data and
changed
the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE
No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily
log
where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Tom Ellison]

Change my first sentence to:

The problem is then not in the query. It is in the facilities added to the
query by the REPORT.

Tom Ellison

Dear Jer:

The problem is then not in the query. It is in the facilities added to
the query by the form.

It is probably a mistake to build up a report without first perfecting the
query on which it is based. I expect that something you have done with
the data in the report is causing the problem. If you were just building
the report, and testing the features as you add them, then you would know
when you had just added a feature that causes a problem.

Finding it after the fact will be more difficult. Perhaps you would be
well off to start making the report again from scratch at least up to the
point where you start having this problem. Copy and paste from the
existing report till one of the properties causes this problem.

It is also possible that the experts in the access.reports newsgroup may
be more familiar with what could cause this.

Tom Ellison

Dear Tom
Yes it does
--
thanks as always for the help
jer

Dear Jer:

Please answer the question:

"Does the query that gives this error work when it is not used for a
report?"

Are you using this query as a saved query for the report, or have you
put
the query code into the report?

Tom Ellison


Dear Tom
As requested,
SELECT Log.Name, Log.[Date/Time], Log.[Old Value], Log.[New Value],
Log.[No], (SELECT MAX([Date/Time]) FROM Log AS L1 WHERE L1.[Name] =
Log.[Name] AND L1.[Date/Time] < Log.[Date/Time]) AS Previous,
DateDiff("s",[Previous],[Date/Time]) AS Seconds
FROM Log
ORDER BY Log.Name, Log.[Date/Time];

I pasted what you sent and when I reviewew the statement the top line
is
prefixed with "log" and also I added in "Log as" L1

--
thanks as always for the help
jer

:

Dear Jer:

As the query I sent had no GROUP BY, I'm thinking you are using
something
else. If so, I don't know what it is. Could you post the query
giving
this
error? Does the query that gives this error work when it is not used
for
a
report?

Tom Ellison


Dear Tom
Yes the query works fine. I have included the datediff function
and
that
works as well. Now I am trying to create a report based on the
query
and
I
am getting a dialog box with the following:-
Multi-level GROUP BY clauses is not allowed in a subquery
Any suggestions on how to get around this
Thanks again
--
thanks as always for the help
jer


:

Dear Jer:

On the basis of uniqueness of Name and Date/Time together, I
believe
this
can be solved:

SELECT [Name], [Date/Time], [Old Value], [New Value], No,
(SELECT MAX([Date/Time])
FROM Log L1
WHERE L1.[Name] = L.[Name]
AND L1.[Date/Time] < L.[Date/Time])
AS Previous
FROM Log L
ORDER BY [Name], [Date/Time]

You can readily calculate the elapsed time using this.

Please let me know if this looks good.

Tom Ellison


Dear Tom
Thanks for your quick response. Unfortunately, I am unable to
test
the
query at this time, but thought that I should answer your
questions
none
the
less
1 The old value = new value may repeat for a given name
2 The time/date values actually inclued seconds -3/16/2006
3:47:52
PM
and
this is a unique value for every record in the log
The No does uniquely numer the rows for a given name (for a
given
date).
If
a name does not pass all stations today it will continue on to
the
next
station tomorrow. That said though, I am querying the log daily
to
get
the
time differences by day. Also, I am not sure that this will
make a
difference or not, but when I import from excel, the access does
not
keep
the
sort order - name, date time
Will keep you posted though
thanks again
--
thanks as always for the help
jer

:

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you
presume
these
values NEVER REPEAT. Could they? Is this a record of changing
channels
on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences,
perhaps
identified in the Name column? What we would call "Groups" in
database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time
column
is
the only true way to find the "previous" row for this
comparison.
Now,
this
could cause sequencing problems. Is this value recorded only
to
the
nearest
minute, or is your example just formatted to show only that
much
resolution?

Specifically, it would be most workable to have Name and
Date/Time
together
be unique. As an alternative, we can try to sort this out by
also
filtering
where the New Value of the previous row is equal to the Old
Value
of
the
subsequent row. While this may help, it isn't foolproof.
Consider
this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2
1
W53 3/20/2006 10:45 Station2 Station1
2
W53 3/20/2006 10:45 Station1 Station2
3

Now, in the above, does the No column uniquely number the rows
for
a
given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time,
then
this
may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending
on
the
characteristics of your data. I do not know these
characteristics.
I'll
need your help to understand them.

Tom Ellison


Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE
No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time
it
takes
to
move
from 1 station to the next (that is from old value to new
value.
From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours
and
24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS
Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer
examination
of
the
query results (and reports) some information is distoretd. I
am
not
sure
how
to inclued the last move. The original source of the log is
an
excel
file,
so before importing into access I added a row of data and
changed
the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE
No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4

*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a
daily
log
where
each name moves approximately through 6 stations

any suggestions or would be appreciated

 

[Guest]

Dear Tom
Thank you exceedingly much, you have been most helpful. I have a start and
I will take your advice
Stay well ...
Be happy
--
thanks as always for the help
jer

Dear Jer:

The problem is then not in the query. It is in the facilities added to the
query by the form.

It is probably a mistake to build up a report without first perfecting the
query on which it is based. I expect that something you have done with the
data in the report is causing the problem. If you were just building the
report, and testing the features as you add them, then you would know when
you had just added a feature that causes a problem.

Finding it after the fact will be more difficult. Perhaps you would be well
off to start making the report again from scratch at least up to the point
where you start having this problem. Copy and paste from the existing
report till one of the properties causes this problem.

It is also possible that the experts in the access.reports newsgroup may be
more familiar with what could cause this.

Tom Ellison

Dear Tom
Yes it does
--
thanks as always for the help
jer

Dear Jer:

Please answer the question:

"Does the query that gives this error work when it is not used for a
report?"

Are you using this query as a saved query for the report, or have you put
the query code into the report?

Tom Ellison


Dear Tom
As requested,
SELECT Log.Name, Log.[Date/Time], Log.[Old Value], Log.[New Value],
Log.[No], (SELECT MAX([Date/Time]) FROM Log AS L1 WHERE L1.[Name] =
Log.[Name] AND L1.[Date/Time] < Log.[Date/Time]) AS Previous,
DateDiff("s",[Previous],[Date/Time]) AS Seconds
FROM Log
ORDER BY Log.Name, Log.[Date/Time];

I pasted what you sent and when I reviewew the statement the top line
is
prefixed with "log" and also I added in "Log as" L1

--
thanks as always for the help
jer

:

Dear Jer:

As the query I sent had no GROUP BY, I'm thinking you are using
something
else. If so, I don't know what it is. Could you post the query
giving
this
error? Does the query that gives this error work when it is not used
for
a
report?

Tom Ellison


Dear Tom
Yes the query works fine. I have included the datediff function and
that
works as well. Now I am trying to create a report based on the
query
and
I
am getting a dialog box with the following:-
Multi-level GROUP BY clauses is not allowed in a subquery
Any suggestions on how to get around this
Thanks again
--
thanks as always for the help
jer


:

Dear Jer:

On the basis of uniqueness of Name and Date/Time together, I
believe
this
can be solved:

SELECT [Name], [Date/Time], [Old Value], [New Value], No,
(SELECT MAX([Date/Time])
FROM Log L1
WHERE L1.[Name] = L.[Name]
AND L1.[Date/Time] < L.[Date/Time])
AS Previous
FROM Log L
ORDER BY [Name], [Date/Time]

You can readily calculate the elapsed time using this.

Please let me know if this looks good.

Tom Ellison


Dear Tom
Thanks for your quick response. Unfortunately, I am unable to
test
the
query at this time, but thought that I should answer your
questions
none
the
less
1 The old value = new value may repeat for a given name
2 The time/date values actually inclued seconds -3/16/2006
3:47:52
PM
and
this is a unique value for every record in the log
The No does uniquely numer the rows for a given name (for a given
date).
If
a name does not pass all stations today it will continue on to
the
next
station tomorrow. That said though, I am querying the log daily
to
get
the
time differences by day. Also, I am not sure that this will make
a
difference or not, but when I import from excel, the access does
not
keep
the
sort order - name, date time
Will keep you posted though
thanks again
--
thanks as always for the help
jer

:

Dear Jer:

Your query has two feature that I question:

1. When you associate two rows on OLD VALUE = NEW VALUE, you
presume
these
values NEVER REPEAT. Could they? Is this a record of changing
channels
on
a TV? I can see where this association may not work.

2. Does the table represent more than one set of sequences,
perhaps
identified in the Name column? What we would call "Groups" in
database
terminology, which are to be treated as independent subsets?

What I'm going to suggest is that the sequence in the Date/Time
column
is
the only true way to find the "previous" row for this
comparison.
Now,
this
could cause sequencing problems. Is this value recorded only to
the
nearest
minute, or is your example just formatted to show only that much
resolution?

Specifically, it would be most workable to have Name and
Date/Time
together
be unique. As an alternative, we can try to sort this out by
also
filtering
where the New Value of the previous row is equal to the Old
Value
of
the
subsequent row. While this may help, it isn't foolproof.
Consider
this:

NAME Time OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 Station1 Station2
1
W53 3/20/2006 10:45 Station2 Station1
2
W53 3/20/2006 10:45 Station1 Station2
3

Now, in the above, does the No column uniquely number the rows
for
a
given
Name?

Assuming this is the case, here's a trial query:

SELECT *
FROM Log L, Log L1
WHERE L1.[Name] = L.[Name]
AND L1.No = L.No - 1

If the No column is per Name and increments by one every time,
then
this
may
do it. This gives you a chance to look at it.

I can code to find the previous row by other methods, depending
on
the
characteristics of your data. I do not know these
characteristics.
I'll
need your help to understand them.

Tom Ellison


Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE
No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it
takes
to
move
from 1 station to the next (that is from old value to new
value.
From
the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours
and
24
minutes.
I have gotten help with the following.

SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value],
Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));

I thought this was working as expected but upon closer
examination
of
the
query results (and reports) some information is distoretd. I
am
not
sure
how
to inclued the last move. The original source of the log is
an
excel
file,
so before importing into access I added a row of data and
changed
the
values
of the time, old and new values as follows

NAME TIME OLD VALUE NEW VALUE
No