An inefficient query

[Andy_Khosravi]

Background: Access 2003 (converted from A97 DB recently), database is
split into FE/BE with the FE residing on client machine.

I've got a question about how I can possibly optimize a query I've
built. This may seem relatively straight forward to some of you more
experienced programmers, but I'm almost entirely self-taught. This
means that I have some gaps in my knowledge and sometimes go about
doing things the hard way never knowing there was a quicker and easier
way to do it.

I have two tables involved in the query: tblIssue (12,000 records) and
tblIssueHistory (85,000 records). The tblIssue table holds issues and
tblIssueHistory holds a history of (locations/specific owners/time in
and time out) where these issues have been in. One issue may have many
historical locations in tblIssueHistory .

Very frequently I need to display some key information whenever an
issue is displayed. Most notably I need to display the very first and
very last record (origination and current) location associated with an
issue from tblIssueHistory .

The current process that I use now first queries (lets call this
qryOriginator) tblIssueHistory using the sum function to group on
IssueID (foreign key to tblIssue) and show the Min of HistID (primary
key of tblIssueHistory ). Another nearly identical query is run,
except it pulls the max of HistID (call this qryCurrent). At this
point, I create another query to pull in tblIssue, add qryOriginator
and qryCurrent, then link qryOriginator and qryCurrent to
tblIssueHistory to allow access to the rest of the fields (like
department, name of owner, time in and time out).

It pulls up the information I want, but it takes much too long to do
so (between 6 and 30 seconds). When added to other criteria I need to
run to display the info needed, it makes a bad situation even worse.
Compounding this is the fact that I expect the number of records I'll
need to house to triple or quadruple in the next year, so I'm getting
desperate to make this database run more efficiently.

I'm wondering if there is a more efficient approach I could take to
get at the first and last record for each issueID. The only thing I
can think of is to add fields to tblIssue to hold originator and
current owner names, then modify them whenever an issue's location
moves. I had done it this way originally, but had trouble keeping the
data consistent with info in tblIssueHistory and switched it up.

Any advice any of you might have will be greatly appreciated!

[Salad]

An***********@b cbsmn.com wrote:

Background: Access 2003 (converted from A97 DB recently), database is
split into FE/BE with the FE residing on client machine.

I've got a question about how I can possibly optimize a query I've
built. This may seem relatively straight forward to some of you more
experienced programmers, but I'm almost entirely self-taught. This
means that I have some gaps in my knowledge and sometimes go about
doing things the hard way never knowing there was a quicker and easier
way to do it.

I have two tables involved in the query: tblIssue (12,000 records) and
tblIssueHistory (85,000 records). The tblIssue table holds issues and
tblIssueHistory holds a history of (locations/specific owners/time in
and time out) where these issues have been in. One issue may have many
historical locations in tblIssueHistory .

Very frequently I need to display some key information whenever an
issue is displayed. Most notably I need to display the very first and
very last record (origination and current) location associated with an
issue from tblIssueHistory .

The current process that I use now first queries (lets call this
qryOriginator) tblIssueHistory using the sum function to group on
IssueID (foreign key to tblIssue) and show the Min of HistID (primary
key of tblIssueHistory ). Another nearly identical query is run,
except it pulls the max of HistID (call this qryCurrent). At this
point, I create another query to pull in tblIssue, add qryOriginator
and qryCurrent, then link qryOriginator and qryCurrent to
tblIssueHistory to allow access to the rest of the fields (like
department, name of owner, time in and time out).

It pulls up the information I want, but it takes much too long to do
so (between 6 and 30 seconds). When added to other criteria I need to
run to display the info needed, it makes a bad situation even worse.
Compounding this is the fact that I expect the number of records I'll
need to house to triple or quadruple in the next year, so I'm getting
desperate to make this database run more efficiently.

I'm wondering if there is a more efficient approach I could take to
get at the first and last record for each issueID. The only thing I
can think of is to add fields to tblIssue to hold originator and
current owner names, then modify them whenever an issue's location
moves. I had done it this way originally, but had trouble keeping the
data consistent with info in tblIssueHistory and switched it up.

Any advice any of you might have will be greatly appreciated!

Sure. Create a query. Open up a new query and drag the history table
into it. Drag the IssueID to a column and drag the HistoryID to two
columns. Make the names MinID, MaxID. Ex:
MinID : HistoryID
MaxID : HistoryID.

From the menu, select View/Totals. In the first column of HistoryID,
in the Totals row, select Min from the dropdown. Select Max to the next.

Save the query. You can now link this to the recordsource of the form
to have access to MinID/MaxID.

Of course, another method would be to use a subform...if you have space.
When you go to an issue record all records from history are displayed.
If you have record selectors on the subform the folks can hit the
first/last record navigation buttons...and they can see anything inbetween.

If you don't have space, you can always create a form to diplay the
history table as a datasheet or continuous form and when you need to see
that info, have a button to display the records in history from that issue.
Docmd.Openform "History",,,"Is sueID = " & Me.IssueID

You should be able to get this to display near instantaneously . If not,
you probably have not indexed fields that need to be indexed.