Report based on query w/subquery won't give a Report Footer total

[Nicholas Scarpinato]

I have a report that runs off a query which pulls item entry times per user.
Each record in the table has an EntryStartTime field which is updated as soon
as a user updates the first field in the entry form. (Technically the current
time is stored in a global variable which is updated once the record is
committed.)

What I want to do is run a report showing how long it takes for a user to
begin a new entry (i.e. the time between the start of record 1 and the start
of record 2), and the average of those times. However, in my report, whenever
I try to add a field in the Report Footer that gets the average of the time
between entries, I get an error saying something about being unable to do
multiple Group Bys in a subquery. (I need the subquery to get the previous
entry times.) How do I make this report work the way I need it to?

 

[Marshall Barton]

I have a report that runs off a query which pulls item entry times per user.
Each record in the table has an EntryStartTime field which is updated as soon
as a user updates the first field in the entry form. (Technically the current
time is stored in a global variable which is updated once the record is
committed.)

What I want to do is run a report showing how long it takes for a user to
begin a new entry (i.e. the time between the start of record 1 and the start
of record 2), and the average of those times. However, in my report, whenever
I try to add a field in the Report Footer that gets the average of the time
between entries, I get an error saying something about being unable to do
multiple Group Bys in a subquery. (I need the subquery to get the previous
entry times.) How do I make this report work the way I need it to?

You might be able to get around that problem by using a
DLookup instead of a subquery.

If that's not reasonable or too slow, sometimes you can use
a separate query to get the previous time and Join that to
your table to put the previous time and current time in the
same record.

If noe of that make sense to you, then post a Copy/Paste of
your query's SQL view and we'll see what we can come up
with.

 

[Nicholas Scarpinato]

Unfortunately, I don't have the underlying query updating the tables. The
information is already in the tables and I'm using the subquery to get the
next previous entry time.

Here's the query (sorry if it's ridiculously long):

SELECT [Main Returns Table].CheckinUser, [Main Returns Table].[Vendor Name], _
[Main Returns Table].EntryStartTime, (SELECT TOP 1 Dupe.[EntryStartTime] _
FROM [Main Returns Table] AS Dupe WHERE Dupe.[CheckinUser] = _
Forms![Reports Admin].[User Select] AND Dupe.[EntryStartTime] < _
[Main Returns Table].[EntryStartTime] ORDER BY Dupe.[EntryStartTime] DESC) _
AS PriorValue, DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference] _
FROM [Main Returns Table]
WHERE ((([Main Returns Table].CheckinUser)=[Forms]![Reports Admin].[User
Select]));

CheckinUser is the username being reported on. Vendor Name is the product
vendor. EntryStartTime is the Now() time that is captured at the beginning of
the user's entry into the entry form.

What I'm attempting to do here is capture all the start times, the previous
start time, and report on the difference between them. The query and report
work perfectly fine until I try to add in an overall average on the time
difference. Then I get the error I mentioned before. I don't think a DLookup
will work for this, but if there's a way to do so, maybe that will solve my
issue.

 

[Marshall Barton]

When you post code or a query, use Copy/Paste without and
additional editing. Queries do not use _ to extend to
another line.

As I said, replace the subquery with a DMax functioncall:

SELECT CheckinUser, [Vendor Name], EntryStartTime,
DMax("EntryStartTime" , "Main Returns Table",
"CheckinUser = Forms![Reports Admin].[User Select]
AND EntryStartTime < #" & EntryStartTime & "#")
AS PriorValue,
DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference]
FROM [Main Returns Table]
WHERE CheckinUser=[Forms]![Reports Admin].[User Select]
--
Marsh
MVP [MS Access]

Unfortunately, I don't have the underlying query updating the tables. The
information is already in the tables and I'm using the subquery to get the
next previous entry time.

Here's the query (sorry if it's ridiculously long):

