Count Function Improvement Advice

[asmolow]

I have a a query counting the number of records between two date values by week. It's specifically counting the number of days that a scheduled work order has in each accounting week. The query is taking an inconvenient amount of time to run and I'm not sure how to improve it.



Does anyone have any advice on how to speed it up or an alternative function to use?

This is the SQL code:

SELECT [Adj and Filt MM214].[Work Area], [Adj and Filt MM214].[Work Order], [Adj and Filt MM214].[WA Scheduled Start], [Adj and Filt MM214].[WA Scheduled Complete Date], [Calendar Date w WK End].[Wk Ending], Count([Calendar Date w WK End].[Calendar Date]) AS [Days per Week]
FROM [Calendar Date w WK End], [Adj and Filt MM214]
WHERE ((([Calendar Date w WK End].[Calendar Date]) Between [wa scheduled start] And [wa scheduled complete date]))
GROUP BY [Adj and Filt MM214].[Work Area], [Adj and Filt MM214].[Work Order], [Adj and Filt MM214].[WA Scheduled Start], [Adj and Filt MM214].[WA Scheduled Complete Date], [Calendar Date w WK End].[Wk Ending];

[rpeare]

Are you using a cartesian join? A cartesian join is when you have two tables (or more) in a query with no link between the tables

If you are that is likely the problem, especially if your tables [Calendar Date w Wk End] and [Adj and Filt MM214] tables/queries are large, you'd basically be multiplying the number of records in both tables together to find out what access is trying to process.

Depending on how the query ADJ AND FILT MM214 is constructed you should be able to get the correct summary of (I assume) weeks the project was worked on.

Another alternative would be to take the project start and end date and divide the date total by 7 to get the number of weeks.

[asmolow]

Yes it is a cartesian join. The [calendar date w WK End] table is just a list of which calendar dates fall into each specific accounting week(Friday-Thursday).

The ADJ AND FILT MM214 is a union query between two identical tables, except one of the tables is linked from another database. I did it this way to help keep the database file size low because the linked table is very large.

I am not trying to find the number of weeks a project(in this case individual production work orders), but rather the number of days a work order spends in each accounting week based on its scheduled start and scheduled completion.

The [calendar date w wk end] table doesn't have that many records, but the [Adj and Filt MM214] has over 700,000. Would reducing the number of fields make a significant difference?

[rpeare]

well let's say your [calendar date w wk end] table has 100 records, with a cartesian join you are multiplying that by 700,000 records or 700,000,000 records (every possible combination of data) your query is trying to process.

If your work week is monday - friday you can calculate that without involving the [calendar date w wk end] table at all. Reducing the number of fields would help but probably the biggest help would be to reduce your 700,000 records to something smaller if you can. I don't know what your data structure looks like or what you want your end result to be but it sounds like you're trying to create a crosstab query showing accounting weeks as your columns and projects as your rows but that's purely a guess on my part.

You also might benefit from creating a junction table between your jobs and the accounting weeks, in other words if you can forge a link between the jobs and the accounting weeks the query would definitely speed up.

[asmolow]

Oh ok, I understand now. I can't really reduce the record size of the tables, but if I set criteria date ranges for the table fields in the query, it should reduce the amount of combinations it has to process, right?

The work week is Monday - Friday, but I need to count the number of days per week based on a Friday-Thursday sequence. For instance, the calendar table would have the field [calendar date] listing each date from 3/6-3/11 adjacent to a[acct week ending] field displaying 3/11 for each date. That is how I am counting the number of work days per acct week for a work order that say starts on 3/4 and ends on 3/15. Can you think of a better way to do it?

You are right to some degree, one of my end results is a cross tab query that has the accounting weeks as the column heading, different work areas as the rows and total hours per week as the values. I also use it purely as a select query because i export the data to a spreadsheet utilizing a pivot chart. The big picture of what I am doing is calculating the scheduled hours per week by first dividing the total amount of hours it takes to complete a work order by the total number of scheduled work days in a work order to establish the hours per day. I then count the number of days a work order is scheduled to be worked in each accounting week and then multiply that count by the calculated hours per day.

[rpeare]

You can use the WEEKDAY function to figure out what day of the week a certain day falls on, then use that to figure whatever else you need.

So for instance weekkday([DateField]) where DATEFIELD is the name of your date field and the date was 3/6/2015 would return a 6 (friday). you can also use the weekday function and decide which is your 'day 1' of the week. So weekday([DateField], vbfriday) would return 1 because you're telling the function friday is to be considered the first day of the week.

So let's say you wanted to count the number of weeks (full or partial) between 3/4/2015 and 3/27/2015, this would cover 5 work weeks according to your schedule, and have 18 working days.

So you could do something like:

int((datediff("d", [StartDate], [EndDate])/7)) + iif(weekday([StartDate], vbfriday) < weekday([EndDate], vbfriday), 1, 0) + iif(weekday([startdate], vbfriday) <> 1, 1, 0)

in other words, figure the number of weeks by dividing the date difference in days between the start date and end date by 7, then add 1 if the weekday of the startdate is less than the weekday of the enddate, add 1 if the stardate is not a friday.

In the example of 3/4/2015 through 3/27/2015 your expression would be evaluated as

int(23/7) + 1 + 1

I didn't test this for all date ranges but I think it will be close and give you an idea how to figure weeks.