How do I number records in an Access Query?

[Guest]

I have a large database each record includes fields for UserID and TransDate.
I would like to select the last 10 records (by TransDate) for each UserID.
If I could just Number or Count the records for each USerID then I can select
by <=10. I can use running sum in report but would like to do this via query.

Thanks - Shawn

 

[Bill]

Shawn,

Maybe this will work for you.

If you want the last 10 records by date order you could use this sql query.

Select TOP 10 * from [table name] order by transdate desc

The query will select the first 10 records with the "oldest" dated because
we specified descending order.

Good luck.

Bill

 

[John Spencer (MVP)]

Coordinated subquery is the answer. This should work for you, BUT it could be slow.

SELECT T.*
FROM YourTable as T
WHERE T.TransDate IN
(SELECT TOP 10 X.TransDate
FROM YourTable as X
WHERE X.UserID = T.UserID
ORDER BY X.TransDate DESC)

 

[Guest]

Perfect!
--
Thanks - Shawn

Coordinated subquery is the answer. This should work for you, BUT it could be slow.

SELECT T.*
FROM YourTable as T
WHERE T.TransDate IN
(SELECT TOP 10 X.TransDate
FROM YourTable as X
WHERE X.UserID = T.UserID
ORDER BY X.TransDate DESC)

 

[Guest]