SELECT [Main Returns Table].CheckinUser, [Main Returns Table].[Vendor Name], _
[Main Returns Table].EntryStartTime, (SELECT TOP 1 Dupe.[EntryStartTime] _
FROM [Main Returns Table] AS Dupe WHERE Dupe.[CheckinUser] = _
Forms![Reports Admin].[User Select] AND Dupe.[EntryStartTime] < _
[Main Returns Table].[EntryStartTime] ORDER BY Dupe.[EntryStartTime] DESC) _
AS PriorValue, DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference] _
FROM [Main Returns Table]
WHERE ((([Main Returns Table].CheckinUser)=[Forms]![Reports Admin].[User
Select]));

CheckinUser is the username being reported on. Vendor Name is the product
vendor. EntryStartTime is the Now() time that is captured at the beginning of
the user's entry into the entry form.

What I'm attempting to do here is capture all the start times, the previous
start time, and report on the difference between them. The query and report
work perfectly fine until I try to add in an overall average on the time
difference. Then I get the error I mentioned before. I don't think a DLookup
will work for this, but if there's a way to do so, maybe that will solve my
issue.

You might be able to get around that problem by using a
DLookup instead of a subquery.

If that's not reasonable or too slow, sometimes you can use
a separate query to get the previous time and Join that to
your table to put the previous time and current time in the
same record.

If noe of that make sense to you, then post a Copy/Paste of
your query's SQL view and we'll see what we can come up
with.

 

[Nicholas Scarpinato]

Ah.... I was confused, in your previous post you mentioned DLookup and for
the life of me I couldn't figure out how that would help me, but DMax makes
more sense... I'll try that and see how it works.

When you post code or a query, use Copy/Paste without and
additional editing. Queries do not use _ to extend to
another line.

As I said, replace the subquery with a DMax functioncall:

SELECT CheckinUser, [Vendor Name], EntryStartTime,
DMax("EntryStartTime" , "Main Returns Table",
"CheckinUser = Forms![Reports Admin].[User Select]
AND EntryStartTime < #" & EntryStartTime & "#")
AS PriorValue,
DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference]
FROM [Main Returns Table]
WHERE CheckinUser=[Forms]![Reports Admin].[User Select]
--
Marsh
MVP [MS Access]

Unfortunately, I don't have the underlying query updating the tables. The
information is already in the tables and I'm using the subquery to get the
next previous entry time.

Here's the query (sorry if it's ridiculously long):

SELECT [Main Returns Table].CheckinUser, [Main Returns Table].[Vendor Name], _
[Main Returns Table].EntryStartTime, (SELECT TOP 1 Dupe.[EntryStartTime] _
FROM [Main Returns Table] AS Dupe WHERE Dupe.[CheckinUser] = _
Forms![Reports Admin].[User Select] AND Dupe.[EntryStartTime] < _
[Main Returns Table].[EntryStartTime] ORDER BY Dupe.[EntryStartTime] DESC) _
AS PriorValue, DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference] _
FROM [Main Returns Table]
WHERE ((([Main Returns Table].CheckinUser)=[Forms]![Reports Admin].[User
Select]));

CheckinUser is the username being reported on. Vendor Name is the product
vendor. EntryStartTime is the Now() time that is captured at the beginning of
the user's entry into the entry form.

What I'm attempting to do here is capture all the start times, the previous
start time, and report on the difference between them. The query and report
work perfectly fine until I try to add in an overall average on the time
difference. Then I get the error I mentioned before. I don't think a DLookup
will work for this, but if there's a way to do so, maybe that will solve my
issue.

Nicholas Scarpinato wrote:

I have a report that runs off a query which pulls item entry times per user.
Each record in the table has an EntryStartTime field which is updated as soon
as a user updates the first field in the entry form. (Technically the current
time is stored in a global variable which is updated once the record is
committed.)

What I want to do is run a report showing how long it takes for a user to
begin a new entry (i.e. the time between the start of record 1 and the start
of record 2), and the average of those times. However, in my report, whenever
I try to add a field in the Report Footer that gets the average of the time
between entries, I get an error saying something about being unable to do
multiple Group Bys in a subquery. (I need the subquery to get the previous
entry times.) How do I make this report work the way I need it to?


You might be able to get around that problem by using a
DLookup instead of a subquery.

If that's not reasonable or too slow, sometimes you can use
a separate query to get the previous time and Join that to
your table to put the previous time and current time in the
same record.

If noe of that make sense to you, then post a Copy/Paste of
your query's SQL view and we'll see what we can come up
with.

 

