Re: How do I number records in an Access Query?

Tech-Archive recommends: Fix windows errors by optimizing your registry

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


"John Spencer (MVP)" wrote:

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

"Shawn Bauer" wrote:

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


"John Spencer (MVP)" wrote:

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

"John Spencer (MVP)" wrote:

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



.

Relevant Pages

  • Re: How do I number records in an Access Query?
    ... Adding indexes to PlyrID and StartGaming did the trick. ... "Shawn Bauer" wrote: ... > our SMS Host database which is FoxPro it has indexes for all tables. ...
    (microsoft.public.access.queries)
  • Re: WSE 3.0 Running under default ASPNet user
    ... UsernameTokenManager which needs to access a remote database. ... The web service needs to access a database on a remote server, ... use the UserID and Password held in the IIS config; ... this would be using the NetworkService account on Server 2003. ...
    (microsoft.public.dotnet.framework.webservices.enhancements)
  • Re: Populating Default values of Forms using queries to Access
    ... I have an Access database that contains the ... |> results for the Userid that someone enters. ... |> What I want to be able to do is use the informaiton to set default values ... |> have the users send me an email anytime they want a to change their ...
    (microsoft.public.frontpage.client)
  • Re: Database Auditing and connection strings
    ... We have a need to be able to perform auditing on the database side ... As part of the requirement we must know the userid ... 1) Using a standard ID and password in SQL Server but providing ... This might at least give us an idea as to why connection pooling ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Dynamic "IN" criteria in query
    ... A method that might work is to create a table that contains the userID and ... or notthan insert a clause in the query "AND UserOfficeIDs=TRUE" ... Data for all offices is stored in the same database. ... "UserOfficeIDs" which returns a string of the comma-delimited ...
    (microsoft.public.access.queries)