Here is what I am trying to do.
I have 2 sets of data that i am trying to combine. One set is order lead time and the other is the number of orders shipped each day. I want to set up a query that will tell me the number of BUSINESS days from when an order is received to when it is shipped. The issue is that business days are random, and are not easy to code in (I also do not want to have to maintain a table of business days).
What i have done so far is create 2 queries. The first query creates a list of business days by counting any day where more than 50 orders where shipped as a business day. The second query is simply a list of all orders where the shipped day is linked to the business days (i dont care about the lead time on orders that dont ship on business days).
My query to calculate lead time includes this:
DAYS: DCount("*","qryOrderShip","[DATE] between #" & [ORDER] & "# AND #" & [SHIP] & "#")
This works to calulate the unique operating days between order and shipment, but the problem is the speed. It takes roughly 2 minutes to return 8000 records, but the real problem is that attempting to make a query off of that query simply crashes my computer. Trying to export the information via application or Excel crashes those programs as well.
Is there a better way to do this? I am starting to think DCount with a large number of records is a bad idea. Any help would be greatly appreciated.