Select Min Query

[Joel_123]

Hello,

I first off want to say thank you to all those who post on this site. I am
not very experienced in Access and the advice and direction offered here is
top notch!

I have been handed a database that has well data in it. There is a table
called Wells which has the following fields:

Well_ID
Well_Name
Well_Location

I also have another table called Well_Data. This table contains the well
data (specific descriptions of items/materials encountered down the well) for
each well. Each well can have numerous well data entries. The two tables
are joined on Well_ID. Here are the fields for the Well_Data Table:

Well_Data_ID
Well_ID
Item_Depth
Item_Description

I am writing a query to search on a specific Item_Description (let's say gold
). I need to be able to return the well name, well location, item depth,
item description, as well as the next item depth below the current item depth.
So for example, I would get a return of:

Well_Name: 40000
Well_Location: Some Place
Item Depth: 150
Item Description: Gold
Next Item Depth 250

I tried to build this by creating one query. I have no problems using ...
Like"*Gold*"... to get me the wells that have gold in them and the query is
relatively quick (being that there are over 2 million Well_Data_ID records).
The problem arises when I try to get the "Next Item Depth" value. The whole
query hangs and Access shuts down. I don't know if I just haven't let it run
long enough but I let it go for an hour and it still hung up. Here's how I
tried to do that:

NextItemDepthSelect Min(Item_Depth) from Well_Data Where Depth >
Well_Data_Alias.[Depth] and Well_ID = Well_Data_Alias.[Well_ID])

I know this works. I created a copy of the database and deleted over 2/3 of
the records and this query completed in about a minute. I was just wondering
if I can somehow re-structure this query to be more efficient. As for
indexes, I ran the analyzer and indexed the fields it told me to. It didn't
help at all.

Any Help would be greatly appreciated.

Thanks.

Joel

 

[John W. Vinson]

Hello,

I first off want to say thank you to all those who post on this site. I am
not very experienced in Access and the advice and direction offered here is
top notch!

On behalf of the many volunteers who answer questions here... thank you.

I have been handed a database that has well data in it. There is a table
called Wells which has the following fields:

Well_ID
Well_Name
Well_Location

I also have another table called Well_Data. This table contains the well
data (specific descriptions of items/materials encountered down the well) for
each well. Each well can have numerous well data entries. The two tables
are joined on Well_ID. Here are the fields for the Well_Data Table:

Well_Data_ID
Well_ID
Item_Depth
Item_Description

Good so far...

I am writing a query to search on a specific Item_Description (let's say gold
). I need to be able to return the well name, well location, item depth,
item description, as well as the next item depth below the current item depth.

gnnn... that's the gotcha of course.

So for example, I would get a return of:

Well_Name: 40000
Well_Location: Some Place
Item Depth: 150
Item Description: Gold
Next Item Depth 250

I tried to build this by creating one query. I have no problems using ...
Like"*Gold*"... to get me the wells that have gold in them and the query is
relatively quick (being that there are over 2 million Well_Data_ID records).

eeep....!!! And of course an index won't help on the wildcard. Good that
*that* part of it is working anyway...

The problem arises when I try to get the "Next Item Depth" value. The whole
query hangs and Access shuts down. I don't know if I just haven't let it run
long enough but I let it go for an hour and it still hung up. Here's how I
tried to do that:

NextItemDepthSelect Min(Item_Depth) from Well_Data Where Depth >
Well_Data_Alias.[Depth] and Well_ID = Well_Data_Alias.[Well_ID])

I know this works. I created a copy of the database and deleted over 2/3 of
the records and this query completed in about a minute. I was just wondering
if I can somehow re-structure this query to be more efficient. As for
indexes, I ran the analyzer and indexed the fields it told me to. It didn't
help at all.

Yep... subqueries will be really really slow because it must run the query for
every record.

Try a JOIN instead:

SELECT First(W.Well_Name) AS TheWellName, First(W.Well_Location) AS Location,
A.[Item Depth], First(A.[Item Description]) AS Description, Max(B.[Item
Depth]) AS Next_Item_Depth
FROM (Wells AS W INNER JOIN Well_Data AS A ON W.Well_Name = A.Well_Name)
INNER JOIN Well_Data AS B ON B.Well_ID = A.Well_ID
AND B.Item_Depth > A.Item_Depth
WHERE A.[Item Description] LIKE "*" & [Enter target substance:] & "*"
GROUP BY W.Well_ID, A.[Item Depth];

Well_ID should already be indexed, be sure that Item_Depth is indexed too
(nonuniquely).

Don't know if this will solve the problem (or even improve it) but it's worth
a try.

I'd suggest avoiding blanks in fieldnames - won't affect the speed but may
make queries easier to edit.

 

[Tom van Stiphout]

On Sat, 01 May 2010 18:13:08 -0600, John W. Vinson

John's advice is good. If this does not help enough, the *Gold*
wildcard is probably the culprit. For a performance test try Gold*
which can use the index. My guess is it will run much faster. If true
but the restriction is unacceptable, you could change the design and
rather than free text in Item_Description you would ask the user to
select any number of keywords from a list. They would be stored in a
new table 1:M related to the current one. That lookup should be very
fast.

