Question for Duane Hookom Re: Concatenate

[Gina Whipp]

Duane,

First of all, thanks for directing me to your module, works like a charm...

Here's my problem..

I have to run a query (qryItemNumbers) to sort out what I want from the
table first:

SELECT tblFFESpecificationStyle.ffessFFESpecificationID,
tblStyleCodes.scName, tblFFESpecificationStyle.ffessDescription
FROM tblStyleCodes INNER JOIN tblFFESpecificationStyle ON
tblStyleCodes.scStyleCodeID = tblFFESpecificationStyle.ffessStyleCodeID
WHERE (((tblStyleCodes.scItemID)=-1))
ORDER BY tblFFESpecificationStyle.ffessFFESpecificationID;

Then I run a query to get that data into a report, using the line below as
one of the fields in the query:

Description: Concatenate("SELECT ffessDescription FROM qryItemNumbers WHERE
ffessFFESpecificationID =" & [ffesFFESpecificationID])

However, the second query runs slow (obviously), any ideas how I can speed
it up?

Thanks for any insight you can give me
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

 

[Duane Hookom]

You need to make sure that ffessFFESpecificationID is indexed. I suppose you
could create and use a global database and recordset object rather than
creating and destroying with each call to the function.

 

[Gina Whipp]

Yes, ffessFFESpecificationID is indexed: PK-tblFFESpecification and
FK-tblFFESpecificationStyle. Perhaps, I should try your second suggestion.

Thanks,
Gina Whipp

You need to make sure that ffessFFESpecificationID is indexed. I suppose
you could create and use a global database and recordset object rather
than creating and destroying with each call to the function.


--
Duane Hookom
MS Access MVP

Duane,

First of all, thanks for directing me to your module, works like a
charm...

Here's my problem..

I have to run a query (qryItemNumbers) to sort out what I want from the
table first:

SELECT tblFFESpecificationStyle.ffessFFESpecificationID,
tblStyleCodes.scName, tblFFESpecificationStyle.ffessDescription
FROM tblStyleCodes INNER JOIN tblFFESpecificationStyle ON
tblStyleCodes.scStyleCodeID = tblFFESpecificationStyle.ffessStyleCodeID
WHERE (((tblStyleCodes.scItemID)=-1))
ORDER BY tblFFESpecificationStyle.ffessFFESpecificationID;

Then I run a query to get that data into a report, using the line below
as one of the fields in the query:

Description: Concatenate("SELECT ffessDescription FROM qryItemNumbers
WHERE ffessFFESpecificationID =" & [ffesFFESpecificationID])

However, the second query runs slow (obviously), any ideas how I can
speed it up?

Thanks for any insight you can give me
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II