Query taking AGES to run 1

[postmanphat]

Hi, I'm writing a small db (attached) that records the number of people contacting an advisory service, how they contacted them (intervention) and various info like gender etc).

I've written a query that churns out various counts about how many people contact the service and how (attached spreadsheet - interventions.xls) at first it worked fine and Access output the results very quickly. The problem is as soon as I put some date criteria in it takes up to 45 minutes to run the query (I've tried it with literally 1 row of data and it still takes 45 mins).

The query that has the problem is 'Interventions', in which you'll see another query called 'InterventionDays' which is the query that gets the date range. If I run 'InterventionDays' on its own its nice and quick, and if I delete 'InterventionDays' from the from 'Interventions' then 'interventions' runs nice and quick. But together it just grinds to a halt.

There are 2 rows of data in there at the moment for 17/02/08(to see in form view - find that date in the combo box on the main menu and click the >> button.

Any advice would be most greatly appreciated.

 

[PHV]

What are your actual SQL codes ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[postmanphat]

Ok you asked for it!!!

'Interventions'

SELECT InterventionDays.InterventionID, qryInterventionsMale.CountOfDropInID AS Male, qryInterventionsFemale.CountOfDropInID AS Female, qryInterventionsAdult.CountOfDropInID AS Adults, qryInterventionEET.CountOfDropInID AS EET, qryInterventionNEET.CountOfDropInID AS NEET, qryInterventionDEET.CountOfDropInID AS DEET, qryInterventionTP.CountOfDropInID AS TP, qryInterventionCA.CountOfDropInID AS CA, qryInterventionCL.CountOfDropInID AS CL, qryInterventionYO.CountOfDropInID AS YO, qryInterventionSN.CountOfDropInID AS SN, qryInterventionsEM.CountOfDropInID AS [Ethnic Minorities], qryInterventionsOutArea.CountOfDropInID AS [Out of Area], qryInterventionSunday.CountOfDropInID AS Sunday, qryInterventionMonday.CountOfDropInID AS Monday, qryInterventionTuesday.CountOfDropInID AS Tuesday, qryInterventionWednesday.CountOfDropInID AS Wednesday, qryInterventionThursday.CountOfDropInID AS Thursday, qryInterventionFriday.CountOfDropInID AS Friday, qryInterventionSaturday.CountOfDropInID AS Saturday
FROM ((((((((((((qryInterventionFriday RIGHT JOIN ((qryInterventionDEET RIGHT JOIN ((qryInterventionEET RIGHT JOIN ((qryInterventionsMale RIGHT JOIN (InterventionDays LEFT JOIN qryInterventionsFemale ON InterventionDays.InterventionID = qryInterventionsFemale.InterventionID) ON qryInterventionsMale.InterventionID = InterventionDays.InterventionID) LEFT JOIN qryInterventionsAdult ON InterventionDays.InterventionID = qryInterventionsAdult.InterventionID) ON qryInterventionEET.InterventionID = InterventionDays.InterventionID) LEFT JOIN qryInterventionNEET ON InterventionDays.InterventionID = qryInterventionNEET.InterventionID) ON qryInterventionDEET.InterventionID = InterventionDays.InterventionID) LEFT JOIN qryInterventionTP ON InterventionDays.InterventionID = qryInterventionTP.InterventionID) ON qryInterventionFriday.InterventionID = InterventionDays.InterventionID) LEFT JOIN qryInterventionThursday ON InterventionDays.InterventionID = qryInterventionThursday.InterventionID) LEFT JOIN qryInterventionSunday ON InterventionDays.InterventionID = qryInterventionSunday.InterventionID) LEFT JOIN qryInterventionWednesday ON InterventionDays.InterventionID = qryInterventionWednesday.InterventionID) LEFT JOIN qryInterventionSN ON InterventionDays.InterventionID = qryInterventionSN.InterventionID) LEFT JOIN qryInterventionSaturday ON InterventionDays.InterventionID = qryInterventionSaturday.InterventionID) LEFT JOIN qryInterventionTuesday ON InterventionDays.InterventionID = qryInterventionTuesday.InterventionID) LEFT JOIN qryInterventionsOutArea ON InterventionDays.InterventionID = qryInterventionsOutArea.InterventionID) LEFT JOIN qryInterventionCL ON InterventionDays.InterventionID = qryInterventionCL.InterventionID) LEFT JOIN qryInterventionYO ON InterventionDays.InterventionID = qryInterventionYO.InterventionID) LEFT JOIN qryInterventionMonday ON InterventionDays.InterventionID = qryInterventionMonday.InterventionID) LEFT JOIN qryInterventionsEM ON InterventionDays.InterventionID = qryInterventionsEM.InterventionID) LEFT JOIN qryInterventionCA ON InterventionDays.InterventionID = qryInterventionCA.InterventionID;

InterventionDays:

SELECT tblDropIn.InterventionID
FROM tblDay INNER JOIN tblDropIn ON tblDay.DayID = tblDropIn.DayID
WHERE (((tblDay.SessionDate) Between [Enter Start Date] And [Enter End Date]))
GROUP BY tblDropIn.InterventionID;

An example of one of the count queries referred to in 'Interventions' (they're all pretty much the same justs counting a different field)

SELECT tblDropIn.InterventionID, Count(tblDropIn.DropInID) AS CountOfDropInID
FROM tblContacts INNER JOIN tblDropIn ON tblContacts.ContactID = tblDropIn.ContactID
GROUP BY tblDropIn.InterventionID, tblContacts.Adult
HAVING (((tblContacts.Adult)=True));

 

[lespaul]

why are you running queries to get everyday of the week? Are those all the exact same query except for the criteria of the day of the week?

Maybe you'd be better off posting your BASE tables and some sample data and expected results...you shouldn't have to run a different query for each day....

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[PHV]

I really don't understand why so many qryInterventionXX !
You may have different counters with a single select by using the IIf function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[postmanphat]

Lesley - I have posted my base tables and stuff, see initial post. The thing with the day of the week is that the user only enters the date, so the query works out what day of the week each intervention falls under.

PHV - Its the only way I know how to count lots of different fields and present them in 1 query... each query counts how many times a different data item is recorded. So there is a query to count how many males, females, adults etc. Also, in each row of the table tbldropIn, the user selects a 'destination' from a table 'tbldestination'. these are also counted in a query each: qryInterventionYO, qryInterventionDEET, qryInterventionEET, qryInterventionCL and so on.

 

[lespaul]

except I don't have Access installed on this pc....so I can't open your database...there are much easier ways to count and group database records.

Leslie

 

[PHV]

A starting point:
SELECT D.InterventionID, Sum(IIf(C.Adult=True,1,0)) AS CountOfAdult, Sum(IIf(C.Adult=True,0,1)) AS CountOfChildren
FROM tblContacts C INNER JOIN tblDropIn D ON C.ContactID = D.ContactID
GROUP BY D.InterventionID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[postmanphat]

Leslie - please find attached screen shots of my ERD and the table 'tblDropIn'. The desired outcome is in the xls attachment on the first post. 'tblInterventions' is just the picklist in the tbldropin table to say whether it is one to one, phone or correspondence. 'tblIssues' and 'tblDestinations' are just picklists, and make up the bulk of the column headings in the xls. 'tblcontacts' is the list of the clients, and this is where the genderinfo is kept. The days of the week are calculated from the sessiondate in 'tblDay'.

I've used this method of counting, grouping and presnting data before and its worked fine, but like I say, this one is taking 30-45 minutes.

Many many thanks in advance.

 

[postmanphat]

PHV - I think I've just fallen in love with you.

Thankyou both for all your help... this place is better than university - I learn more here than I did in 3 years at that dump!