[Nicholas Scarpinato]

Well, I think the concept is correct, but I get nothing but errors no matter
what I try. The only thing I can think of is maybe DMax doesn't work properly
with a Long Date formatted date? I need the EntryStartTime it in Long Date
format because I need the entry time and date. I don't really know what else
to try.

Ah.... I was confused, in your previous post you mentioned DLookup and for
the life of me I couldn't figure out how that would help me, but DMax makes
more sense... I'll try that and see how it works.

When you post code or a query, use Copy/Paste without and
additional editing. Queries do not use _ to extend to
another line.

As I said, replace the subquery with a DMax functioncall:

SELECT CheckinUser, [Vendor Name], EntryStartTime,
DMax("EntryStartTime" , "Main Returns Table",
"CheckinUser = Forms![Reports Admin].[User Select]
AND EntryStartTime < #" & EntryStartTime & "#")
AS PriorValue,
DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference]
FROM [Main Returns Table]
WHERE CheckinUser=[Forms]![Reports Admin].[User Select]
--
Marsh
MVP [MS Access]

Unfortunately, I don't have the underlying query updating the tables. The
information is already in the tables and I'm using the subquery to get the
next previous entry time.

Here's the query (sorry if it's ridiculously long):

SELECT [Main Returns Table].CheckinUser, [Main Returns Table].[Vendor Name], _
[Main Returns Table].EntryStartTime, (SELECT TOP 1 Dupe.[EntryStartTime] _
FROM [Main Returns Table] AS Dupe WHERE Dupe.[CheckinUser] = _
Forms![Reports Admin].[User Select] AND Dupe.[EntryStartTime] < _
[Main Returns Table].[EntryStartTime] ORDER BY Dupe.[EntryStartTime] DESC) _
AS PriorValue, DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference] _
FROM [Main Returns Table]
WHERE ((([Main Returns Table].CheckinUser)=[Forms]![Reports Admin].[User
Select]));

CheckinUser is the username being reported on. Vendor Name is the product
vendor. EntryStartTime is the Now() time that is captured at the beginning of
the user's entry into the entry form.

What I'm attempting to do here is capture all the start times, the previous
start time, and report on the difference between them. The query and report
work perfectly fine until I try to add in an overall average on the time
difference. Then I get the error I mentioned before. I don't think a DLookup
will work for this, but if there's a way to do so, maybe that will solve my
issue.


:

Nicholas Scarpinato wrote:

I have a report that runs off a query which pulls item entry times per user.
Each record in the table has an EntryStartTime field which is updated as soon
as a user updates the first field in the entry form. (Technically the current
time is stored in a global variable which is updated once the record is
committed.)

What I want to do is run a report showing how long it takes for a user to
begin a new entry (i.e. the time between the start of record 1 and the start
of record 2), and the average of those times. However, in my report, whenever
I try to add a field in the Report Footer that gets the average of the time
between entries, I get an error saying something about being unable to do
multiple Group Bys in a subquery. (I need the subquery to get the previous
entry times.) How do I make this report work the way I need it to?


You might be able to get around that problem by using a
DLookup instead of a subquery.

If that's not reasonable or too slow, sometimes you can use
a separate query to get the previous time and Join that to
your table to put the previous time and current time in the
same record.

If noe of that make sense to you, then post a Copy/Paste of
your query's SQL view and we'll see what we can come up
with.

 

[Marshall Barton]

How about some clues? "I get nothing but errors" really
doesn't convey much information. A fresh copy of the query
might also help.

The one thing that comes to mind is your system setting for
date format might not be set to US style. If that's the
case, then you need to use:

.... AND EntryStartTime < " & Format(EntryStartTime,
"\#yyyy\/m\/d h\:n\:s\#") ...
--
Marsh
MVP [MS Access]

Well, I think the concept is correct, but I get nothing but errors no matter
what I try. The only thing I can think of is maybe DMax doesn't work properly
with a Long Date formatted date? I need the EntryStartTime it in Long Date
format because I need the entry time and date. I don't really know what else
to try.

When you post code or a query, use Copy/Paste without and
additional editing. Queries do not use _ to extend to
another line.

