Re: Query taking too long

From: Ravi (Ravi_at_discussions.microsoft.com)
Date: 01/03/05 

Date: Sun, 2 Jan 2005 20:57:16 -0800

Hi

Table:
VehicleID....VehId
Log Date.....Dt...Date/time
Mileage......Mile..Number

Then i use a query to group vehiclewise

SELECT Table1.VehID, Min(Table1.Dt) AS MinDt, Max(Table1.Dt) AS MaxDt,
Max(Table1.Mile) AS MaxMiles
FROM Table1
GROUP BY Table1.VehID;

The query below is then used for finding the average:
**Countworkdays(startdate,EndDate)...vb function to count the work days

SELECT Avg1qry.VehID, Avg1qry.MaxDt, Avg1qry.MinDt, Avg1qry.MaxMiles,
(CountWorkDays([MinDt],[MaxDt])) AS WkDay, ([MaxMiles]/[WkDay]) AS Average
FROM Avg1qry;

When i run this query..for just 2 records which are 5 yrs old..i.e
startDate= 01/01/2000 and Enddate=31/12/2004..it takes 8 seconds
Ravi

"John Vinson" wrote:

> On Sun, 2 Jan 2005 14:15:02 -0800, "Ravi"
> <Ravi@discussions.microsoft.com> wrote:
>
> >Hi
> >I are trying to get the average consumption of our vehicles.
> >A log is entered every 2 days with "Log Date" and Mileage
> >In a query for number of days i take MaxDate-MinDate and count 'only' the
> >working days for which a vb function is used
> >
> >When the query is run ...it takes too much time since it is calculating the
> >'working days' for each vehicle..the records go back 5 years so it calculates
> >from Date() to 5 years.
> >I feel the approach i am using is not very efficient.
> >Please give me an idea how to speed up the query
> >Thanks
> >Ravi
>
> Please post a description of your table, and the SQL view of your
> current query. Calling a VBA function for every row in a query is
> indeed going to be a major performance drag.
>
>
> John W. Vinson[MVP]
>

Relevant Pages

  • Re: Displaying row no/Record Counting
    ... EnteredOn Date/Time when the record was added. ... You create a query that contains Table1. ... On each row of your query, you need to count the number of records in Table1 ... In> order to generate an alphabetical listing of these people,> I have to analyze the report in Excel and sort the> spreadsheet based on the client's name. ...
    (microsoft.public.access.queries)
  • Re: How to add to a new field based on information in existing field?
    ... a query to join the two tables. ... For example, Table1 includes fields TestID ... Table2 includes fields Description and DescriptionFr. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Non-updateable query issue
    ... You have an unnormalized table (of course, as it came from a flat spreadsheet instead of a relational source), and is it has 100 columns. ... joined to the master, showing all the records, the query is not updateable. ... > Say Fred deletes record 79 from Table1, and then Betty changes the> phone ... > Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: Non-updateable query issue
    ... Microsoft Access MVP ... When putting together that query, ... "Duane Hookom" wrote: ... Say Fred deletes record 79 from Table1, ...
    (microsoft.public.access.queries)
  • Re: Non-updateable query issue
    ... When putting together that query, ... spreadsheet instead of a relational source), and is it has 100 columns. ... Allen Browne - Microsoft MVP. ... Say Fred deletes record 79 from Table1, ...
    (microsoft.public.access.queries)