The query below worked perfect on a test table of 4,000 records but when I
ran it on actual table of 6,200,000 records it was still grinding away 15
hours later :-(

SELECT T.*
FROM StatTrip1 AS T
WHERE (((T.StartGaming) In (SELECT TOP 10 X.StartGaming
FROM StatTrip1 as X
WHERE X.PlyrID = T.PlyrID
ORDER BY X.StartGaming DESC)));


This will not be run frequently but I need to determine how many player
accounts meet certain criteria over there last 10 visits before we implement
on floor. So, I tried brute force and created table sorted by combined field
PlyrID-Date. I then add autonumber and call it TripID and calcualate a
TripCount by PlyrID where [TripCnt] = [TripIDmax]-[TripID]+1. Problem is
that Access won't let me add an autonumber to a table this large giving error
message "File sharing lock exceeded. Increase MaxLocksPerFile registry
entry." How do I do this?

 

[John Spencer (MVP)]

Are the StartGaming and PlyrID fields indexed? That would help performance
significantly. With 6.2 million records, this may not run in Access.

As far as increasing the MaxLocksPerFile Registry entry, I can't give you any
advice at this point. I am using an Apple Mac right now and ...

The query below worked perfect on a test table of 4,000 records but when I
ran it on actual table of 6,200,000 records it was still grinding away 15
hours later :-(

SELECT T.*
FROM StatTrip1 AS T
WHERE (((T.StartGaming) In (SELECT TOP 10 X.StartGaming
FROM StatTrip1 as X
WHERE X.PlyrID = T.PlyrID
ORDER BY X.StartGaming DESC)));

This will not be run frequently but I need to determine how many player
accounts meet certain criteria over there last 10 visits before we implement
on floor. So, I tried brute force and created table sorted by combined field
PlyrID-Date. I then add autonumber and call it TripID and calcualate a
TripCount by PlyrID where [TripCnt] = [TripIDmax]-[TripID]+1. Problem is
that Access won't let me add an autonumber to a table this large giving error
message "File sharing lock exceeded. Increase MaxLocksPerFile registry
entry." How do I do this?

--
Thanks - Shawn

Coordinated subquery is the answer. This should work for you, BUT it could be slow.

SELECT T.*
FROM YourTable as T
WHERE T.TransDate IN
(SELECT TOP 10 X.TransDate
FROM YourTable as X
WHERE X.UserID = T.UserID
ORDER BY X.TransDate DESC)

 

[Guest]

They are not indexed. Don't know much about indexes other then when I query
our SMS Host database which is FoxPro it has indexes for all tables. The
table with 6.2M records is pulled from another database. We can't run
queries on live data on this database export required data to Access to run
reports. So, if I index StartGaming and PlyrID maybe it will run more
efficiently? Even if it took 4-6 hours, I need the data once. I'll
probably write macro to select and analyze a sub-set of the records and
append results to new table until all are complete.

Thanks for your help. I am not very experienced with Access, just figure
out how to accomplish what ever needs arise.

--
Thanks - Shawn

Are the StartGaming and PlyrID fields indexed? That would help performance
significantly. With 6.2 million records, this may not run in Access.

As far as increasing the MaxLocksPerFile Registry entry, I can't give you any
advice at this point. I am using an Apple Mac right now and ...

The query below worked perfect on a test table of 4,000 records but when I
ran it on actual table of 6,200,000 records it was still grinding away 15
hours later :-(

SELECT T.*
FROM StatTrip1 AS T
WHERE (((T.StartGaming) In (SELECT TOP 10 X.StartGaming
FROM StatTrip1 as X
WHERE X.PlyrID = T.PlyrID
ORDER BY X.StartGaming DESC)));

This will not be run frequently but I need to determine how many player
accounts meet certain criteria over there last 10 visits before we implement
on floor. So, I tried brute force and created table sorted by combined field
PlyrID-Date. I then add autonumber and call it TripID and calcualate a
TripCount by PlyrID where [TripCnt] = [TripIDmax]-[TripID]+1. Problem is
that Access won't let me add an autonumber to a table this large giving error
message "File sharing lock exceeded. Increase MaxLocksPerFile registry
entry." How do I do this?

--
Thanks - Shawn

Coordinated subquery is the answer. This should work for you, BUT it could be slow.

SELECT T.*
FROM YourTable as T
WHERE T.TransDate IN
(SELECT TOP 10 X.TransDate
FROM YourTable as X
WHERE X.UserID = T.UserID
ORDER BY X.TransDate DESC)


Shawn wrote:

I have a large database each record includes fields for UserID and TransDate.
I would like to select the last 10 records (by TransDate) for each UserID.
If I could just Number or Count the records for each USerID then I can select
by <=10. I can use running sum in report but would like to do this via query.

Thanks - Shawn

 

[Guest]

Adding indexes to PlyrID and StartGaming did the trick. Took about 30 min
but it ran and didn't take 14 hours
--
Thanks - Shawn

They are not indexed. Don't know much about indexes other then when I query
our SMS Host database which is FoxPro it has indexes for all tables. The
table with 6.2M records is pulled from another database. We can't run
queries on live data on this database export required data to Access to run
reports. So, if I index StartGaming and PlyrID maybe it will run more
efficiently? Even if it took 4-6 hours, I need the data once. I'll
probably write macro to select and analyze a sub-set of the records and
append results to new table until all are complete.

Thanks for your help. I am not very experienced with Access, just figure
out how to accomplish what ever needs arise.

--
Thanks - Shawn

Are the StartGaming and PlyrID fields indexed? That would help performance
significantly. With 6.2 million records, this may not run in Access.

As far as increasing the MaxLocksPerFile Registry entry, I can't give you any
advice at this point. I am using an Apple Mac right now and ...

The query below worked perfect on a test table of 4,000 records but when I
ran it on actual table of 6,200,000 records it was still grinding away 15
hours later :-(

SELECT T.*
FROM StatTrip1 AS T
WHERE (((T.StartGaming) In (SELECT TOP 10 X.StartGaming
FROM StatTrip1 as X
WHERE X.PlyrID = T.PlyrID
ORDER BY X.StartGaming DESC)));

This will not be run frequently but I need to determine how many player
accounts meet certain criteria over there last 10 visits before we implement
on floor. So, I tried brute force and created table sorted by combined field
PlyrID-Date. I then add autonumber and call it TripID and calcualate a
TripCount by PlyrID where [TripCnt] = [TripIDmax]-[TripID]+1. Problem is
that Access won't let me add an autonumber to a table this large giving error
message "File sharing lock exceeded. Increase MaxLocksPerFile registry
entry." How do I do this?

--
Thanks - Shawn

:

Coordinated subquery is the answer. This should work for you, BUT it could be slow.

SELECT T.*
FROM YourTable as T
WHERE T.TransDate IN
(SELECT TOP 10 X.TransDate
FROM YourTable as X
WHERE X.UserID = T.UserID
ORDER BY X.TransDate DESC)


Shawn wrote:

I have a large database each record includes fields for UserID and TransDate.
I would like to select the last 10 records (by TransDate) for each UserID.
If I could just Number or Count the records for each USerID then I can select
by <=10. I can use running sum in report but would like to do this via query.

Thanks - Shawn

 

[John Spencer (MVP)]

Great! Glad I asked the question.

Adding indexes to PlyrID and StartGaming did the trick. Took about 30 min
but it ran and didn't take 14 hours
--
Thanks - Shawn

They are not indexed. Don't know much about indexes other then when I query
our SMS Host database which is FoxPro it has indexes for all tables. The
table with 6.2M records is pulled from another database. We can't run
queries on live data on this database export required data to Access to run
reports. So, if I index StartGaming and PlyrID maybe it will run more
efficiently? Even if it took 4-6 hours, I need the data once. I'll
probably write macro to select and analyze a sub-set of the records and
append results to new table until all are complete.

Thanks for your help. I am not very experienced with Access, just figure
out how to accomplish what ever needs arise.

--
Thanks - Shawn

Are the StartGaming and PlyrID fields indexed? That would help performance
significantly. With 6.2 million records, this may not run in Access.

As far as increasing the MaxLocksPerFile Registry entry, I can't give you any
advice at this point. I am using an Apple Mac right now and ...



Shawn Bauer wrote:

The query below worked perfect on a test table of 4,000 records but when I
ran it on actual table of 6,200,000 records it was still grinding away 15
hours later :-(

SELECT T.*
FROM StatTrip1 AS T
WHERE (((T.StartGaming) In (SELECT TOP 10 X.StartGaming
FROM StatTrip1 as X
WHERE X.PlyrID = T.PlyrID
ORDER BY X.StartGaming DESC)));

This will not be run frequently but I need to determine how many player
accounts meet certain criteria over there last 10 visits before we implement
on floor. So, I tried brute force and created table sorted by combined field
PlyrID-Date. I then add autonumber and call it TripID and calcualate a
TripCount by PlyrID where [TripCnt] = [TripIDmax]-[TripID]+1. Problem is
that Access won't let me add an autonumber to a table this large giving error
message "File sharing lock exceeded. Increase MaxLocksPerFile registry
entry." How do I do this?

--
Thanks - Shawn

:

Coordinated subquery is the answer. This should work for you, BUT it could be slow.

SELECT T.*
FROM YourTable as T
WHERE T.TransDate IN
(SELECT TOP 10 X.TransDate
FROM YourTable as X
WHERE X.UserID = T.UserID
ORDER BY X.TransDate DESC)


Shawn wrote:

I have a large database each record includes fields for UserID and TransDate.
I would like to select the last 10 records (by TransDate) for each UserID.
If I could just Number or Count the records for each USerID then I can select
by <=10. I can use running sum in report but would like to do this via query.

Thanks - Shawn

 

[Hile]

John
This post is along the lines of what I need but I still don't know how. I
don't know access very well. We have an Access 2k3 db querying an AS400 file
which has 4-5 indexes. How do I check if the queries are querying the indexes
for optimization. Right now the thing takes hours to return an output and
this db is used daily and needs to return output in short time periods. The
as400 file has 20.2M records and I've been trying to get IT to optimize the
file and purge data but I'm not getting anywhere, yet I still need to be able
to function. The file is for fuel ticket purchases and it contains data from
2004 to present. I'm trying to get them to purge 2004 but there are issues
with the purge program not running.

Can I do a macro or subquery to run prior to the main query to shorten the
dataset? if so how? Right now we programmed a ROBOT job into AS400 to pull a
smaller subset of the data but this can only run the night before (off hours)
and thus our data now runs with a 1day lag. This is billing data so we need
this to be live as of time it is run.

Any suggestions? Let me know if you need any more details. As it is I don't
think I'm giving you much.
--
Hile

Great! Glad I asked the question.

Adding indexes to PlyrID and StartGaming did the trick. Took about 30 min
but it ran and didn't take 14 hours
--
Thanks - Shawn

They are not indexed. Don't know much about indexes other then when I query
our SMS Host database which is FoxPro it has indexes for all tables. The
table with 6.2M records is pulled from another database. We can't run
queries on live data on this database export required data to Access to run
reports. So, if I index StartGaming and PlyrID maybe it will run more
efficiently? Even if it took 4-6 hours, I need the data once. I'll
probably write macro to select and analyze a sub-set of the records and
append results to new table until all are complete.

Thanks for your help. I am not very experienced with Access, just figure
out how to accomplish what ever needs arise.

--
Thanks - Shawn


:

Are the StartGaming and PlyrID fields indexed? That would help performance
significantly. With 6.2 million records, this may not run in Access.

As far as increasing the MaxLocksPerFile Registry entry, I can't give you any
advice at this point. I am using an Apple Mac right now and ...



Shawn Bauer wrote:

The query below worked perfect on a test table of 4,000 records but when I
ran it on actual table of 6,200,000 records it was still grinding away 15
hours later :-(

SELECT T.*
FROM StatTrip1 AS T
WHERE (((T.StartGaming) In (SELECT TOP 10 X.StartGaming
FROM StatTrip1 as X
WHERE X.PlyrID = T.PlyrID
ORDER BY X.StartGaming DESC)));

This will not be run frequently but I need to determine how many player
accounts meet certain criteria over there last 10 visits before we implement
on floor. So, I tried brute force and created table sorted by combined field
PlyrID-Date. I then add autonumber and call it TripID and calcualate a
TripCount by PlyrID where [TripCnt] = [TripIDmax]-[TripID]+1. Problem is
that Access won't let me add an autonumber to a table this large giving error
message "File sharing lock exceeded. Increase MaxLocksPerFile registry
entry." How do I do this?

--
Thanks - Shawn

:

Coordinated subquery is the answer. This should work for you, BUT it could be slow.

SELECT T.*
FROM YourTable as T
WHERE T.TransDate IN
(SELECT TOP 10 X.TransDate
FROM YourTable as X
WHERE X.UserID = T.UserID
ORDER BY X.TransDate DESC)


Shawn wrote:

I have a large database each record includes fields for UserID and TransDate.
I would like to select the last 10 records (by TransDate) for each UserID.
If I could just Number or Count the records for each USerID then I can select
by <=10. I can use running sum in report but would like to do this via query.

Thanks - Shawn

 

[John Spencer]

Sorry. I have no idea on doing anything with AS400 data. I would S P E C U
L A T E that you need to do a pass-through query. Beyond that I have no
idea. Perhaps someone else does.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John
This post is along the lines of what I need but I still don't know how. I
don't know access very well. We have an Access 2k3 db querying an AS400
file
which has 4-5 indexes. How do I check if the queries are querying the
indexes
for optimization. Right now the thing takes hours to return an output and
this db is used daily and needs to return output in short time periods.
The
as400 file has 20.2M records and I've been trying to get IT to optimize
the
file and purge data but I'm not getting anywhere, yet I still need to be
able
to function. The file is for fuel ticket purchases and it contains data
from
2004 to present. I'm trying to get them to purge 2004 but there are issues
with the purge program not running.

Can I do a macro or subquery to run prior to the main query to shorten the
dataset? if so how? Right now we programmed a ROBOT job into AS400 to pull
a
smaller subset of the data but this can only run the night before (off
hours)
and thus our data now runs with a 1day lag. This is billing data so we
need
this to be live as of time it is run.

Any suggestions? Let me know if you need any more details. As it is I
don't
think I'm giving you much.
--
Hile

Great! Glad I asked the question.

Adding indexes to PlyrID and StartGaming did the trick. Took about 30
min
but it ran and didn't take 14 hours
--
Thanks - Shawn

:

They are not indexed. Don't know much about indexes other then when
I query
our SMS Host database which is FoxPro it has indexes for all tables.
The
table with 6.2M records is pulled from another database. We can't
run
queries on live data on this database export required data to Access
to run
reports. So, if I index StartGaming and PlyrID maybe it will run
more
efficiently? Even if it took 4-6 hours, I need the data once. I'll
probably write macro to select and analyze a sub-set of the records
and
append results to new table until all are complete.

Thanks for your help. I am not very experienced with Access, just
figure
out how to accomplish what ever needs arise.

--
Thanks - Shawn


:

Are the StartGaming and PlyrID fields indexed? That would help
performance
significantly. With 6.2 million records, this may not run in
Access.

As far as increasing the MaxLocksPerFile Registry entry, I can't
give you any
advice at this point. I am using an Apple Mac right now and ...



Shawn Bauer wrote:

The query below worked perfect on a test table of 4,000 records
but when I
ran it on actual table of 6,200,000 records it was still grinding
away 15
hours later :-(

SELECT T.*
FROM StatTrip1 AS T
WHERE (((T.StartGaming) In (SELECT TOP 10 X.StartGaming
FROM StatTrip1 as X
WHERE X.PlyrID = T.PlyrID
ORDER BY X.StartGaming DESC)));

This will not be run frequently but I need to determine how many
player
accounts meet certain criteria over there last 10 visits before
we implement
on floor. So, I tried brute force and created table sorted by
combined field
PlyrID-Date. I then add autonumber and call it TripID and
calcualate a
TripCount by PlyrID where [TripCnt] = [TripIDmax]-[TripID]+1.
Problem is
that Access won't let me add an autonumber to a table this large
giving error
message "File sharing lock exceeded. Increase MaxLocksPerFile
registry
entry." How do I do this?

--
Thanks - Shawn

:

Coordinated subquery is the answer. This should work for you,
BUT it could be slow.

SELECT T.*
FROM YourTable as T
WHERE T.TransDate IN
(SELECT TOP 10 X.TransDate
FROM YourTable as X
WHERE X.UserID = T.UserID
ORDER BY X.TransDate DESC)


Shawn wrote:

I have a large database each record includes fields for
UserID and TransDate.
I would like to select the last 10 records (by TransDate)
for each UserID.
If I could just Number or Count the records for each USerID
then I can select
by <=10. I can use running sum in report but would like to
do this via query.

Thanks - Shawn

 

[Hile]

Thanks
Can you send me a resource link where I can readup on this pass through
query option?
--
Hile

Sorry. I have no idea on doing anything with AS400 data. I would S P E C U
L A T E that you need to do a pass-through query. Beyond that I have no
idea. Perhaps someone else does.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John
This post is along the lines of what I need but I still don't know how. I
don't know access very well. We have an Access 2k3 db querying an AS400
file
which has 4-5 indexes. How do I check if the queries are querying the
indexes
for optimization. Right now the thing takes hours to return an output and
this db is used daily and needs to return output in short time periods.
The
as400 file has 20.2M records and I've been trying to get IT to optimize
the
file and purge data but I'm not getting anywhere, yet I still need to be
able
to function. The file is for fuel ticket purchases and it contains data
from
2004 to present. I'm trying to get them to purge 2004 but there are issues
with the purge program not running.

Can I do a macro or subquery to run prior to the main query to shorten the
dataset? if so how? Right now we programmed a ROBOT job into AS400 to pull
a
smaller subset of the data but this can only run the night before (off
hours)
and thus our data now runs with a 1day lag. This is billing data so we
need
this to be live as of time it is run.

Any suggestions? Let me know if you need any more details. As it is I
don't
think I'm giving you much.
--
Hile

Great! Glad I asked the question.

Shawn Bauer wrote:

Adding indexes to PlyrID and StartGaming did the trick. Took about 30
min
but it ran and didn't take 14 hours
--
Thanks - Shawn

:

They are not indexed. Don't know much about indexes other then when
I query
our SMS Host database which is FoxPro it has indexes for all tables.
The
table with 6.2M records is pulled from another database. We can't
run
queries on live data on this database export required data to Access
to run
reports. So, if I index StartGaming and PlyrID maybe it will run
more
efficiently? Even if it took 4-6 hours, I need the data once. I'll
probably write macro to select and analyze a sub-set of the records
and
append results to new table until all are complete.

Thanks for your help. I am not very experienced with Access, just
figure
out how to accomplish what ever needs arise.

--
Thanks - Shawn


:

Are the StartGaming and PlyrID fields indexed? That would help
performance
significantly. With 6.2 million records, this may not run in
Access.

As far as increasing the MaxLocksPerFile Registry entry, I can't
give you any
advice at this point. I am using an Apple Mac right now and ...



Shawn Bauer wrote:

The query below worked perfect on a test table of 4,000 records
but when I
ran it on actual table of 6,200,000 records it was still grinding
away 15
hours later :-(

SELECT T.*
FROM StatTrip1 AS T
WHERE (((T.StartGaming) In (SELECT TOP 10 X.StartGaming
FROM StatTrip1 as X
WHERE X.PlyrID = T.PlyrID
ORDER BY X.StartGaming DESC)));

This will not be run frequently but I need to determine how many
player
accounts meet certain criteria over there last 10 visits before
we implement
on floor. So, I tried brute force and created table sorted by
combined field
PlyrID-Date. I then add autonumber and call it TripID and
calcualate a
TripCount by PlyrID where [TripCnt] = [TripIDmax]-[TripID]+1.
Problem is
that Access won't let me add an autonumber to a table this large
giving error
message "File sharing lock exceeded. Increase MaxLocksPerFile
registry
entry." How do I do this?

--
Thanks - Shawn

:

Coordinated subquery is the answer. This should work for you,
BUT it could be slow.

SELECT T.*
FROM YourTable as T
WHERE T.TransDate IN
(SELECT TOP 10 X.TransDate
FROM YourTable as X
WHERE X.UserID = T.UserID
ORDER BY X.TransDate DESC)


Shawn wrote:

I have a large database each record includes fields for
UserID and TransDate.
I would like to select the last 10 records (by TransDate)
for each UserID.
If I could just Number or Count the records for each USerID
then I can select
by <=10. I can use running sum in report but would like to
do this via query.

Thanks - Shawn