SELECT CheckinUser, [Vendor Name], EntryStartTime,
DMax("EntryStartTime" , "Main Returns Table",
"CheckinUser = Forms![Reports Admin].[User Select]
AND EntryStartTime < #" & EntryStartTime & "#")
AS PriorValue,
DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference]
FROM [Main Returns Table]
WHERE CheckinUser=[Forms]![Reports Admin].[User Select]


Nicholas Scarpinato wrote:
Unfortunately, I don't have the underlying query updating the tables. The
information is already in the tables and I'm using the subquery to get the
next previous entry time.

Here's the query (sorry if it's ridiculously long):

SELECT [Main Returns Table].CheckinUser, [Main Returns Table].[Vendor Name], _
[Main Returns Table].EntryStartTime, (SELECT TOP 1 Dupe.[EntryStartTime] _
FROM [Main Returns Table] AS Dupe WHERE Dupe.[CheckinUser] = _
Forms![Reports Admin].[User Select] AND Dupe.[EntryStartTime] < _
[Main Returns Table].[EntryStartTime] ORDER BY Dupe.[EntryStartTime] DESC) _
AS PriorValue, DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference] _
FROM [Main Returns Table]
WHERE ((([Main Returns Table].CheckinUser)=[Forms]![Reports Admin].[User
Select]));

CheckinUser is the username being reported on. Vendor Name is the product
vendor. EntryStartTime is the Now() time that is captured at the beginning of
the user's entry into the entry form.

What I'm attempting to do here is capture all the start times, the previous
start time, and report on the difference between them. The query and report
work perfectly fine until I try to add in an overall average on the time
difference. Then I get the error I mentioned before. I don't think a DLookup
will work for this, but if there's a way to do so, maybe that will solve my
issue.

 

[Nicholas Scarpinato]

Sorry for not posting more specific information, but I didn't have time to
write all the errors down, something more important came up with this
particular database and I've been concentrating on that issue up until now.
The errors I was getting were in the PriorValue field, it came up as
"#Error"... but I've fixed that. In your query, I had to change this line:

"CheckinUser = Forms![Reports Admin].[User Select] AND EntryStartTime < #" &
EntryStartTime & "#") AS PriorValue

to this:

"CheckinUser = '" & Forms![Reports Admin].[User Select] & "' AND
EntryStartTime < #" & EntryStartTime & "#") AS PriorValue

and that solved everything. Now to try the report...

How about some clues? "I get nothing but errors" really
doesn't convey much information. A fresh copy of the query
might also help.

The one thing that comes to mind is your system setting for
date format might not be set to US style. If that's the
case, then you need to use:

.... AND EntryStartTime < " & Format(EntryStartTime,
"\#yyyy\/m\/d h\:n\:s\#") ...
--
Marsh
MVP [MS Access]

Well, I think the concept is correct, but I get nothing but errors no matter
what I try. The only thing I can think of is maybe DMax doesn't work properly
with a Long Date formatted date? I need the EntryStartTime it in Long Date
format because I need the entry time and date. I don't really know what else
to try.

:
When you post code or a query, use Copy/Paste without and
additional editing. Queries do not use _ to extend to
another line.

SELECT CheckinUser, [Vendor Name], EntryStartTime,
DMax("EntryStartTime" , "Main Returns Table",
"CheckinUser = Forms![Reports Admin].[User Select]
AND EntryStartTime < #" & EntryStartTime & "#")
AS PriorValue,
DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference]
FROM [Main Returns Table]
WHERE CheckinUser=[Forms]![Reports Admin].[User Select]


Nicholas Scarpinato wrote:
Unfortunately, I don't have the underlying query updating the tables. The
information is already in the tables and I'm using the subquery to get the
next previous entry time.

Here's the query (sorry if it's ridiculously long):

SELECT [Main Returns Table].CheckinUser, [Main Returns Table].[Vendor Name], _
[Main Returns Table].EntryStartTime, (SELECT TOP 1 Dupe.[EntryStartTime] _
FROM [Main Returns Table] AS Dupe WHERE Dupe.[CheckinUser] = _
Forms![Reports Admin].[User Select] AND Dupe.[EntryStartTime] < _
[Main Returns Table].[EntryStartTime] ORDER BY Dupe.[EntryStartTime] DESC) _
AS PriorValue, DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference] _
FROM [Main Returns Table]
WHERE ((([Main Returns Table].CheckinUser)=[Forms]![Reports Admin].[User
Select]));