-Tom.
Microsoft Access MVP

Hello,

I first off want to say thank you to all those who post on this site. I am
not very experienced in Access and the advice and direction offered here is
top notch!

On behalf of the many volunteers who answer questions here... thank you.

I have been handed a database that has well data in it. There is a table
called Wells which has the following fields:

Well_ID
Well_Name
Well_Location

I also have another table called Well_Data. This table contains the well
data (specific descriptions of items/materials encountered down the well) for
each well. Each well can have numerous well data entries. The two tables
are joined on Well_ID. Here are the fields for the Well_Data Table:

Well_Data_ID
Well_ID
Item_Depth
Item_Description

Good so far...

I am writing a query to search on a specific Item_Description (let's say gold
). I need to be able to return the well name, well location, item depth,
item description, as well as the next item depth below the current item depth.

gnnn... that's the gotcha of course.

So for example, I would get a return of:

Well_Name: 40000
Well_Location: Some Place
Item Depth: 150
Item Description: Gold
Next Item Depth 250

I tried to build this by creating one query. I have no problems using ...
Like"*Gold*"... to get me the wells that have gold in them and the query is
relatively quick (being that there are over 2 million Well_Data_ID records).

eeep....!!! And of course an index won't help on the wildcard. Good that
*that* part of it is working anyway...

The problem arises when I try to get the "Next Item Depth" value. The whole
query hangs and Access shuts down. I don't know if I just haven't let it run
long enough but I let it go for an hour and it still hung up. Here's how I
tried to do that:

NextItemDepthSelect Min(Item_Depth) from Well_Data Where Depth >
Well_Data_Alias.[Depth] and Well_ID = Well_Data_Alias.[Well_ID])

I know this works. I created a copy of the database and deleted over 2/3 of
the records and this query completed in about a minute. I was just wondering
if I can somehow re-structure this query to be more efficient. As for
indexes, I ran the analyzer and indexed the fields it told me to. It didn't
help at all.

Yep... subqueries will be really really slow because it must run the query for
every record.

Try a JOIN instead:

SELECT First(W.Well_Name) AS TheWellName, First(W.Well_Location) AS Location,
A.[Item Depth], First(A.[Item Description]) AS Description, Max(B.[Item
Depth]) AS Next_Item_Depth
FROM (Wells AS W INNER JOIN Well_Data AS A ON W.Well_Name = A.Well_Name)
INNER JOIN Well_Data AS B ON B.Well_ID = A.Well_ID
AND B.Item_Depth > A.Item_Depth
WHERE A.[Item Description] LIKE "*" & [Enter target substance:] & "*"
GROUP BY W.Well_ID, A.[Item Depth];

Well_ID should already be indexed, be sure that Item_Depth is indexed too
(nonuniquely).

Don't know if this will solve the problem (or even improve it) but it's worth
a try.

I'd suggest avoiding blanks in fieldnames - won't affect the speed but may
make queries easier to edit.

 

[Joel_123 via AccessMonster.com]

Thank you both for the help. I made one change - I changed "max" to "min" to
search for the next highest value and it worked awesome. The whole query
takes about 20 seconds. I do have one other questions though. When I ran my
previous query searching simply for ... Like "*Gold*", I get a return of
about 95,000 records. When I use this query, i get a return of about 85,000
records. I have figured out that this is a result of some wells having a
"gold" entry as the last Well_Data entry and there are no further values to
enter as the "next depth". How would I account for this. I imagine that I
would need something to account for NULL but I don't think Nz() would work in
SQL. Any ideas?

Thanks Again.

 

[John W. Vinson]

Thank you both for the help. I made one change - I changed "max" to "min" to
search for the next highest value and it worked awesome. The whole query
takes about 20 seconds. I do have one other questions though. When I ran my
previous query searching simply for ... Like "*Gold*", I get a return of
about 95,000 records. When I use this query, i get a return of about 85,000
records. I have figured out that this is a result of some wells having a
"gold" entry as the last Well_Data entry and there are no further values to
enter as the "next depth". How would I account for this. I imagine that I
would need something to account for NULL but I don't think Nz() would work in
SQL. Any ideas?

Thanks Again.

It will if you change the INNER JOIN to B to LEFT JOIN... I hope.

I'm delighted that it improved the performance so much!

 

[KARL DEWEY]

Try this --
SELECT Well_Name, Well_Location, Item_Depth, Item_Description, (SELECT TOP 1
[XX].Item_Depth FROM Well_Data AS [XX] WHERE [XX].Well_ID = Wells.Well_ID AND
[XX].Item_Depth > Well_Data.Item_Depth ORDER BY [XX].Item_Depth) AS [Next
Item Depth]
FROM Wells LEFT JOIN Well_Data ON Wells.Well_ID = Well_Data.Well_ID
WHERE Well_Data.Item_Description Like "*GOLD*";