Do multiple queries using ADO recordsets 1

[mdav2]

I have a simple employee training database. The two tables are linked using NI number. The LU_Course is just a lookup for the ethnicity code stored in the employee table.

The query I am running below works but takes 50 seconds to complete. If I run it in two stages the results appear instantaneously.

I would like to know if it is possible to run a query using an ADO recordset then use that to run a second query.

Thanks,

MDAV

IS

SELECT IIf(IsNull([EthnicityDescription]),'Not Recorded',[EthnicityDescription]) AS Ethnic_Description, LU_Ethnicity.EthnicityCode, Count(IIf(IsNull([EthnicityDescription]),'Not Recorded',[EthnicityDescription])) AS Ethnic_Count
FROM LU_Ethnicity RIGHT JOIN T_Employee ON LU_Ethnicity.EthnicityCode = T_Employee.Ethnicity
WHERE (((T_Employee.NINumber) In (SELECT DISTINCT T_Employee.NINumber FROM T_Training INNER JOIN T_Employee ON T_Training.NINumber = T_Employee.NINumber WHERE (((T_Training.DateTaken) Is Not Null Or (T_Training.DateTaken) Between #1/1/1980# And #1/1/2999#)))))
GROUP BY IIf(IsNull([EthnicityDescription]),'Not Recorded',[EthnicityDescription]), LU_Ethnicity.EthnicityCode
ORDER BY LU_Ethnicity.EthnicityCode;

 

[PHV]

You may try something like this:
SELECT Nz([EthnicityDescription],'Not Recorded') AS Ethnic_Description
, D.EthnicityCode, Count(*) AS Ethnic_Count
FROM (SELECT DISTINCT A.NINumber, A.Ethnicity FROM T_Employee AS A
INNER JOIN T_Training AS B ON A.NINumber = B.NINumber
WHERE B.DateTaken Between #1/1/1980# And #1/1/2999#
) AS C LEFT JOIN LU_Ethnicity AS D ON C.Ethnicity = D.EthnicityCode
GROUP BY Nz([EthnicityDescription],'Not Recorded'), D.EthnicityCode
ORDER BY D.EthnicityCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mdav2]

Thanks for that PHV, my SQL has never been all that good because I don't tend to do a lot and the stuff I do end up doing is rather simple. Back to basics for me!