CheckinUser is the username being reported on. Vendor Name is the product
vendor. EntryStartTime is the Now() time that is captured at the beginning of
the user's entry into the entry form.

What I'm attempting to do here is capture all the start times, the previous
start time, and report on the difference between them. The query and report
work perfectly fine until I try to add in an overall average on the time
difference. Then I get the error I mentioned before. I don't think a DLookup
will work for this, but if there's a way to do so, maybe that will solve my
issue.

 

[Nicholas Scarpinato]

That did the trick! It takes a little while to process, but I'll be adding a
date limit to the report which should cut down on the amount of data being
processed (hopefully). Thank you for your help, and your paitence.

Sorry for not posting more specific information, but I didn't have time to
write all the errors down, something more important came up with this
particular database and I've been concentrating on that issue up until now.
The errors I was getting were in the PriorValue field, it came up as
"#Error"... but I've fixed that. In your query, I had to change this line:

"CheckinUser = Forms![Reports Admin].[User Select] AND EntryStartTime < #" &
EntryStartTime & "#") AS PriorValue

to this:

"CheckinUser = '" & Forms![Reports Admin].[User Select] & "' AND
EntryStartTime < #" & EntryStartTime & "#") AS PriorValue

and that solved everything. Now to try the report...

How about some clues? "I get nothing but errors" really
doesn't convey much information. A fresh copy of the query
might also help.

The one thing that comes to mind is your system setting for
date format might not be set to US style. If that's the
case, then you need to use:

.... AND EntryStartTime < " & Format(EntryStartTime,
"\#yyyy\/m\/d h\:n\:s\#") ...
--
Marsh
MVP [MS Access]

Well, I think the concept is correct, but I get nothing but errors no matter
what I try. The only thing I can think of is maybe DMax doesn't work properly
with a Long Date formatted date? I need the EntryStartTime it in Long Date
format because I need the entry time and date. I don't really know what else
to try.


:
When you post code or a query, use Copy/Paste without and
additional editing. Queries do not use _ to extend to
another line.

SELECT CheckinUser, [Vendor Name], EntryStartTime,
DMax("EntryStartTime" , "Main Returns Table",
"CheckinUser = Forms![Reports Admin].[User Select]
AND EntryStartTime < #" & EntryStartTime & "#")
AS PriorValue,
DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference]
FROM [Main Returns Table]
WHERE CheckinUser=[Forms]![Reports Admin].[User Select]


Nicholas Scarpinato wrote:
Unfortunately, I don't have the underlying query updating the tables. The
information is already in the tables and I'm using the subquery to get the
next previous entry time.

Here's the query (sorry if it's ridiculously long):

SELECT [Main Returns Table].CheckinUser, [Main Returns Table].[Vendor Name], _
[Main Returns Table].EntryStartTime, (SELECT TOP 1 Dupe.[EntryStartTime] _
FROM [Main Returns Table] AS Dupe WHERE Dupe.[CheckinUser] = _
Forms![Reports Admin].[User Select] AND Dupe.[EntryStartTime] < _
[Main Returns Table].[EntryStartTime] ORDER BY Dupe.[EntryStartTime] DESC) _
AS PriorValue, DateDiff("n",[PriorValue],[EntryStartTime]) AS [Time
Difference] _
FROM [Main Returns Table]
WHERE ((([Main Returns Table].CheckinUser)=[Forms]![Reports Admin].[User
Select]));

CheckinUser is the username being reported on. Vendor Name is the product
vendor. EntryStartTime is the Now() time that is captured at the beginning of
the user's entry into the entry form.

What I'm attempting to do here is capture all the start times, the previous
start time, and report on the difference between them. The query and report
work perfectly fine until I try to add in an overall average on the time
difference. Then I get the error I mentioned before. I don't think a DLookup
will work for this, but if there's a way to do so, maybe that will solve my
issue.